Chapter 16. Criteria Queries

NHibernate features an intuitive, extensible criteria query API.

16.1. Creating an ICriteria instance

The interface NHibernate.ICriteria represents a query against a particular persistent class. The ISession is a factory for ICriteria instances.

ICriteria crit = sess.CreateCriteria<Cat>();
crit.SetMaxResults(50);
var cats = crit.List<Cat>();

16.2. Narrowing the result set

An individual query criterion is an instance of the interface NHibernate.Expression.ICriterion. The class NHibernate.Expression.Expression defines factory methods for obtaining certain built-in ICriterion types.

var cats = sess.CreateCriteria<Cat>()
    .Add( Expression.Like("Name", "Fritz%") )
    .Add( Expression.Between("Weight", minWeight, maxWeight) )
    .List<Cat>();

Expressions may be grouped logically.

var cats = sess.CreateCriteria<Cat>()
    .Add( Expression.Like("Name", "Fritz%") )
    .Add( Expression.Or(
        Expression.Eq( "Age", 0 ),
        Expression.IsNull("Age")
    ) )
    .List<Cat>();
var cats = sess.CreateCriteria<Cat>()
    .Add( Expression.In( "Name", new String[] { "Fritz", "Izi", "Pk" } ) )
    .Add( Expression.Disjunction()
        .Add( Expression.IsNull("Age") )
    	.Add( Expression.Eq("Age", 0 ) )
    	.Add( Expression.Eq("Age", 1 ) )
    	.Add( Expression.Eq("Age", 2 ) )
    ) )
    .List<Cat>();

There are quite a range of built-in criterion types (Expression subclasses), but one that is especially useful lets you specify SQL directly.

// Create a string parameter for the SqlString below
var cats = sess.CreateCriteria<Cat>()
    .Add(Expression.Sql("lower({alias}.Name) like lower(?)",
        "Fritz%", NHibernateUtil.String))
    .List<Cat>();

The {alias} placeholder with be replaced by the row alias of the queried entity.

16.3. Ordering the results

You may order the results using NHibernate.Expression.Order.

