LINQ LEFT JOIN on Nullable< int> [英] LINQ LEFT JOIN on Nullable<int>

问题描述

我有两张表：

 项目（
 ProjectID INT，
名称VARCHAR（200 ）
 AssignedUserID INT NULL 
）
用户（
 UserID INT，
姓氏VARCHAR（50），
名字VARCHAR（50）
 ）
  

我正在使用Entity Framework Database-First方法。所以在我的模型中：

 类项目{
 public int ProjectID; 
 public string Name; 
 public Nullable< int> AssignedUserID; 
} 
 
类用户{
 public int UserID; 
 public string LastName; 
 public string FirstName; 
} 
  

我想查询所有 PROJECT 及其分配的用户：

  SELECT 
 p.ProjectID，
 p.Name，
 u.LastName，
 u.FirstName 
 FROM项目p 
 LEFT JOIN用户u ON u.UserID = p.AssignedUserID 
  

翻译成Linq：

  var projectDetails = 
从p在上下文中。项目
在上下文中加入uU.User 
在p.AssignedUserID等于u.UserID到lj 
从x在lj.DefaultIfEmpty（）
 select new { 
 ProjectID = p.ProjectID，
 ProjectName = p.Name，
 UserLastName = u.LastName，
 UserFirstName = u.FirstName 
} 
  

但是，我收到错误：

连接子句中的一个表达式的类型不正确。
在Join的调用中，类型推断失败。

我已经尝试了 INT？而在Linq问题中的LEFT OUTER JOIN问题时，int int比较 ，但是仍然给我这些错误：

使用解决方案：（int）（p.AssignedUserID？default（int））

LINQ to Entities不识别方法'Int32 ToInt32（Int32）'
方法，并且此方法不能转换为存储表达式。

使用解决方案 GetValueOrDefault（）：

LINQ to Entities不识别方法'Int32
GetValueOrDefault（Int32）'方法，并且此方法无法将
转换为存储表达式。

你如何做 LEFT JOIN on Nullable< int> 和 int ？

解决方案

这是我在空白字段上加入时所做的b
$ b

原始Linq：

  var proj ectDetails = 
 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：

 code> var projectDetails = 
 from p in context.Project 
 join u in context.User 
 on new {User = p.AssignedUserID} equals new {User =（int？） u.UserID}从lj.DefaultIfEmpty（）中的x中的lj 
 
选择新的{
 ProjectID = p.ProjectID，
 ProjectName = p.Name，
 UserLastName = x.LastName，
 UserFirstName = x.FirstName 
} 
  

问题是你试图加入一个int int，这是giv给你错误消息，将UserId的int转换为可空的int，将解决您的问题。

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?

解决方案

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.

这篇关于LINQ LEFT JOIN on Nullable< int>的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！