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 whe…

Accepted Answer

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.

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

Solution

Problem