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.