NHibernate Tips: Criteria queries & Queries in native SQL

Criteria queries

HQL is extremely powerful but some people prefer to build queries dynamically, using an object oriented API, rather than embedding strings in their .NET code. For these people, NHibernate provides an intuitive ICriteria query API.

ICriteria crit = session.CreateCriteria(typeof(Cat));
crit.Add( Expression.Eq("color", Eg.Color.Black) );
crit.SetMaxResults(10);
IList cats = crit.List();

If you are uncomfortable with SQL-like syntax, this is perhaps the easiest way to get started with NHibernate. This API is also more extensible than HQL. Applications might provide their own implementations of the ICriterion interface.

Queries in native SQL

You may express a query in SQL, using CreateSQLQuery(). You must enclose SQL aliases in braces.

IList cats = session.CreateSQLQuery(
    "SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10",
    "cat",
    typeof(Cat)
).List();
IList cats = session.CreateSQLQuery(
    "SELECT {cat}.ID AS {cat.Id}, {cat}.SEX AS {cat.Sex}, " +
           "{cat}.MATE AS {cat.Mate}, {cat}.SUBCLASS AS {cat.class}, ... " +
    "FROM CAT {cat} WHERE ROWNUM<10",
    "cat",
    typeof(Cat)
).List()

SQL queries may contain named and positional parameters, just like NHibernate queries.