Can I shape a LINQ query by modifying the IQueryable?

Question

I have a big LINQ query that results in inefficient SQL queries. I'm pretty sure I can optimize it a lot by shaping the query (like mentioned in this blog post by Scott Gu and also in this excellent blog post by Eugene Prystupa, who calls it "hinting").

However, this query is important legacy code, so I'd rather not touch the query itself. Is it possible to shape a LINQ query by modifying the resulting IQueryable?

For example, let's say the inefficient query is in GetOrders, which returns the query result as an IQueryable<Order>. Can I shape the IQueryable result? E.g.:

var result = GetOrders();

result = result.Select(o =>
    new { o, o.Id, o.Customer, o.Payment })
    .AsEnumerable()
    .Select(o => o.o);

Note the Select at the end, which is to get back Order objects. (See Prystupa's blog for this gem.) The point of this example shaping would be to get LINQ2SQL (or Entity Framework) to join the Order table with Customer and Payment. The original query would query those tables n times after querying Order, where n is the number of Orders.

Can you provide `GetOrders()` function first as I don't think that it return IQuerable? — Manprit Singh Sahota, Aug 04 '17 at 11:35
Why not? It returns the result of a `Where` on the Table. I.e., `return Db.Orders.Where(o => …` etc. — Protector one, Aug 04 '17 at 11:37
Do the return type of `GetOrders` is IQueryable or IEnumerable? — Manprit Singh Sahota, Aug 04 '17 at 11:39
EF has a [RawQuery](https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx) feature you can use it in with other queries. — Filip Cordas, Aug 04 '17 at 11:43
I answered something [unrelated](https://stackoverflow.com/questions/39258093/order-column-by-a-separate-liststring-variable/44210085#44210085) but there is a use of union values for some join operation might help. — Filip Cordas, Aug 04 '17 at 11:58
@Protectorone: if the method returns IQueryable, there's no translation to sql yet. You can then do pretty much whatever you want. But your question is somehow strange, you only ask "is it possible ..." and the answer to that is "yes". What kind of advice you actually expect then? — Wiktor Zychla, Aug 04 '17 at 15:31
Your query may end up weird from EF but it will reduce. SQL should optimise away parts such as subqueries that aren't used in the output. — Matthew Whited, Aug 04 '17 at 15:35
@Wiktor: Your comment is pretty much what I was hoping for as an answer; some actual documentation or other official document on the subject would be great! — Protector one, Aug 05 '17 at 16:53
@Protectorone: I don't unfortunately know any official resources on that and the actual implementation has many edge cases. Plus, different linq implementations can produce different queries out of same expression trees. It's all then about specific cases. — Wiktor Zychla, Aug 05 '17 at 18:45

score 0 · Accepted Answer · answered Aug 09 '17 at 13:21

Through experimentation, I have found that this is indeed possible. As long as the query is still LINQ-to-SQL or LINQ-to-Entities and not LINQ-to-Objects, you can still shape the query. (A query will become LINQ-to-Objects when you put the result set in memory by calling something like ToList or AsEnumerable on it.)

A big caveat though: you can only get SQL Joins on one-to-one relationship children. If you include one-to-many or many-to-many children in the anonymous object of the Select, the generated SQL will still have n additional queries to the tables of the children, where n is the number of records. This tripped me up for a long time and had me thinking the shaping wasn't doing anything at all.

I still wish I could find an official document on this matter, though.

score -1 · Answer 2 · answered Aug 07 '17 at 21:10

I believe, as pointed out by the blog article, you have to do a AsEnumerable or else the intermediate select will be optimized out of the query:

var result = GetOrders();

result = result.Select(o => new { o, o.Id, o.Customer, o.Payment })
               .AsEnumerable()
               .Select(o => o.o);

Can I shape a LINQ query by modifying the IQueryable?

2 Answers2