SelectMany creates lots of SQL select statements instead of one with join

Question

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

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

I want to get all OrderItems for one customer.

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

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;

What I would expect is something like:

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.

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.

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...

Answer 1

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))

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))

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.