LINQ to实体生成不正确的SQL [英] LINQ to Entities generating incorrect SQL

问题描述

我正在过滤一个IQueryable，以返回将字段UserId(可为null的int)设置为null的所有实体.查询生成不正确的SQL，因此失败-语句为 如下-

I am filtering an IQueryable to return all entities that have the field UserId (a nullable int) set to null. The query generates the incorrect SQL and thus fails -- the statement is as follows -

var filtered = certificates.Where(c => !c.UserId.HasValue).Select(c => c.SubjectName);

生成的SQL是-

SELECT 
CAST(NULL AS varchar(1)) AS [C1], 
CAST(NULL AS int) AS [C2], 
CAST(NULL AS datetime2) AS [C3], 
CAST(NULL AS datetime2) AS [C4], 
CAST(NULL AS bit) AS [C5], 
CAST(NULL AS datetime2) AS [C6], 
CAST(NULL AS int) AS [C7]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

有人知道WTF正在进行吗?这个想法很简单，我只想返回UserId为false的所有行. UserId可为空，并且要查询的表具有与所述条件匹配的三行，但是LINQ查询返回0.

Any idea WTF is going on? The idea is simple I just want to return all the rows where the field UserId is false. UserId is nullable and the table being queried has three rows that match the condition described, however the LINQ query returns 0.

谢谢！

推荐答案

这是EF在确定确定不会返回任何结果时生成的查询.这样的查询可以最大程度地减少数据库处理.

This is the kind of query that EF generates when it knows for sure that the query won't return any results. Such a query minimizes database processing.

EF如何确定?仅当它知道数据库中的UserId不可为空时.反过来，只有在Certificate(POCO类)中还有一个按需映射的User引用时才可以.寻找类似的东西

How can EF be so sure? This can only be when for all it knows UserId in the database is not nullable. This, in turn, can only be when there's also a User reference in Certificate (the POCO class) that is mapped as required. Look for something like

HasRequired(t => t.User).WithMany(t => t.Certificates)

中的

或DbContext中的OnModelCreating替代. (在代码优先中，可能具有必需的引用，而随附的原始Id属性是可为null的类型.在edmx文件中，此验证无效).

in an EntityTypeConfiguration<Certificate>, or in an override of OnModelCreating in your DbContext. (In code-first it is possible to have a required reference, while the accompanying primitive Id property is a nullable type. In an edmx file this doesn't validate).

因此，如果外键在数据库中为空，那么我认为您必须将User映射为可选.

So I think you have to map User as optional if in the database the foreign key is nullable.

这篇关于LINQ to实体生成不正确的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！