How can I make this SelectMany use a Join?

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.

---

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.

---

How about this:

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

---

I am not 100% sure. But my guess is because you are traversing down the relationship that is how the query is built up, compared to the second solution where you are actually joining two sets by a value.

---

So after Francisco's answer and experimenting with LINQPad I have come up with a decent workaround.

var lines = from c in Customers
            where c == customer
            from o in c.CustomerOrders
            from l in o.OrderLines
            select l;

This forces the EntitySet into an Expression which the provider then turns into the appropriate query. The first two lines are the key, by querying the IQueryable and then putting the EntitySet in the SelectMany it becomes an expression. This works for the other operators as well, Where, Select, etc.

---

Try this query:

IQueryable<OrderLine> query =
  from c in myDataContext.customers.Take(1)
  from o in c.CustomerOrders
  from l in o.OrderLines
  select l;

You can go to the CustomerOrders property definition and see how the property acts when it used with an actual instance. When the property is used in a query expression, the behavior is up to the query provider - the property code is usually not run in that case.

See also this answer, which demonstrates a method that behaves differently in a query expression, than if it is actually called.