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

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.

Answer 1

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 }

...which will find no matches PAY_SEC_ID is null, or

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

...which defaults p1 to 0 when PAY_SEC_ID is null and again no match will be found (assuming that ID values will never be 0).

Answer 2

Hopefully this helps someone with a similar facepalm moment I just had, make sure the object's property names are the same. The error displays itself as:

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

This is slightly misleading as this is the same message that appears when you have two value types that are different i.e. int and double.

What this actually meant in my case was that the two objects themselves were different types, not the values:

join count in productCount on new { tool.ItemNo, tool.ItemType } equals new { count.OrigNumber, count.ItemType }

This was generating the following objects; which are obviously not comparable.

'a is new { int ItemNo, int ItemType }

'a is new { int OrigNumber, int ItemType }

To correct this simply just name the OrigNumber field to ItemNo:

join count in productCount on new { tool.ItemNo, tool.ItemType } equals new { ItemNo = count.OrigNumber, count.ItemType }

Answer 3

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.)

Answer 4

In the original LINQ query, the where clause contains assignment, not comparison (i.e. need "==" instead of "=").