Recently in the project that is using NHibernate 3.2, I needed to use some aggregations in my database queries. The use case was pretty typical - aggregate some pre-filtered set of invoices by the product sold, count how many sales were there for each product, order the data by total sales value and take top 10 results. It is pretty easy to accomplish in SQL:
SELECT TOP 10 Product, COUNT(*) AS SaleCount, SUM(Value) AS TotalValue
FROM Invoices
WHERE Cancelled = 0
GROUP BY Product
ORDER BY TotalValue DESC
It is also pretty easy to express in LINQ syntax:
Invoices.Where(i => i.Cancelled == false)
.GroupBy(i => i.Product)
.Select(g => new TopSellingProduct
{
Product = g.Key,
SaleCount = g.Count(),
TotalValue = g.Sum(i => i.Value)
})
.OrderByDescending(g => g.TotalValue)
.Take(10);
I knew that NHibernate's LINQ provider offers limited support for GroupBy operator. Taking into consideration that all the lambda expressions in the query are in fact expression trees that need to be parsed and expressed in SQL, what I expected to be the most problematic, was the Select clause that creates new TopSellingProduct instances (which is not a NHibernate-managed entity) and sets its properties, in case of Sum even using nested lambdas. Actually, this was not a problem at all, even when using anonymous types inside - impressive! NHibernate somehow gets the list of fields and aggregation functions that needs to be fetched and turns it into SELECT clause correctly.
But the query above couldn't be translated into SQL anyway. It turned out that the operators that seemed easier to implement - OrderBy, Take and Skip - were not supported. So with NHibernate 3.2, I could only create an aggregation and fetch all the aggregated values at once, without ordering or paging. In my case, it could mean fetching 50k rows just to show top 10. Not an option.
Fortunately, quick search through the NHibernate's JIRA dashboard gave me the hope that things look better with the newer NHibernate version - 3.3.1. I've upgraded seamlessly using NuGet, and here is the summary of my observations:
| SQL feature | LINQ syntax example | NHibernate 3.2 | NHibernate 3.3.1 |
|---|---|---|---|
| SELECT of simple aggregated value; COUNT() function | .GroupBy(x => ...).Select(g => g.Count()) | OK | OK |
| SELECT of anonymous class | .GroupBy(x => ...) .Select(g => new { g.Key, Count = g.Count() }) | OK | OK |
| SELECT of named class | .GroupBy(x => ...)
.Select(g => new MyType
{
Key = g.Key,
Count = g.Count()
}) | OK | OK |
| SUM(), MIN(), MAX() functions | .GroupBy(x => ...)
.Select(g => new
{
Sum = g.Sum(x => ...),
Min = g.Min(x => ...),
Max = g.Max(x => ...)
}) | OK | OK |
| AVG() function | .GroupBy(x => ...).Select(g => g.Avg(x => ...)) | buggy, truncates value to int (NH-2429) | OK |
| WHERE (condition applied before aggregation) | .Where(x => ...).GroupBy(x => ...) | OK | OK |
| HAVING (condition applied after aggregation) | .GroupBy(x => ...).Where(g => ...) | silent failure, produces subquery instead of HAVING clause and returns wrong results (NH-2883) | OK |
| ORDER BY (sorting) | .GroupBy(x => ...).OrderBy(g => ...) | MismatchTreeNodeException (NH-2781) | OK |
| TOP / LIMIT (number of results) | .GroupBy(x => ...).Take(10) | NotImplementedException | OK |
| OFFSET (paging support) | .GroupBy(x => ...).Skip(10) | NotImplementedException | OK |
Things look MUCH better now - everything what I need (and a bit more) is correctly supported with the newest LINQ provider.
Recently, in the project I work in I've stumbled upon a pretty useful class, being part of our project-wide infrastructure and heavily used through the project, marked with an [Obsolete] attribute. Unfortunately, the parameterless overload, without a message, was used. I didn't know that part of code well, so I decided to consult with the author what does it mean for this class to be obsolete. Quick research through the revision history traced back to a developer who is no longer working in the project. Also, noone in the team could point me to an alternative approach I should use, most probably because there was none yet. 