In the last chapter, we fetched our entities by their Id
. In this recipe, we'll show you a few basic criteria queries to fetch entities by other properties.
QueryByCriteria
to the project.CriteriaQueries
to the folder:using System.Collections.Generic; using NH4CookbookHelpers.Queries; using NH4CookbookHelpers.Queries.Model; using NHibernate; using NHibernate.Criterion; using NHibernate.SqlCommand; namespace QueryRecipes.QueryByCriteria { public class CriteriaQueries : IQueries { private readonly ISession _session; public CriteriaQueries(ISession session) { _session = session; } public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.CreateCriteria<Movie>() .Add(Restrictions.Eq("Director", directorName)) .List<Movie>(); } public IEnumerable<Movie> GetMoviesWith(string actorName) { return _session.CreateCriteria<Movie>() .CreateCriteria("Actors", JoinType.InnerJoin) .Add(Restrictions.Eq("Actor", actorName)) .List<Movie>(); } public Book GetBookByISBN(string isbn) { return _session.CreateCriteria<Book>() .Add(Restrictions.Eq("ISBN", isbn)) .UniqueResult<Book>(); } public IEnumerable<Product> GetProductsByPrice(decimal minPrice, decimal maxPrice) { return _session.CreateCriteria<Product>() .Add(Restrictions.And( Restrictions.Ge("UnitPrice", minPrice), Restrictions.Le("UnitPrice", maxPrice) )) .AddOrder(Order.Asc("UnitPrice")) .List<Product>(); } } }
class
Recipe to the folder.using NH4CookbookHelpers.Queries; using NHibernate; namespace QueryRecipes.QueryByCriteria { public class Recipe : QueryRecipe { protected override void Run(ISession session) { var queries=new CriteriaQueries(session); ShowQueryResults(queries); } } }
Again, let's work through each of these four queries individually:
GetMoviesDirectedBy
query:_session.CreateCriteria<Movie>() .Add(Restrictions.Eq("Director", directorName)) .List<Movie>();
In the preceding code, we use session.CreateCriteria
to get an ICriteria
object. Our generic parameter, Movie
, tells NHibernate that we're going to query on movies. In the second line, we restrict the movies to only those directed by n. Finally, we call the List
method, which executes the query and returns our Steven Spielberg
movies. Due to the generic parameter Movie
, NHibernate returns a strongly typed IList<Movie>
instead of an IList
.
This results in the following SQL query:
SELECT this_.Id as Id1_0_, this_.Name as Name1_0_, this_.Description as Descript4_1_0_, this_.UnitPrice as UnitPrice1_0_, this_.Director as Director1_0_ FROM Product this_ WHERE this_.ProductType = 'Movie' AND this_.Director = 'Steven Spielberg'
GetMoviesWith
query:_session.CreateCriteria<Movie>() .CreateCriteria("Actors", JoinType.InnerJoin) .Add(Restrictions.Eq("Actor", actorName)) .List<Movie>();
We are again querying movies, but in this example, we are querying based on a child collection. We want all the Morgan Freeman's movies. In terms of our model, we want to return all of the Movies
with an associated ActorRole
object where the Actor
property equals the string 'Morgan Freeman'
.
The second line sets up an inner join between Movies
and ActorRoles
based on the contents of a Movie's
Actors
collection. In SQL an inner join only returns the rows with a match. CreateCriteria
also changes the context of the query from Movie
to ActorRole
. This allows us to filter our ActorRoles
further on the third line.
On the third line, we simply filter the ActorRole
objects down to only Morgan Freeman's roles. Because of the inner join, this also filters the Movies
. Finally, we execute the query and get the results with a call to List<Movie>
.
Here is the resulting SQL query:
SELECT this_.Id as Id1_1_, this_.Version as Version1_1_, this_.Name as Name1_1_, this_.Description as Descript5_1_1_, this_.UnitPrice as UnitPrice1_1_, this_.Director as Director1_1_, actorrole1_.Id as Id0_0_, actorrole1_.Version as Version0_0_, actorrole1_.Actor as Actor0_0_, actorrole1_.Role as Role0_0_ FROM Product this_ inner join ActorRole actorrole1_ on this_.Id = actorrole1_.MovieId WHERE this_.ProductType = 'Movie' AND actorrole1_.Actor = 'Morgan Freeman'
GetBookByISBN
query:_session.CreateCriteria<Book>() .Add(Restrictions.Eq("ISBN", isbn)) .UniqueResult<Book>();
In this criteria query, we're searching for a particular book by its ISBN. However, since we use UniqueResult<Book>
instead of List<Book>
, NHibernate returns a single Book
object or null if it's not found. This query assumes that ISBN
is unique.
We get this simple SQL query:
SELECT this_.Id as Id1_0_, this_.Name as Name1_0_, this_.Description as Descript4_1_0_, this_.UnitPrice as UnitPrice1_0_, this_.Author as Author1_0_, this_.ISBN as ISBN1_0_ FROM Product this_ WHERE this_.ProductType = 'Eg.Core.Book' AND this_.ISBN = '3043'
GetProductByPrice
query:_session.CreateCriteria<Product>() .Add(Restrictions.And( Restrictions.Ge("UnitPrice", minPrice), Restrictions.Le("UnitPrice", maxPrice) )) .AddOrder(Order.Asc("UnitPrice")) .List<Product>()
With this criteria query, we combine a greater than or equal to operation and a less than or equal to operation using an And
operation to return products priced between two values. The And
restriction takes two child restrictions as parameters.
We can also use the Between
restriction to create an equivalent criteria query, such as this:
.Add(Restrictions.Between("UnitPrice", minPrice, maxPrice))
We use the AddOrder
method to sort our product results by ascending unit price.
Here's the resulting SQL query:
SELECT this_.Id as Id1_0_, this_.Name as Name1_0_, this_.Description as Descript4_1_0_, this_.UnitPrice as UnitPrice1_0_, this_.Director as Director1_0_, this_.Author as Author1_0_, this_.ISBN as ISBN1_0_, this_.ProductType as ProductT2_1_0_ FROM Product this_ WHERE (this_.UnitPrice >= 0 /* @p0 */ and this_.UnitPrice <= 15 /* @p1 */) ORDER BY this_.UnitPrice asc
The criteria API is intended for dynamically built queries, such as the advanced search feature we see on retail websites, where the user may choose any number of filters and sort criteria. However, these queries must be parsed and compiled on the fly.
For relatively static queries, with a set of well-known parameters, it is preferable to use named HQL queries, as these are precompiled when we build the session factory.
The criteria API suffers from the magic strings
problem, where strings refer to properties and classes in our application. With strongly typed APIs, we can easily change a property name using the refactoring tools of Visual Studio or ReSharper
. With the criteria API, when we change a property name in our model, we have to find and update every criteria query that uses the property. As we will see in the next recipe, the new QueryOver
API helps solve this problem.
3.129.25.217