在Entity Framework中对空值进行左连接 [英] Left Join in Entity Framework on null values

问题描述

我需要使用Entity Framework在C#项目中执行10个左联接.我已经检查了至少10个不同的页面以及关于如何执行此操作的stackoverflow帖子.他们都没有工作.

I need to perform 10 left joins in a C# project using Entity Framework. I have checked at least 10 different pages and stackoverflow posts on how to perform this operation. None of them worked.

我当前的加入看起来像这样:

My current join looks like this:

from tbl1 in context.tblName1
join varOne in context.tblName2 on tbl1.paramOne equals varOne.paramOne into j1
from jResOne in j1.DefaultIfEmpty()

我收到错误消息:

强制类型转换为值类型'System.Int32'失败，因为实例化值为空-结果类型的通用参数或查询必须使用可为空的类型."

"The cast to value type 'System.Int32' failed because the materialized value is null- Either the result type's generic parameter or the query must use a nullable type."

我被认为应该通过".DefaultIfEmpty()"解决.

Which I was lead to believe should be solved by the ".DefaultIfEmpty()"

我该如何解决这个问题?

How do I solve this issue?

推荐答案

@David Browne关于导航属性与EF中的联接是正确的.但是这里的问题有所不同(与联接操作无关，但与处理结果无关)，并且包含在异常消息中:

@David Browne is right about navigation properties vs joins in EF. But here the problem is different (unrelated to join operations, but to processing the result) and is contained in the exception message:

强制转换为值类型'System.Int32'的原因是物化值为空-结果类型的通用参数或查询必须使用可空类型.

The cast to value type 'System.Int32' failed because the materialized value is null - Either the result type's generic parameter or the query must use a nullable type.

这意味着投影( select )试图从左侧外部联接的某些右侧分配不可为空的类型值.在LINQ to Objects中，它将是一个简单的 NullReferenceException .但是，LINQ to Entities将查询转换为SQL，并且数据库(尤其是SQL)自然支持 NULL 值，即使是不可为空的列也是如此.因此，EF能够成功执行SQL，但是到了需要将结果具体化的地步(即将其放入匿名/特定的类成员中)，并且db查询返回 null ，而相应的属性是不可为null的类型，则EF无法继续执行，并引发异常，要求您通过将其强制转换为可为null的类型或将其转换为某些默认值等方式来解决它-由您决定，EF无法做出该决定.

What it means is that the projection (select) is trying to assign a non nullable type value from some of the right sides of the left outer joins. In LINQ to Objects it would be a simple NullReferenceException. However, LINQ to Entities translates the query to SQL, and databases (and SQL in particular) have natural support for NULL values even for non nullable columns. So EF is able to execute the SQL successfully, but when it comes to the point it needs to materialize the result (i.e. to put it into anonymous/specific class members), and the db query returns null while the corresponding property is non nullable type, EF cannot proceed and is throwing exception asking you to resolve it by either casting it to a nullable type, or converting it to some default value etc. - it's up to you, EF can't make that decision.

假设 TableB 具有 int属性，并且您有这样的查询:

Let say TableB has int Property and you have a query like this:

var query = from a in db.TableA
            from b in db.TableB.Where(b => b.Key == a.Key).DefaultIfEmpty()
            select new { Property = b.Property };

结果 Property 的C#隐含类型为 int ，因此，当查询结果包含 b 时，您将得到上述异常.与 a 不匹配(从C#角度来看， b 将为 null ，但从SQL角度来看， b.Property 将为 NULL ).

The C# implied type of the result Property is int, hence you'll get the aforementioned exception when the query result contains b that does not match a (b will be null from C# point of view, but from the SQL side the b.Property will be NULL).

为了解决它，您可以将其提升为可空类型:

In order to resolve it, you could promote it to nullable type:

Property = (int?)b.Property

或设置为某些默认值

Property =  b != null ? b.Property : 0

取决于您的需求.并且对任何非空值类型(例如 int ， decimal ， DateTime 等)执行类似操作.( string 是引用类型，因此没有这种问题(可以保留 null 值).

depending on your needs. And do similar for any non nullable value type like int, decimal, DateTime etc. (string is a reference type, so it has no such issue (can hold null value)).

这篇关于在Entity Framework中对空值进行左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！