How to use NHibernate's SQLCriterion - take 2

After having an interesting discussion with Stefaan (colleague, friend, ex-colleague and now colleague again) about my previous post on NHibernate's SQLCriterion, I decided to have another look at my usage of SQLCriterion.

He argued that with SQLCriterion, you're more or less working around the ORM, which I can agree with. After putting it on StackOverflow, I got three answers and tried all three.

This is what I went with in the end:

ISessionFactory sessionFactory = GetSessionFactory(); 
ISession session = sessionFactory.GetCurrentSession(); 
ICriteria criteria = session.CreateCriteria(); 
return session.QueryOver().Where(c => c.Group.Id == groupId).List(); 

A more elegant solution.

How to use NHibernate's SQLCriterion

I recently needed to use NHibernate's SQLCriterion, but lost some time on finding out how to use it. Once you've found it, it's quite simple actually:


ISessionFactory sessionFactory = GetSessionFactory();
ISession session = sessionFactory.GetCurrentSession();
ICriteria criteria = session.CreateCriteria<Customer>();

var sqlString = new SqlString("{alias}.GroupId = " + groupId);
criteria.Add(new SQLCriterion(sqlString, new object[0], new IType[0]));

return criteria.List<Customer>();

The {alias} is there so NHibernate knows where to put the table name. I'm not so sure about the second and third argument of the SQLCriterion constructor, but in my case, I didn't need them. Just don't pass in null or you'll get an exception.

If you need a more advanced SQLCriterion, check out this post by Remco Ros.