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

Answer 1

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 };

Answer 2

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 };

Answer 3

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.

Answer 4

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