NHibernate Pitfalls: Take and Where Order in LINQ Queries
This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.
Starting with NHibernate 3.3.3, the order by which the Take and Where operators are placed on a LINQ query matters, whereas before this version, it didn’t. This is consistent to what we would expect from a SQL query.
For example, the two SQL queries produce different results:
1: SELECT *
2: FROM
3: (
4: SELECT product.name, product.price, product.product_id, ROW_NUMBER() OVER (ORDER BY product.product_id) rownum
5: FROM product
6: WHERE product.price > 10
7: ) q
8: WHERE rownum <= 2
9:
10: SELECT *
11: FROM
12: (
13: SELECT product.name, product.price, product.product_id, ROW_NUMBER() OVER (ORDER BY product.product_id) rownum
14: FROM product
15: ) q
16: WHERE rownum <= 2
17: AND q.price > 10
And so do the two LINQ queries:
1: var p1 = session.Query<Product>().Take(2).Where(x => x.Price > 10).ToList();
2: var p2 = session.Query<Product>().Where(x => x.Price > 10).Take(2).ToList();
See the release notes.
Of course, besides this, both LINQ and SQL queries will only provide consistent results when applying paging if a order is given, but you probably already know that.