c# - LINQ LEFT JOIN on Nullable

Question

下面有两个表:

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

我正在使用 Entity Framework 数据库优先方法。所以在我的模型中:

Class Project{
    public int ProjectID;
    public string Name;
    public Nullable<int> AssignedUserID;
}

Class User{
    public int UserID;
    public string LastName;
    public string FirstName;
}

我要查询所有PROJECT及其分配的用户:

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

翻译成 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
        }

但是，我收到一个错误:

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

我已经在 int? and int comparison when LEFT OUTER JOIN in Linq issue 上尝试了这两种解决方案，但它仍然给我这些错误:

使用解决方案:(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.

使用解决方案 GetValueOrDefault() :

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

你好LEFT JOIN在 Nullable<int>和 int ？

Answer 1

当我必须在可为 null 的字段上执行 Join 时，这就是我所做的

原始 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
        }

修改后的 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
        }

问题是您正在尝试将一个 int 与一个 int 连接起来？这会给您错误消息，将 UserId 的 int 转换为可为 null 的 int，将解决您的问题。