static void

More NHibernate Queries

See the simple queries.

MultiQuery/ MultiCriteria

You can batch multiple queries in a single call. The Hql equivalent would use Multiquery; Criteria uses MultiCriteria.

//you can also use separate ICriterias with .Future / .FutureValue
IMultiCriteria multiCriteria = s.CreateMultiCriteria()
    .Add<Product>(
        s.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)
        )
    .Add(
        s.CreateCriteria<Product>()
        //add rowcount projection - NB: RowCountInt64 for long
        .SetProjection(Projections.RowCount())
        .Add(Restrictions.Eq("Category.Id", 2))
        )
    ;
var criteriaResults = multiCriteria.List();
IList<Product> products = (IList<Product>)criteriaResults[0];
int criteriaCount = (int)((IList)criteriaResults[1])[0];

MultiCriteria also works with NH 3.0's QueryOver.

//QueryOver.Of is equivalent to DetachedCriteria
var multiQueryOver = s.CreateMultiCriteria();
var pagedQuery = QueryOver.Of<Product>()
    .Where(x => x.Category.Id == 2)
    .OrderBy(x => x.Id).Asc
    .Skip(10)
    .Take(10);
 
var countQuery = QueryOver.Of<Product>()
    .Where(x => x.Category.Id == 2).ToRowCountQuery();
 
//named queries are a little easier to read than ordinals
multiQueryOver
    .Add("Page", pagedQuery)
    //projections have to be cast to specific type
    .Add<int>("Count", countQuery);
//multiQueryOver.List() is implicit
var pagedResult = (IList<Product>)multiQueryOver.GetResult("Page");
//always get List<T>
var total = ((IEnumerable<int>)multiQueryOver.GetResult("Count")).Single();

Projecting to a DTO: Criteria

Projections to single value aggregations (such as counts) are simple with IQuery/ICriteria.UniqueResult<T>.
Projecting to multiple values (for a DTO/ViewModel etc) can be awkward, especially for Criteria. The generated SQL only selects the required columns- not everything. The Transformers.AliasToBean is an ugly relic of the port from Java.

var proj = Projections.ProjectionList()
    //projected mapped class property to alias of dto property
    .Add(Projections.Property("ProductName"), "ProductName")
    .Add(Projections.Property("c.CategoryName"), "CategoryName")
    .Add(Projections.Property("UnitsInStock"), "Units");
 
var result = session.CreateCriteria<Product>("p")
    .Add(Restrictions.Gt("UnitPrice", 10m))
    .CreateAlias("Category", "c")
    .Add(Restrictions.Eq("Category.Id", 2))
    .SetProjection(proj)
    .SetResultTransformer(
        NHibernate.Transform.Transformers.AliasToBean(typeof(Northwind.Dto.ProductLite)))
    .List<Northwind.Dto.ProductLite>();

Projecting to a DTO: HQL

In Hql, you can either have fun with object arrays, or you have to use a mappings import on the DTO.

IList results = session.CreateQuery(
     @"select p.ProductName, c.CategoryName, p.UnitsInStock
      from Product p join p.Category c
      where p.UnitPrice > 10 and c.Id = 2").List();
 
foreach (object[] row in results)
{
    string name = (string)row[0];
    string category = (string)row[1];
    var units = (short)row[2];
    var dto = new Northwind.Dto.ProductLite(name, category, units);
}
 
//need to import it
//<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
//  <import class="Northwind.Dto.ProductLite, Northwind" />
//</hibernate-mapping>
var results2 = session.CreateQuery(
     @"select new ProductLite(p.ProductName, c.CategoryName, p.UnitsInStock)
      from Product p join p.Category c
      where p.UnitPrice > 10 and c.Id = 2")
     .List<Northwind.Dto.ProductLite>();

Projecting to a DTO: QueryOver

QueryOver can project to an IList<object> like Hql, which is cast to our type in Linq2Objects:

//queryover and linq to get anonymous type
var result = session.QueryOver<Product>()
    .Where(p => p.Category.Id == 2 && p.UnitPrice > 10)
    .Select(p => p.ProductName,
            p => p.UnitsInStock)
    .List<object[]>()
    //now normal Linq to turn into anonymous or existing type
    .Select(o => new Northwind.Dto.ProductLite
                     {
                         //but we cast manually (careful about nulls)
                         ProductName = (string)o[0],
                         Units = Convert.ToInt32(o[1])
                     });

This is QueryOver with a group projection (SelectList/ Select...)

//grouped result
var groupedResult = session.QueryOver<Product>()
    //use SelectList/Select...
    .SelectList(list =>
        list.SelectGroup(p => p.Category.Id)
        .SelectAvg(p=> p.UnitPrice)
        )
    .List<object[]>()
    //normal Linq to turn into anonymous or existing type
    .Select(o => new
    {
        CategoryId = Convert.ToInt32(o[0]),
        AveragePrice = Convert.ToInt32(o[1])
    });

Projecting to a DTO: Linq

Linq makes all that ugliness go away. Nirvana.

var linq = from product in session.Query<Product>()
           join category in session.Query<Category>()
               on product.Category.Id equals category.Id
           where product.UnitPrice > 10 && category.Id == 2
           select new Northwind.Dto.ProductLite
                      {
                          ProductName = product.ProductName,
                          CategoryName = category.CategoryName,
                          Units = Convert.ToInt32(product.UnitsInStock)
                      };