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