LINQ Pitfalls: Order of operators (cont.)

In the previous post I've shown that operators in NHibernate.Linq query have to keep some logical order due to SQL language design. But the rules for NHibernate queries are not as strict as order of clauses in SQL. In fact, there is quite a good compromise between LINQ's flexibility and SQL constraints.

NHibernate generally allows to move operators around as long as the result doesn't depend on the order of operators. For example, there's no logical difference between these two queries:

session.Query<River>()
    .OrderBy(x => x.Length)
    .Where(x => x.Length > 100)
    .ToList();

session.Query<River>()
    .Where(x => x.Length > 100)
    .OrderBy(x => x.Length)
    .ToList();

Both these queries produce correct SQL, even if the first one defines operations in non-SQL-compliant order (WHERE is before ORDER BY in SQL). NHibernate knows that applying Where restriction can be done at any time and it doesn't change the result. That's why it allows to put it almost anywhere in LINQ query. The only rule is to place it before paging operators (Skip and Take), as it does matter if we are paging before or after applying Where conditions.

Similarily with projections - Select operator can be placed in different query parts and it doesn't change the result. Theoretically it could be placed even after paging, but for some reason NHibernate is more SQL-compliant in this case and doesn't allow Take or Skip to precede Select.

So, besides Skip and Take, are there any other operators that need to be placed in specific query part? NHibernate doesn't like OrderBy placed before GroupBy, but this is rather bizzare combination and we may ignore it. All the other supported operators seems to be pretty portable - at least I haven't found any other problems when putting operators in non-SQL-compliant order. Well, NHibernate's Linq provider has to be extremally complex, it is not a surprise that it took 3 man-years to implement it.

How about non-standard, NHibernate-specific operators, such as Cacheable, CacheMode, ToFuture, Fetch, FetchMany? In NHibernate 3.2 it can move around, too. However, in NH 3.0 Fetch and FetchMany had to be the last operators, just before ToList or other method that triggers the database call. Otherwise, NotSupportedException was thrown.