Yesterday I was writing a dashboard application to summarise data held in a SQL Server database. Part of the dashboard involves showing the average of a certain value in rows added in the last hour, day, week and month. In the good old days this would have been easy; I would have written a stored procedure involing the AVG function. However, as I was using NHibernate I thought I would try and do this without writing any SQL (or indeed HQL). While I am a big fan of NHibernate this was much less straightforward than writing some simple SQL.

Here is the code I came up with:

public double GetAverage(DateTime dateFrom, DateTime dateTo)
{
	var query = Session.QueryOver().SelectList(list => list.SelectAvg(item => item.Value))
		.Where(x => x.Date >= dateFrom && x.Date <= dateTo);

	try
	{
		return query.List().First();
	}
	catch (GenericADOException exception)
	{
		// Note that if there are no results for the period (and the average is null) then we will
		// get a specific error back from NHibernate. We filter this out and return 0:
		if (exception.Message.StartsWith("Unable to perform find[SQL: SQL not available]", StringComparison.OrdinalIgnoreCase) && 
		    exception.InnerException != null && 
		    exception.InnerException.Message.StartsWith("Value cannot be null", StringComparison.OrdinalIgnoreCase))
		{
		    return 0;
		}

		// In all other cases we re-throw:
		throw;
	}
}

I discovered that if the query is ran over a period containing no data NHibernate throws an exception, hence the error trapping which returns 0 if the exact error referring to zero records is encountered.

Interestingly, when I turned on Log4Net SQL logging I found that NHibernate generates exactly the same SQL I would have written myself in the good old days:

SELECT avg(cast(this_.Value as DOUBLE PRECISION)) as y0_ 
FROM [Item] this_ 
WHERE 
	(this_.Date >= @p0 and this_.Date <= @p1);
@p0 = 28/03/2013 20:17:04 [Type: DateTime (0)], 
@p1 = 28/03/2013 21:17:04 [Type: DateTime (0)]

Nice work NHibernate!