var cats = sess.CreateCriteria<Cat>()
    .Add( Expression.Like("Name", "F%")
    .AddOrder( Order.Asc("Name") )
    .AddOrder( Order.Desc("Age") )
    .SetMaxResults(50)
    .List<Cat>();

16.4. Associations

You may easily specify constraints upon related entities by navigating associations using CreateCriteria().

var cats = sess.CreateCriteria<Cat>()
    .Add( Expression.Like("Name", "F%")
    .CreateCriteria("Kittens")
        .Add( Expression.Like("Name", "F%") )
    .List<Cat>();

Note that the second CreateCriteria() returns a new instance of ICriteria, which refers to the elements of the Kittens collection.

The following, alternate form is useful in certain circumstances.

var cats = sess.CreateCriteria<Cat>()
    .CreateAlias("Kittens", "kt")
    .CreateAlias("Mate", "mt")
    .Add( Expression.EqProperty("kt.Name", "mt.Name") )
    .List<Cat>();

(CreateAlias() does not create a new instance of ICriteria.)

Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use SetResultTransformer(Transformers.AliasToEntityMap).

var cats = sess.CreateCriteria<Cat>()
    .CreateCriteria("Kittens", "kt")
        .Add( Expression.Eq("Name", "F%") )
    .SetResultTransformer(Transformers.AliasToEntityMap)
    .List<IDictionary>();
foreach ( IDictionary map in cats )
{
    Cat cat = (Cat) map[CriteriaSpecification.RootAlias];
    Cat kitten = (Cat) map["kt"];
}

Note that for retrieving just kittens you can also use an entity projection. See Section 16.8, “Projections, aggregation and grouping” for more information.

16.5. Join entities without association (Entity joins or ad hoc joins)

In criteria you have the ability to define a join to any entity, not just through a mapped association. To achieve it, use CreateEntityAlias and CreateEntityCriteria. By example:

IList<Cat> uniquelyNamedCats = sess.CreateCriteria<Cat>("c")
    .CreateEntityAlias(
        "joinedCat",
        Restrictions.And(
            Restrictions.EqProperty("c.Name", "joinedCat.Name"),
            Restrictions.NotEqProperty("c.Id", "joinedCat.Id")),
        JoinType.LeftOuterJoin,
        typeof(Cat).FullName)
    .Add(Restrictions.IsNull("joinedCat.Id"))
    .List();

16.6. Dynamic association fetching

You may specify association fetching semantics at runtime using Fetch().

var cats = sess.CreateCriteria<Cat>()
    .Add( Expression.Like("Name", "Fritz%") )
    .Fetch(SelectMode.Fetch, "Mate")
    .Fetch(SelectMode.Fetch, "Kittens")
    .List<Cat>();

This query will fetch both Mate and Kittens by outer join. See Section 21.1, “Fetching strategies” for more information.

16.7. Example queries

The class NHibernate.Expression.Example allows you to construct a query criterion from a given instance.

Cat cat = new Cat();
cat.Sex = 'F';
cat.Color = Color.Black;
var results = session.CreateCriteria<Cat>()
    .Add( Example.Create(cat) )
    .List<Cat>();

Version properties, identifiers and associations are ignored. By default, null-valued properties and properties which return an empty string from the call to ToString() are excluded.

You can adjust how the Example is applied.

Example example = Example.Create(cat)
    .ExcludeZeroes()           //exclude null- or zero-valued properties
    .ExcludeProperty("Color")  //exclude the property named "color"
    .IgnoreCase()              //perform case insensitive string comparisons
    .EnableLike();             //use like for string comparisons
var results = session.CreateCriteria<Cat>()
    .Add(example)
    .List<Cat>();

You can even use examples to place criteria upon associated objects.

var results = session.CreateCriteria<Cat>()
    .Add( Example.Create(cat) )
    .CreateCriteria("Mate")
        .Add( Example.Create( cat.Mate ) )
    .List<Cat>();

16.8. Projections, aggregation and grouping

The class NHibernate.Expression.Projections is a factory for IProjection instances. We apply a projection to a query by calling SetProjection().

var results = session.CreateCriteria<Cat>()
    .SetProjection( Projections.RowCount() )
    .Add( Expression.Eq("Color", Color.BLACK) )
    .List<int>();
var results = session.CreateCriteria<Cat>()
    .SetProjection( Projections.ProjectionList()
        .Add( Projections.RowCount() )
        .Add( Projections.Avg("Weight") )
        .Add( Projections.Max("Weight") )
        .Add( Projections.GroupProperty("Color") )
    )
    .List<object[]>();

There is no explicit "group by" necessary in a criteria query. Certain projection types are defined to be grouping projections, which also appear in the SQL group by clause.

An alias may optionally be assigned to a projection, so that the projected value may be referred to in restrictions or orderings. Here are two different ways to do this:

var results = session.CreateCriteria<Cat>()
    .SetProjection( Projections.Alias( Projections.GroupProperty("Color"), "colr" ) )
    .AddOrder( Order.Asc("colr") )
    .List<string>();
var results = session.CreateCriteria<Cat>()
    .SetProjection( Projections.GroupProperty("Color").As("colr") )
    .AddOrder( Order.Asc("colr") )
    .List<string>();

The Alias() and As() methods simply wrap a projection instance in another, aliased, instance of IProjection. As a shortcut, you can assign an alias when you add the projection to a projection list:

var results = session.CreateCriteria<Cat>()
    .SetProjection( Projections.ProjectionList()
        .Add( Projections.RowCount(), "catCountByColor" )
        .Add( Projections.Avg("Weight"), "avgWeight" )
        .Add( Projections.Max("Weight"), "maxWeight" )
        .Add( Projections.GroupProperty("Color"), "color" )
    )
    .AddOrder( Order.Desc("catCountByColor") )
    .AddOrder( Order.Desc("avgWeight") )
    .List<object[]>();
var results = session.CreateCriteria(typeof(DomesticCat), "cat")
    .CreateAlias("kittens", "kit")
    .SetProjection( Projections.ProjectionList()
        .Add( Projections.Property("cat.Name"), "catName" )
        .Add( Projections.Property("kit.Name"), "kitName" )
    )
    .AddOrder( Order.Asc("catName") )
    .AddOrder( Order.Asc("kitName") )
    .List<object[]>();

You can also add an entity projection to a criteria query:

var kittens = sess.CreateCriteria<Cat>()
    .CreateCriteria("Kittens", "kt")
    .Add(Expression.Eq("Name", "F%"))
    .SetProjection(Projections.Entity(typeof(Cat), "kt"))
    .List();
var cats = sess.CreateCriteria<Cat>()
    .CreateCriteria("Kittens", "kt")
    .Add(Expression.Eq("Name", "F%"))
    .SetProjection(
        Projections.RootEntity(),
        Projections.Entity(typeof(Cat), "kt"))
    .List<object[]>();

foreach (var objs in cats)
{
    Cat cat = (Cat) objs[0];
    Cat kitten = (Cat) objs[1];
}

See Section 17.9, “Entities Projection” for more information.

16.9. Detached queries and sub-queries

The DetachedCriteria class lets you create a query outside the scope of a session, and then later execute it using some arbitrary ISession.

DetachedCriteria query = DetachedCriteria.For<Cat>()
    .Add( Expression.Eq("sex", 'F') );

using (ISession session = ....)
using (ITransaction txn = session.BeginTransaction())
{
    var results = query.GetExecutableCriteria(session).SetMaxResults(100).List<Cat>();
    txn.Commit();
}

A DetachedCriteria may also be used to express a sub-query. ICriterion instances involving sub-queries may be obtained via Subqueries.

DetachedCriteria avgWeight = DetachedCriteria.For<Cat>()
    .SetProjection( Projections.Avg("Weight") );
session.CreateCriteria<Cat>()
    .Add( Subqueries.Gt("Weight", avgWeight) )
    .List<Cat>();
DetachedCriteria weights = DetachedCriteria.For<Cat>()
    .SetProjection( Projections.Property("Weight") );
session.CreateCriteria<Cat>()
    .Add( Subqueries.GeAll("Weight", weights) )
    .List<Cat>();

Even correlated sub-queries are possible:

DetachedCriteria avgWeightForSex = DetachedCriteria.For<Cat>("cat2")
    .SetProjection( Projections.Avg("Weight") )
    .Add( Expression.EqProperty("cat2.Sex", "cat.Sex") );
session.CreateCriteria(typeof(Cat), "cat")
    .Add( Subqueries.Gt("weight", avgWeightForSex) )
    .List<Cat>();