How can I make this SelectMany use a Join?

Question

Given that I have three tables (Customer, Orders, and OrderLines) in a Linq To Sql model where

Customer -- One to Many -> Orders -- One to Many -> OrderLines

When I use

var customer = Customers.First();
var manyWay = from o in customer.CustomerOrders
              from l in o.OrderLines
              select l;

I see one query getting the customer, that makes sense. Then I see a query for the customer's orders and then a single query for each order getting the order lines, rather than joining the two. Total of n + 1 queries (not counting getting customer)

But if I use

var tableWay = from o in Orders
               from l in OrderLines
               where o.Customer == customer
               && l.Order == o
               select l;

Then instead of seeing a single query for each order getting the order lines, I see a single query joining the two tables. Total of 1 query (not counting getting customer)

I would prefer to use the first Linq query as it seems more readable to me, but why isn't L2S joining the tables as I would expect in the first query? Using LINQPad I see that the second query is being compiled into a SelectMany, though I see no alteration to the first query, not sure if that's a indicator to some problem in my query.

Answer 1

I think the key here is

customer.CustomerOrders

Thats an EntitySet, not an IQueryable, so your first query doesn't translate directly into a SQL query. Instead, it is interpreted as many queries, one for each Order.

That's my guess, anyway.

Answer 2

How about this:

Customers.First().CustomerOrders.SelectMany(item => item.OrderLines)