LINQ LEFT JOIN on Nullable <int>

Question

I have two tables below:

Project( ProjectID INT, Name VARCHAR(200), AssignedUserID INT NULL ) User( UserID INT, LastName VARCHAR(50), FirstName VARCHAR(50) ) 

I am using the Entity Framework Database-First approach. So in my model:

 Class Project{ public int ProjectID; public string Name; public Nullable<int> AssignedUserID; } Class User{ public int UserID; public string LastName; public string FirstName; } 

I want to query all the PROJECT and its assigned user:

 SELECT p.ProjectID, p.Name, u.LastName, u.FirstName FROM Project p LEFT JOIN Users u ON u.UserID = p.AssignedUserID 

Translation in Linq:

 var projectDetails = from p in context.Project join u in context.User on p.AssignedUserID equals u.UserID into lj from x in lj.DefaultIfEmpty() select new { ProjectID = p.ProjectID, ProjectName = p.Name, UserLastName = u.LastName, UserFirstName = u.FirstName } 

However, I get the error message:

The type of one of the expressions in the join clause is invalid. Type input error in "Join" call.

I tried both solutions on int? and int when LEFT OUTER JOIN in Linq problem , but still it gives me the following errors:

Using solution: (int)(p.AssignedUserID ?? default(int))

The LINQ to Entities does not recognize the 'Int32 ToInt32 (Int32)' method, and this method cannot be translated into a storage expression.

Using the GetValueOrDefault() solution:

LINQ to Entities does not recognize the 'Int32 GetValueOrDefault (Int32) "method, and this method cannot be translated into a warehouse expression.

How do you make LEFT JOIN on Nullable<int> and int ?

Answer 1

This is what I do when I need to merge into a field with a null value

Original Linq:

 var projectDetails = from p in context.Project join u in context.User on p.AssignedUserID equals u.UserID into lj from x in lj.DefaultIfEmpty() select new { ProjectID = p.ProjectID, ProjectName = p.Name, UserLastName = u.LastName, UserFirstName = u.FirstName } 

Modified Linq:

 var projectDetails = from p in context.Project join u in context.User on new {User = p.AssignedUserID} equals new {User = (int?)u.UserID} into lj from x in lj.DefaultIfEmpty() select new { ProjectID = p.ProjectID, ProjectName = p.Name, UserLastName = x.LastName, UserFirstName = x.FirstName } 

The problem is that you are trying to join int with int ?, which gives you an error message, discards the int of the UserId object with NULL int, will solve your problem.

Answer 2

In fact, you are doing a great job that EF should do for you (this happens a lot, particularly with join ).

You need the navigation property in your Project class, not just the FK that you have now:

EDIT : Updated the following POCOs for the EF-First database (i.e. using partial classes).

 // generated code public partial class Project { public int ProjectId { get; set; } public string Name { get; set; } public int? AssignedUserId { get; set; } } public class User { public int UserId { get; set; } public string LastName { get; set; } public string FirstName { get; set; } } // your code public partial class Project { [ForeignKey("AssignedUserId")] public User User { get; set; } } 

Please note that you need to remember to add the partial keyword to the generated version of the Project class each time you regenerate it, but this is still better, because - keeping your additions in a separate partial class declaration - the partial keyword - this is the only thing you need to remember in the generated class.

So your query looks simple:

 var projectDetails = from p in context.Projects select new { p.ProjectId, p.Name, p.User.LastName, p.User.FirstName }; 

Let's see how it looks:

 Console.WriteLine(projectDetails.ToString()); 

This gives the following SQL:

 SELECT [Extent1].[ProjectId] AS [ProjectId], [Extent1].[Name] AS [Name], [Extent2].[LastName] AS [LastName], [Extent2].[FirstName] AS [FirstName] FROM [Projects] AS [Extent1] LEFT OUTER JOIN [Users] AS [Extent2] ON [Extent1].[AssignedUserId] = [Extent2].[UserId] 

That will look exactly the way you want.

Answer 3

If any of the columns in the join is Nullable , we need to get the actual value of itts using .Value , something like this: -

  join u in context.User on p.AssignedUserID.Value equals u.UserID into lj 

Or you can also do this: -

 join u in context.User on new { p.AssignedUserID } equals new { AssignedUserID = u.UserID } into lj