NHibernate's methods of querying are powerful, but there's a learning curve. Longer term, Linq is the way to go, although it may not be as capable as Linq2Sql or Entity Framework (Hql and Criteria are arguably superior for advanced scenarios anyway).
- Get by primary key is built-in (session.Get and session.Load; latter loads a proxy which only does the database select when needed).
- HQL - a provider-neutral Sql-like language. Unfortunately it's just a big string. It's good for ststic queries and has the most advanced capabilities.
- Criteria - more object like and good for building dynamic runtime queries. Property names are still strings.
- QueryOver - (NHibernate 3+) uses lambdas over Criteria to make it strongly typed.
- Linq -
- In NHibernate 2 and 2.1, this is a NHibernate Contrib extension (as ISession.Linq<T>). You need to get the source and rebuild against the latest NHibernate 2.1 version. It works well for simple queries.
- From NHibernate 3.0, Linq is part of the NHibernate core (as ISession.Query<T>) and gives more advanced features, although not everything may be there.
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);
MultiQuery
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];
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;
Projecting to a DTO
See above for projections for aggregations such as counts. 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>();
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>();