为什么EF在查询引用属性时会生成这条sql
当使用 AdventureWorks 数据库并发出此查询时：
AdventureWorksEntities entities = new AdventureWorksEntities();
entities.Contacts.Where(x => x.FirstName == "A"
|| x.FirstName == "B"
|| x.FirstName == "C")
.ToList();
将被转换为此 sql，这是最好的：
//ommited for brevity
FROM [Person].[Contact] AS [Extent1]
WHERE [Extent1].[FirstName] IN (N'A',N'B',N'C')
但是，当我发出此查询时：
entities.Employee.Where(x => x.Contact.FirstName == "A"
|| x.Contact.FirstName == "B"
|| x.Contact.FirstName == "C")
.ToList();
我得到此 SQL：
//ommited for brevity
FROM [HumanResources].[Employee] AS [Extent1]
INNER JOIN [Person].[Contact] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]
LEFT OUTER JOIN [Person].[Contact] AS [Extent3] ON [Extent1].[ContactID] = [Extent3].[ContactID]
WHERE [Extent2].[FirstName] = N'A' OR [Extent3].[FirstName] IN (N'B',N'C')
为什么我得到内部和外部联接，并且 EF 正在拆分两者的交叉点在哪里？
值得注意的是，使用 contains 创建相同的 SQL：
var names = new List<string>{"A", "B", "C"};
entities.Employee.Where(x => names.Contains(x.Contact.FirstName)).ToList();
编辑：所以它似乎是一个 EF bug，我已经接受了提供解决方案的答案
编辑：打开的连接问题，它位于 此处
When using the AdventureWorks database and issuing this query:
AdventureWorksEntities entities = new AdventureWorksEntities();
entities.Contacts.Where(x => x.FirstName == "A"
|| x.FirstName == "B"
|| x.FirstName == "C")
.ToList();
Will be translated to this sql, which is the best it can:
//ommited for brevity
FROM [Person].[Contact] AS [Extent1]
WHERE [Extent1].[FirstName] IN (N'A',N'B',N'C')
However when I issue this query:
entities.Employee.Where(x => x.Contact.FirstName == "A"
|| x.Contact.FirstName == "B"
|| x.Contact.FirstName == "C")
.ToList();
I get this SQL:
//ommited for brevity
FROM [HumanResources].[Employee] AS [Extent1]
INNER JOIN [Person].[Contact] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]
LEFT OUTER JOIN [Person].[Contact] AS [Extent3] ON [Extent1].[ContactID] = [Extent3].[ContactID]
WHERE [Extent2].[FirstName] = N'A' OR [Extent3].[FirstName] IN (N'B',N'C')
Why am I getting an inner and outer join and is EF splitting the where across both of them?
Of Note using contains creates the same SQL:
var names = new List<string>{"A", "B", "C"};
entities.Employee.Where(x => names.Contains(x.Contact.FirstName)).ToList();
EDIT: So it appears to be a EF bug, I've accepted the answer which provided a work around
EDIT: Opened connect issue, it's located here
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
编写一个存储过程，它接受 TVP 作为输入参数，并让 EF 实现 SP 的结果:)
Write a Stored Procedure which accepts a TVP as input parameter and let EF materialize the results from the SP :)
看起来像是 EF 中的一个错误。
我认为问题与您检查子表中的字段有关。 EF 没有看到所有 OR 子句都引用同一个表以及该表中的相同字段。因此，它创建了复杂的连接。
尝试重写它以使用 CONTAINS 而不是 OR。
在 LINQ to SQL 中使用 contains()
Looks like a bug in EF.
I think that the problems is related to you checking against a field in a sub table. EF does not see that all the OR clauses refer to the same table and the same field in that that table. Therefore, it creates that complicated join.
Try rewriting it to use CONTAINS instead of OR.
Using contains() in LINQ to SQL