为什么EF会将包含方法的调用转换为Exists子句而不是In子句? [英] Why does EF translate a Contains method call to Exists clause instead of In Clause?

问题描述

当我创建此查询时:

  context.Orders.Where(x => context.Customers.Where(c => c.PersonID ==10002).Select(c => c.CustomerID.Value).包含(x.CustomerID.Value)).ToList(); 

我希望它创建这样的查询:

 从其中有CustomerID的订单中选择*(从Customer中选择CustomerID其中PersonID = 10002) 

但是生成的查询等同于:

 从存在的订单中选择*(从Customer中选择1，在其中cc.PersonID = 10002和c.CustomerID = o.CustomerID) 

这是一个非常简单的查询，可以写为:

  context.Orders.Where(x => x.Customer.PersonID == 10002).ToList() 

但是我的实际情况不能.而且，对于这个简单的查询，执行时间没有什么不同，但是在更复杂的情况下，预期的查询运行得更快.

如何强制EF以我想要的方式使用查询?

解决方案

我的理论是EF本身不支持 Contains ，但它确实支持 Any .作为一种技巧，它将您的表达式转换为等效的 Any 表达式，然后将其转换为Sql中的 Exists 表达式.

这种方法的问题在于，SQL查询的杂乱程度比需要的杂乱(您的问题)，而且我还发现了一些示例，这些示例无法很好地优化数据库.

我使用的解决方法是用内部的 Join 替换 Contains .因此，您的示例变为:

在上下文中来自c的

 .在上下文中加入o.o.CustomerId上的订单等于c.CustomerId其中c.PersonId = 10002选择o; 

(对不起，我知道您使用的是lambda表达式，但我不确定如何使用这些表达式编写联接).

When I create this query:

context.Orders.Where(x => context.Customers.Where(c => c.PersonID == 
10002).Select(c => c.CustomerID.Value).Contains(x.CustomerID.Value)).ToList();

I Expect it to create a query like this:

select * from Orders where CustomerID in (select CustomerID from Customers 
Where PersonID = 10002)

But the generated query is equivalent to:

select * from Orders o where Exists(select 1 from Customers c where
c.PersonID = 10002 and c.CustomerID = o.CustomerID)

this is a very simple query which could be written as:

context.Orders.Where(x => x.Customer.PersonID == 10002).ToList()

but my actual scenario cannot be. Also, for this simple query, the execution time is not different, but in more complex cases, the expected query runs much faster.

How can I force EF to use the query the way I want it to?

解决方案

My theory is that EF does not natively support Contains, but it does support Any. As a hack, it translates your expression into the equivalent Any expression, which translates into an Exists expression in Sql.

My problem with that approach is that the sql query is messier than it needs to be (your issue), and I have also found examples where the database cannot optimize as well as it could.

The workaround that I use is to replace the Contains with an inner Join. So your example becomes:

from c in context.Customers
join o in context.Orders on o.CustomerId equals c.CustomerId
where c.PersonId = 10002
select o;

(Sorry, I know you are using lambda expressions but I'm not sure how to write joins using those).

这篇关于为什么EF会将包含方法的调用转换为Exists子句而不是In子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！