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.

One order can have many customers? Or are those arrows not important? — FlyingStreudel, Jul 06 '11 at 21:05
One order one customer. Sorry I changed it to hopefully convey the meaning better. — David, Jul 06 '11 at 21:37

score 2 · Accepted Answer · answered Jul 06 '11 at 22:16

2

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.

answered Jul 06 '11 at 22:16

Francisco

4,104
3
24
27

Thank you Francisco, it makes sense now. Though this does not answer the question directly it DOES point me in the right direction. I will be adding my own answer and don't feel right making my own answer the correct one. – David Jul 07 '11 at 15:42

score 1 · Answer 2 · answered Jul 06 '11 at 21:05

1

How about this:

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

answered Jul 06 '11 at 21:05

acermate433s

2,514
4
24
32

That produced the same n+1 queries. I'm running these through LinqPad to see the results. – David Jul 06 '11 at 21:35

score 1 · Answer 3 · answered Jul 06 '11 at 21:06

1

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.

answered Jul 06 '11 at 21:06

Tomas Jansson

22,767
13
83
137

But shouldn't traversing the relationship tell L2S to use a Join anyway? Since it knows about the connection between the two tables. – David Jul 06 '11 at 21:31
Of course it probably tells L2S about the connection, but that is the way L2S is implemented. My guess is that the implementation is cleaner and easier to extend the way it is now, but I see your point. – Tomas Jansson Jul 07 '11 at 07:01

score 1 · Answer 4 · edited May 23 '17 at 12:04

1

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.

edited May 23 '17 at 12:04

Community

1
1

answered Jul 06 '11 at 22:29

Amy B

108,202
21
135
185

That produces the same n+1 queries. Interesting note though is that that query does not return an IQueryable, it returns an IEnumerable. Must be because of the EntitySet. – David Jul 07 '11 at 14:35

David · Answer 5 · 2011-07-07T16:50:34.307

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.

How can I make this SelectMany use a Join?

5 Answers5

Linked