Today, I was dumbfounded by LINQ's optimization skills. I built a query over a list of items, which have an assigned category and a display order. I wanted to fetch a list of the distinct categories, to display them as an overview, and have them sorted according to the order of the entries themselves. My LINQ query looked something like this:
var categories = context.Entries.OrderBy(e => e.DisplayOrder).Select(e => e.Category).Distinct();
Everything seemed to work, but when I started ordering things around, the categories would remain as they were. What I also noticed, was that they were ordered alphabetically, which perplexed me. I decided to look at the generated query, and lo and behold, there was not a single ORDER BY clause in it. I took the generated query, added my own order by clause to it, and got an error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Of course: Distinct probably just sorts the values to make filtering out duplicates easier. I left out the Distinct() method, and my ordering was the way I wanted to have it.
I have not yet worked on a sql-based solution for this, since my structure holding the categories handled duplicate filtering by itself. And my data barely has any duplicates in it anyways, so I left it at that.
But what amazed me, was that LINQ actually left out the order by on its own accord, since it would have caused an error. The LINQ to SQL parser really knows, what it's doing, although in my case, I would have liked to get some sort of warning, that the query will not behave as expected. Nonetheless, I love LINQ 