Hi there,

currently, I am trying to use aggregate functions with NHibernate. Basically, I want to calculate the average value of a table's column and getting the total number of records of this table at the same time - but both only for records that match a specific critera.

In SQL Server this works:

select avg(Stars), count(*) from Rating where Page = 'Default.aspx'

The result are two columns, the first one with 42, the second one with 23 as value.

Now I am trying to run this query with NHibernate by creating a query and specifying the file name as named parameter. I run this query by calling Enumerable() and get an IEnumerable as result - well, what am I going to do with this?

Casting to object[] fails, although IMHO results with more than one column should be object[], shouldn't they? The exception tells me that casting from Int64 to object[] is not possible.

So, I have tried to run this query as two queries - one for avg, one for count. Since now only one value is returned, I have tried to call UniqueResult to get the scaler value - but it does not work, too.

C#-Code:
double? average = session.CreateQuery("select avg(Stars) from Rating where Page = :Page").SetString("Page", url.Substring(url.LastIndexOf('/') + 1)).UniqueResult<double?>();
int count = session.CreateQuery("select count(*) from Rating where Page = :Page").SetString("Page", url.Substring(url.LastIndexOf('/') + 1)).UniqueResult<int>();

NHibernate tells me, that the result is not unique ... in both cases 23 rows are returned (which is the number of rows) - why?

Any idea what this might be, and how I can fix this?

Thanks,


Golo

This seems strange to me, but all the examples fromthe docs use aliases. Just for kicks, have you tried using one? Like:

Seems worth a try.

Hi,

thanks for your hint - it has been worth a lot :-). Now I have:

double? average = session.CreateQuery("select avg(r.Stars) from Rating r where r.Page = :Page").SetString("Page", url.Substring(url.LastIndexOf('/') + 1)).UniqueResult<double?>();
int count = session.CreateQuery("select count(*) from Rating r where r.Page = :Page").SetString("Page", url.Substring(url.LastIndexOf('/') + 1)).UniqueResult<int>();

and the first one works (although the results always get casted to int, no idea why).

The second one now gives me a "The specified cast is invalid" ... any idea what still might be wrong?

Have a nice day,


Golo

Okay, the question with "count" is answered ... count returns long, not int ... just change the data type to long and it works :-).

So, there is only one question left - why does AVG return an int, not a double?

Okay, this question can also be answered: Stars is an INT column, and SQL Server 2005 returns INT when you calculate AVG on an INT, it returns DOUBLE when you calculate AVG on a DOUBLE column.

Since I do not want to change the column type, how can I modify the query that it returns a double?

Either cast ( "avg( cast( r.Stars as float ) )..." ) or multiply by 1.0 ("avg( r.Stars * 1.0)"). NH might be able to do both.

Great :-)!

You made my day :-)!

The first one results in an error ("syntax error near cast"), but the second one works as expected :-)!

Thanks a lot for your help!