阻止LINQ到实体连接中的NULL检查 [英] Prevent NULL checks in LINQ to Entity Joins
问题描述
我们有一个名为 Student 的表。该表有一个称为 Homeroom 的字段，其中值是学生的房间的房间号。该值可以为null。
We have a table called Student. That table has a field called Homeroom, where the value is a room number of the student's homeroom. The value can be null.
我们有一个名为 Staff 的第二个表。该表还有一个称为 Homeroom 的字段，以指示教师分配给哪个房间。该值可以为空。
We have a second table called Staff. That table also has a field called Homeroom to indicate which homeroom the teacher is assigned to. The value can be null.
但是当学生的 Homeroom 为null时，一个
But when the student's Homeroom is null, a Staff record should not be returned.
我们曾经利用这样一个事实：检查两个空字段的相等性总是在SQL中返回false。通过SQL，这是我们如何获取我们想要的数据：
We used to take advantage of the fact that checking two null fields for equality always returns false in SQL. Through SQL, this is how we would get the data we want:
SELECT STUDENT.ID, STAFF.NAME as [Homeroom Teacher]
FROM STUDENT
LEFT OUTER JOIN STAFF ON
STAFF.BUILDING = STUDENT.BUILDING AND
STAFF.HOMEROOM = STUDENT.HOMEROOM
学生将被退回，但没有老师。
Student would be returned, but no teacher.
我们正在使用代码优先的实体框架POCO对象。所以我们有一个Student对象和一个Staff对象。当我们在LINQ中重新创建这个SQL时：
We are using Entity Framework with Code First POCO objects. So, we have a Student object and a Staff object. When we recreate this SQL in LINQ:
from student in repo.GetStudents()
join homeroomTeacher in repo.GetStaff()
new { student.Building, Room = student.Homeroom }
equals new { homeroomTeacher.Building, Room = homeroomTeacher.Homeroom }
into roj2
from homeroomTeacherRoj in roj2.DefaultIfEmpty()
select student.Id, homeroomTeacherRoj.Name;
生成的SQL包含两个Homeroom字段的NULL检查：
The SQL generated contains a NULL check on both Homeroom fields:
SELECT STUDENT.ID, STAFF.NAME
FROM STUDENT AS [Extent1]
LEFT OUTER JOIN [dbo].[STAFF] AS [Extent2] ON
([Extent1].[BUILDING] = [Extent2].[BUILDING]) AND
(
([Extent1].[HOMEROOM] = [Extent2].[HOMEROOM]) OR
(([Extent1].[HOMEROOM] IS NULL) AND ([Extent2].[HOMEROOM] IS NULL))
)
这将返回学生，任何没有定义房间的工作人员。这不是我们想要的或预期的，基于我们以前如何编写我们的SQL语句。
This will return the student, and any staff who does not have a homeroom defined. That's not what we wanted or expected based on how we previously wrote our SQL statements.
一个明显的方法是确保我们不包括不有一个homeroom（ join homeroomTeacher in repo.GetStaff（）。Where（staff => staff.Homeroom！= null）但是LINQ中还有另一种方法来防止当加入它们时，对空字段进行空检查？
An obvious way around it is to make sure we don't include staff that do not have a homeroom (join homeroomTeacher in repo.GetStaff().Where(staff => staff.Homeroom != null). But is there another way in the LINQ to prevent null checks on fields when joining them?
推荐答案
如果将您的连接移动到where子句，则在DbContext对象将关闭（EF 6引入）NULL检查行为：
if you move your join into a where clause, then the following setting on the DbContext object will turn off the (EF 6 introduced) NULL checking behaviour:
Context.Configuration.UseDatabaseNullSemantics = true;
所以，要在where子句中Join，可以将查询分割成2个IQueryable对象
So, to "Join" in a where clause, you can split out the query into 2 IQueryable objects
var subquery = from homeroomTeacher in repo.GetStaff()
where ...
select homeroomTeacher;
var query = from student in repo.GetStudents()
where subquery.Any(homeroomTeacher =>
homeroomTeacher.xxx == student.xxx) -- simplified join for demo code
select student;
fyi，引入了UseDatabaseNullSemantics来修复这种行为，但看起来他们忘记了JOIN语义并仅将其应用于WHERE语义。
fyi, the UseDatabaseNullSemantics was introduced to fix-up this behaviour but it looks like they forgot the JOIN semantics and only applied it to the WHERE semantics.
此原始声明错误 - EF 4.3.1展示了相同的JOIN行为：
This original statement was wrong - EF 4.3.1 exhibited the same JOIN behaviour:
这实际上意味着与以前的版本相比，EF 6的一些结果集现在是不同的。在我看来，这是一个大的交易!!!因为它在我的工作解决方案中引入了错误!!!!
This essentially means some result sets are now different for EF 6, compared to previous versions. This, in my mind, IS A BIG DEAL !!!! as it introduced bugs into my working solutions !!!!
我在codeplex上提出了一个问题： https://entityframework.codeplex.com/workitem/2006
I have raised an issue on codeplex:https://entityframework.codeplex.com/workitem/2006
这篇关于阻止LINQ到实体连接中的NULL检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
