LinqRemoteDataAdapter issue

Data Abstract
1

Anton,

I am testing the latest Beta build 9.3.0.1329 of Data Abstract for .Net Server Edition (I am trying to use Substring on a Linq join).

I have a Linq query using LinqRemoteDataAdapter as follows:

        var affiliates = from p in linqRemoteDataAdapter.GetTable<SvcCWAuth.AFFILIATION>()
                         where p.PROV_NBR == member.PCP.AFFNumber.Substring(0, 12)
                         where p.BUSINESS_UNIT == member.BusinessUnit
                         where p.PROG_NBR == member.ProgramNumber
                         where p.REGION == member.Region
                         where p.VOID == "  "
                         where p.YMDEFF <= bedLogDate
                         where p.YMDEND >= bedLogDate
                         select p;

When I look at the query generated in the debugger, I get the following:

SELECT [t0].[ACCEPT], [t0].[ACCEPTASSIGN], [t0].[ACTIVE_PATIENTS], [t0].[AFF_NBR], [t0].[ALT_QUALIFIER], [t0].[AREA], [t0].[BUSINESS_UNIT], [t0].[CAPKEY], [t0].[CAPTYPE], [t0].[CARRIER], [t0].[CF_CODE], [t0].[CHECKSUM_VALUE], [t0].[CLAIM_TYPE], [t0].[CLASS_X], [t0].[COUNTY], [t0].[CYCLE], [t0].[EXCEPTION_FEE], [t0].[FUND_CLASS], [t0].[HAT_CODE], [t0].[IMAGE_RECNBR], [t0].[IRS_NBR], [t0].[LAG], [t0].[LICENSE_NBR], [t0].[LOS_REG], [t0].[MEDICAID_NBR], [t0].[MEDICARE_NBR], [t0].[METHOD_X], [t0].[NARC_NBR], [t0].[NPI], [t0].[OFFICE_NBR], [t0].[OP_NBR], [t0].[PATIENT_LOAD], [t0].[PAY_CLASS], [t0].[PAY_DISCOUNT], [t0].[PAYTO], [t0].[PERIOD_TABLE_NBR], [t0].[POOL_CLASS], [t0].[PRAC_NBR], [t0].[PROG_NBR], [t0].[PROV_NBR], [t0].[PROVCAT], [t0].[REGION], [t0].[REMIT_EFT], [t0].[REMIT_EOP], [t0].[SCALE_CLASS], [t0].[SPEC1], [t0].[STATUS_X], [t0].[SUB_PROG_NBR], [t0].[TBL_OVRD], [t0].[TPID_TRANSTYPE], [t0].[TRANSCODE], [t0].[VOID], [t0].[YMDEFF], [t0].[YMDEND], [t0].[YMDTRANS], [t0].[YMDVISIT], [t0].[ZIP_HIAA]
FROM [AFFILIATION] AS [t0]
WHERE ((((((([t0].[PROV_NBR] = :p0) AND ([t0].[BUSINESS_UNIT] = :p1)) AND ([t0].[PROG_NBR] = :p2)) AND ([t0].[REGION] = :p3)) AND ([t0].[VOID] = :p4)) AND ([t0].[YMDEFF] <= 20170922)) AND ([t0].[YMDEND] >= 20170922))

This all looks reasonable, but the when the Linq expression is evaluated, I get the following error:

“An exception occurred on the server: SQL Parser error: From token expected, LeftParenthesis token found”

Any ideas or suggestions?

Thanks,

Mark

2

Hello

Are you 100% sure that this is the query that fails?

You mention Join however this is just a plain query with constant query parameters. member.PCP.AFFNumber.Substring(0, 12) here is evaluated client-side and its constant value is being sent to the server.

Also please upgrade to the latest Beta build published yesterday.

Regards

3

I am testing the fix for the Substring on a join, however, this query is executed before the query with the join. This query is the one currently failing.

I have upgraded to the latest Beta release (Oct 4th) and have tested with the same results.

Just to make things simpler, I rewrote the query to use straight variables and got the same results. Here is the rewritten query and the values for each variable at the time the query was executed:

string provNumber = Member.PCP.AFFNumber.Substring(0, 12);
string businessUnit = Member.BusinessUnit;
string programNumber = Member.ProgramNumber;
string region = Member.Region;
int bedLogDate = BedLogDate;
var affiliates = from p in linqRemoteDataAdapter.GetTable<SvcCWAuth.AFFILIATION>()
where p.PROV_NBR == provNumber
where p.BUSINESS_UNIT == businessUnit
where p.PROG_NBR == programNumber
where p.REGION == region
where p.VOID == " "
where p.YMDEFF <= bedLogDate
where p.YMDEND >= bedLogDate
select p;

provNumber = "12132 "
businessUnit = "04"
programNumber = "EJ"
region = "BA"
bedLogDate = 20170922

Thanks,

Mark

4

Please create a testcase and send it to support@