The type of one of the expressions in the join clause is incorrect in Entity Framework

c# entity-framework join linq linq-to-entities

Question

While trying to execute this query:

var query = from dpr in ctx.DPR_MM
            join q in ctx.QOT on dpr.DPR_QOT_ID equals qot_id
            join p in ctx.PAY_MM on new { q.QOT_SEC_ID, dpr.DPR_TS } equals new { p.PAY_SEC_ID, p.PAY_DATE }
            where q.QOT_ID = qot_id
            select new
            {
                dpr.dpr_ts,
                dpr.dpr_close,
                pay.First().pay_dividend
            };

I'm getting this error:

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'.

QOT_SEC_ID is of type decimal and PAY_SEC_ID is of type int32. I'm not allowed to change it in the table.

No matter what I do, I'm not able to change it in model's properties. I have tried to convert the types like this:

join p in ctx.PAY on new { sec_id = (Int32)(q.QOT_SEC_ID), dpr.DPR_TS } equals new { sec_id = (Int32)p.PAY_SEC_ID, p.PAY_DATE }

but getting the error above.

1
61
12/15/2014 10:49:30 PM

Accepted Answer

The types and the names of the properties in the anonymous types must match:

new { p1 = q.QOT_SEC_ID, p2 = dpr.DPR_TS } 
    equals 
new { p1 = (decimal)p.PAY_SEC_ID, p2 = p.PAY_DATE }

or if p.PAY_SEC_ID were an int?:

new { p1 = (int?)q.QOT_SEC_ID, p2 = dpr.DPR_TS } 
    equals 
new { p1 = p.PAY_SEC_ID, p2 = p.PAY_DATE }
142
8/18/2015 8:18:31 PM

Popular Answer

I'm guessing that one of the columns has a type that is implicitly convertable to the other. Likely, int and int?. That's why equals implicitly converts and new { X = 1 } is incompatible with new { X = (int?)1 }.

Cast one of the conflicting columns to int or int? depending on whether nulls are possible or not. E.g.

new { Customer_ID = (int?)pl.Customer_ID, ... }

Admittedly, the compiler error in this particular case is quite unclear and does not point to the root cause.

(This answer was rescued from a deleted duplicate. Since it's more complete than the currently accepted one I'll add it.)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow