Type Inference failed in a call to 'join' on nullable and non-nullable int

Question

In my Linq, I am trying to make an inner join to a nullable field. Employee and Department have a relation, Department may have an EmployeeID or may have a null. So what would be my join, if i want only the records that satisifed the inner join (no result for null EmployeeIDs):

var result = from emp in employees
             join dept in departments
             on new { Source = emp.EmployeeID }
             equals new { Source = dept.EmployeeID };

I am getting an exception:

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

Thanks

score 11 · Answer 1 · answered Jul 09 '12 at 00:15

11

To compare Int? and Int, append .Value to the nullable property:

var result = from emp in employees
             join dept in departments
             on new { Source = emp.EmployeeID }
             equals new { Source = dept.EmployeeID.Value };

answered Jul 09 '12 at 00:15

Greg Sansom

20,442
6
58
76

3

It is important to note that you have to name the one that you add ".Value" to because the auto name will be "Value" not, "EmployeeID". You can (but don't need to) add a name to the one that does not have ".Value". So for this example `on new{emp.EmployeeID} equals new {EmployeeID = dept.EmployeeID.Value}` would work just fine – David May 20 '20 at 22:34
@David This is the correct answer. I was struggling because the auto name was "Value" and not the name of the variable. However the error Visual Studio gave me inferred that I was comparing incompatible datatypes. I think a lot would appreciate it if it was added this to this answer. – Kent Kostelac Jun 21 '22 at 08:59

Asbjørn Ulsberg · Accepted Answer · 2013-12-09T13:44:08.320

8

What if you reverse your join and put a little where in there?

var result = from department in departments
             where department.EmployeeID != null
             join employee in employees
             on department.EmployeeID.Value equals employee.EmployeeID
             select new { employee, department };

edited Dec 09 '13 at 13:44

answered Sep 08 '11 at 08:20

Asbjørn Ulsberg

8,721
3
45
61

How could it even suppose to work, still emp.EmployeeID is a non-nullable field and dept.EmployeeID is nullable field, I even check your code but no luck, Still same error. We can't join nullable int to int – manav inder Sep 08 '11 at 08:26
1

@asbjornu: you need to change dept.EmployeeID into dept.EmployeeID.Value – Konstantin Oznobihin Sep 08 '11 at 08:30
3

@MSingh: So is your code different from one here http://ideone.com/4F8X2? I guess it should be, since you are getting error still. – Konstantin Oznobihin Sep 08 '11 at 08:55
1

From the link you've given, I am so so much overwhelmed, I've no words to say THANK YOU, You are simply outstanding, There was a difference between this code and the one have written in that link, Anyhow BIG THANKS – manav inder Sep 08 '11 at 09:01
@Konstantin, I've modifed my answer (since it's already checked as a solution) to match your code. – Asbjørn Ulsberg Sep 08 '11 at 10:54
3

This is only a tip: why do you used "on new" and "equals new", for a single comparer, you don't need to use the statement "new", only "on" and "equals", `on department.EmployeeID.Value equals employee.EmployeeID` – gandarez Sep 08 '11 at 13:30

score 7 · Answer 3 · answered Dec 17 '17 at 12:03

Found a useful answer from another link at https://social.msdn.microsoft.com/Forums/en-US/bf98ec7a-cb80-4901-8eb2-3aa6636a4fde/linq-join-error-the-type-of-one-of-the-expressions-in-the-join-clause-is-incorrect-type-inference?forum=linqprojectgeneral

To join multi-valued keys you need to construct an anonymous typ on both sides of the 'equals' that is the same type. The anonymous type initializer expression infers both type and name of members from the expression you supply. In your case the names of the members are different so the types end up being different so C# cannot figure out the common type between the two.

on new { VC.Make, VC.Model } equals new { MD.MakeID, MD.RefNum }

should be

on new { VC.Make, CV.Model } equals new { Make = MD.MakeID, Model = MD.RefNum }

Using the name = value syntax in the initializer you can specify the name the compiler uses when creating the type. If all members types & names are the same then the anonymous types are the same type.

This is especially important when "Just re-order the query" dilutes understanding and requires more work. If you're checking for null in your where clause, you can just use .Value for the nullable column and then you'll HAVE to specifically name the object property like you say here! — Ryanman, Nov 05 '20 at 20:15

score 2 · Answer 4 · edited May 13 '13 at 19:09

2

Check the type on emp.EmployeeID and dept.EmployeeID. You might be missing a cast if they are different.

something like:

on new { Source = emp.EmployeeID }
equals new { Source = **(int)**dept.EmployeeID };

Looks like emp.EmployeeID is of type int and dept.EmployeeID is of type nullable<int>.

edited May 13 '13 at 19:09

Cairnarvon

25,981
9
51
65

answered May 13 '13 at 18:47

devendar r mandala

253
1
2
6

I nearly gave up due to this mistake – N_E Oct 20 '22 at 21:56

score 1 · Answer 5 · answered Jul 20 '15 at 21:55

I had the same issue, where my charge_codes.CompanyId was nullable but my order_items.CompanyId was NOT nullable.

So I had to get my charge codes into their own ananomous type and make it not be nullable.

var chargeCodes = from s in db.Charge_Codes
where s.CompanyID != null
select new { CompanyID = (int)s.CompanyID, 
             Charge_CodeID = s.Charge_CodeID, 
             Revenue_Code_Id = (int)s.Revenue_CodeID, };



//now my chargeCodes contains an anonymous with a non nullable CompanyID and 
//a non nullable Revenue_CodeID 

//use chargeCodes here
var query = from oi in db.Order_Items
join cc in chargeCodes on 
new {oi.CompanyID, oi.Charge_CodeID} equals new {cc.CompanyID, cc.Charge_CodeID}

Thank you! I had a similar problem where the id field was a varchar in one table and number in other. Converting to anomalous type fixed it. — Jim Neff, Mar 08 '17 at 13:59

score 0 · Answer 6 · answered Jun 28 '18 at 09:46

In my scenario I had this error on a join using multiple columns. The property names were different and one of them was also nullable. All I did was creating a name to those properties and adding the ".Value" on the nullable value so the LINQ Join could correctly associate those properties.

var query = from y in Context.Table1
        join y in Context.Table2 on new { Field1 = x.Field1.Value, Field2 = x.Field2 }
        equals new { Field1 = y.Field1DiffName, Field2 = y.Field2 }

I hope it helps whoever is facing this issue.

Type Inference failed in a call to 'join' on nullable and non-nullable int

6 Answers6

Linked