LINQ LEFT JOIN on Nullable

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'm using 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 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

Translating to 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'm getting an error:

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

I've tried both solutions on int? and int comparison when LEFT OUTER JOIN in Linq issue, but still it's giving me these errors:

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

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

Using the solution GetValueOrDefault():

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

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

Answer 1

This is what I do when I have to do a Join on a nullable field

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 an int with an int?, which is giving you the error message, cast the int of the UserId to a nullable int, will solve your issue.

Answer 2

If any of the columns in join is of Nullable type, we need to fetch itts actual value 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

Answer 3

You're actually doing a lot of the work that EF is supposed to be doing for you (this happens alot, in particular with join).

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

EDIT: updated the following POCOs for EF Database-First (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; }
}

Note that you have to remember to add the partial keyword on the generated version of the Project class each time you regenerate it, but this is still better, because — by keeping your additions in a separate partial class declaration — the partial keyword is the only thing you'll have to remember to change in the generated class.

So your query is simply the following:

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

Let's see what this looks like:

Console.WriteLine(projectDetails.ToString());

This yields 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]

Which would appear to be exactly what you want.