CreateSQLQuery SQL and scalar

des · **Joined:** Tue Oct 03, 2006 7:25 am **Posts:** 8

I was trying to retrieve COUNT(*) of rows by using CreateSQLQuery but realised that it's not possible at this moment:

session.CreateSQLQuery(@"SELECT count(*) AS {r.id} FROM Rez {r} WHERE f_AgeInDays({r}.Create_Date, 'NOW') <= 7", "Rez", typeof(Rez));

It seems that type and alias are obligate parameters - is it possible to somehow workaround it?

Version: NHibernate-1.2.0.Beta1-debug

Thanks!

guymahieu · **Joined:** Sat Sep 09, 2006 5:55 am **Posts:** 23

It may work better if you bind 'r' to typeof(Rez), since you use 'r' to identify it between the {}'s:

Code:

session.CreateSQLQuery(@"SELECT count(*) AS {r.id} FROM Rez {r} WHERE f_AgeInDays({r}.Create_Date, 'NOW') <= 7", "r", typeof(Rez));

I don't know if you really want to use the id property for this; you might want to check out the new scalar query feature for native sql. It's under "13.1.1. Scalar queries" in the documentation located in the doc folder of your nhibernate installation.

You'd probably end up with something like:

Code:

sess.CreateSQLQuery(@"SELECT count(*) as REZ_COUNT FROM Rez WHERE f_AgeInDays(Rez.Create_Date, 'NOW') <= 7")
 .AddScalar("REZ_COUNT", NHibernateUtil.Int64);

An IList will be returned containing the scalar values (they might be wrapped in object arrays, but I'm not sure).

Hope this helps.

mnichols · **Joined:** Mon Aug 21, 2006 9:18 am **Posts:** 179

Look into the new Projection Criteria that is part of NHib 1.2 beta 1. It has things like Projections.Count(propertyName) and when combined with Expression calls will do what you are after.