static void

NHibernate Queries

NHibernate's methods of querying are powerful. NHibernate's older HQL and Criteria/QueryOver support advanced scenarios, but there's a learning curve. NHibernate supports Linq (session.Query) but it is not quite as capable as Linq2Sql or Entity Framework (which have their own limitations).

See more NHibernate query features

Lists with restrictions

//directly get by id (see also Load<> - loads proxy)
var category = session.Get<Category>(2);
 
//hql
var hqlQuery = session.CreateQuery("from Product p where p.Category.Id = ? order by p.Id")
    //set the parameter
    .SetInt32(0, 2)
    //second page of 10
    .SetFirstResult(10).SetMaxResults(10);
 
var list = hqlQuery.List<Product>();
 
//criteria
var criteria = session.CreateCriteria<Product>()
    //"Restrictions" used to be "Expression"
    .Add(Restrictions.Eq("Category.Id", 2))
    //ordering
    .AddOrder(NHibernate.Criterion.Order.Asc("Id"))
    //paging, 2nd page of 10
    .SetFirstResult(10) //zero based
    .SetMaxResults(10);
 
var list2 = criteria.List<Product>();
 
//query over
var queryOver = session.QueryOver<Product>()
    .Where(x => x.Category.Id == 2)
    //simple restrictions- And or &&/||
    //.And(x => !x.Discontinued)
    //.And(x => !x.Discontinued && x.UnitsInStock > 0)
    .OrderBy(x => x.Id).Asc
    .Skip(10)
    .Take(10);
var list3 = queryOver.List();
 
 
//using NHibernate.Linq (session.Linq in NH 2/session.Query in NH3)
var linq = (from product in session.Query<Product>()
            where product.Category.Id == 2
            orderby product.Id
            select product)
    .Skip(10)
    .Take(10);
var list4 = linq.ToList();

Single Results

//HQL counts
var hqlCountQuery = session.CreateQuery("select count(*) from Product p where p.Category.Id = ?")
            .SetInt32(0, 2);
var count1 = hqlCountQuery.UniqueResult<long>(); //always a long
 
//criteria counts
var countCriteria = session.CreateCriteria<Product>()
    //add rowcount projection - NB: RowCountInt64 for long
        .SetProjection(Projections.RowCount())
        .Add(Restrictions.Eq("Category.Id", 2));
var count2 = countCriteria.UniqueResult<int>();
 
//queryOver counts
var count3 = session.QueryOver<Product>()
    .Where(x => x.Category.Id == 2)
    .RowCount();
 
//linq counts
var count4 = session.Query<Product>().Count(p => p.Category.Id == 2);

Joins

In criteria, use a nested criteria or alias.

//no join, it knows Id is on Product
var list1 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq("Discontinued", false))
    .Add(Restrictions.Eq("Category.Id", 2))
    .List<Product>();
 
//for any other category properties create nested criteria
var list2 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq("Discontinued", false))
    .CreateCriteria("Category")
        .Add(Restrictions.Eq("CategoryName", "Condiments"))
    .List<Product>();
 
//use alias to flatten
var list4 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq("Discontinued", false))
    .CreateAlias("Category", "c")
    .Add(Restrictions.Eq("c.CategoryName", "Condiments"))
    .List<Product>();
 
//queryOver with join
var qover = session.QueryOver<Product>()
    .Where(x => !x.Discontinued)
    .JoinQueryOver(x => x.Category)
    .Where(c => c.CategoryName == "Condiments")
    .List();
 
//queryOver with join and aliases
Product productAlias = null; //you need null objects
Category categoryAlias = null;
var qoAlias = session.QueryOver(() => productAlias)
    .JoinQueryOver(x => x.Category, () => categoryAlias)
    //you can use the simple
    .Where(() => categoryAlias.CategoryName == "Condiments")
    .And(() => !productAlias.Discontinued)
    .List();
 
//linq
var linq = (from product in session.Query<Product>()
            join category in session.Query<Category>()
                 on product.Category.Id equals category.Id
            where category.CategoryName == "Condiments"
            && !product.Discontinued
            select product).ToList();

Subqueries

//with HQL
var hqlList = session.CreateQuery(
    @"from Product p where p.Id in
        (select n.Id from Product n
         where (n.UnitsInStock = :units
         or n.Discontinued = :dis))
         and p.Category.Id = :cat")
    .SetInt16("units", (short)0)
    .SetBoolean("dis", true)
    .SetInt32("cat", 2)
    .List<Product>();
 
//with Criteria and DetachedCriteria
var notForSale = DetachedCriteria.For<Product>("noSale")
    //for subquery you must project
    .SetProjection(Projections.Property("noSale.id"))
    .Add(Restrictions.Disjunction()
        .Add(Restrictions.Eq("noSale.UnitsInStock", (short)0))
        .Add(Restrictions.Eq("noSale.Discontinued", true)))
    .Add(Restrictions.Eq("Category.Id", 2));
 
var criteriaList = session.CreateCriteria<Product>()
    //the id must be in our subquery select
    .Add(Subqueries.PropertyIn("Id",notForSale))
    .List<Product>();
 
//with QueryOver (NH3)
var detachedQueryOver = QueryOver.Of<Product>()
    //you can .And or use simple expressions with && and ||
    .Where(x => x.UnitsInStock == 0 || x.Discontinued)
    .And(x=> x.Category.Id == 2)
    .Select(x => x.Id) //simple projection
    ;
var queryOverList = session.QueryOver<Product>()
    .WithSubquery
    .WhereProperty(x => x.Id)
    .In(detachedQueryOver)
    .List();
 
//NH Linq doesn't support subqueries :(
var linq = from product in session.Query<Product>()
           where
            (product.Discontinued
            || product.UnitsInStock == 0)
           && product.Category.Id == 2
           select product;