SelectMany创建许多SQL select语句，而不是使用join的语句 [英] SelectMany creates lots of SQL select statements instead of one with join

问题描述

我正在用SelectMany编写查询，并检查了它在LINQPad中生成的SQL.查询非常简单.

I'm writing a query with SelectMany and checked SQL it generates in LINQPad. The query is very simple.

假设我有3个实体:Customer，Order，OrderItem. OrderItem保存有关订购的产品和数量的信息.

Let's say I have 3 entities: Customer, Order, OrderItem. OrderItem holds info about what product is ordered and in what quantity.

我想为一位客户获得全部OrderItems.

I want to get all OrderItems for one customer.

context.Customers.First().Orders.SelectMany(o=>o.OrderItems)

我得到了预期的结果集，但是SQL对我来说真的很奇怪.有很多选择语句.首先，它选择一个客户，这没关系.然后它选择一个订单(因为该客户只有一个)，然后为先前选择的Order中的每个OrderItem创建一个选择.因此，我得到的选择数与OrderItems + Orders一样多选择Customer.看起来像这样:

I get result set as I expect, but SQL is really odd for me. There's a bunch of select statements. First it selects one customer, which is ok. Then it selects one Order (because this customer has only one), then is creates one select for each OrderItem in previously selected Order... So I get as many selects as there are OrderItems + Orders for selected Customer. So it looks like:

select top 1 from Customers;

select * from Orders where CustomerID = @cID;

select * from OrderItems where OrderID = @o1;
select * from OrderItems where OrderID = @o2;
select * from OrderItems where OrderID = @o3;
select * from OrderItems where OrderID = @o4;

我期望的是这样的:

select oi.* 
from OrderItems oi
join Orders o on o.OrderID = oi.OrderId
join Customers c on c.CustomerID = o.CustomerID
where c.CustomerID = @someID

一个不错的选择，干净整洁.

One select, nice and clean.

SelectMany确实可以那样工作吗，或者我做错了什么，或者我的模型出了什么问题?在这种简单的SelectMany应该如何转换为SQL的示例中，我找不到任何地方.

Does SelectMany really works like that or am I doing something wrong, or maybe something wrong is with my model? I can't find anywhere examples on how that kind of simple SelectMany should translate to SQL.

这对于小数量而言无关紧要，但是当客户拥有100个订单且每个订单包含200个订单商品时，那么将有2万个选择...

This doesn't matter for small numbers, but when a customer would have 100 orders with 200 order items each, then there would be 20 000 selects...

推荐答案

您应使用以下内容进行查询(使用someId查询特定客户的订单商品):

You should use the following for your query (to query for the order items of a specific customer with someId):

context.Customers.Where(c => c.Id == someId)
    .SelectMany(c => c.Orders.SelectMany(o => o.OrderItems))

或-使用单个数据库查询重现First()的行为:

Or - to reproduce the behaviour of First() but with a single DB query:

context.Customers.Take(1)
    .SelectMany(c => c.Orders.SelectMany(o => o.OrderItems))

您的原始查询使用(查询1)加载客户，然后惰性加载加载该客户的Orders集合(查询2)，然后再次惰性加载为每个加载的Order(查询3到n)加载订单项集合.为了避免所有这些多个查询，您一定不能在查询表达式内使用查询执行方法"，例如First()或ToList()等.

You original query loads the customer with First (query 1), then lazy loading loads the Orders collection of that customer (query 2), then lazy loading again loads the order items collection for each loaded Order (query 3 to n). To avoid all those multiple queries you must not use a "query execution method" like First() or ToList(), etc. inside of your query expression.

这篇关于SelectMany创建许多SQL select语句，而不是使用join的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！