In the last chapter, we fetched our entities by their ID. In this recipe, I'll show you a few basic criteria queries to fetch entities by other properties.
Queries
class, add the following method:public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.CreateCriteria<Movie>() .Add(Restrictions.Eq("Director", directorName)) .List<Movie>(); }
Queries
class, add the following method to query for movies by actor name: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> GetProductByPrice( 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>(); }
Program.cs
, use the following code for the RunQueries
method:static void RunQueries(ISession session) { var queries = new Queries(session); Show("Movies directed by Spielberg:", queries.GetMoviesDirectedBy( "Steven Spielberg")); Show("Movies with Morgan Freeman:", queries.GetMoviesWith( "Morgan Freeman")); Show("This book:", queries.GetBookByISBN( "978-1-849513-04-3")); Show("Cheap products:", queries.GetProductByPrice(0M, 15M)); }
Let's work through each of these four queries individually.
_session.CreateCriteria<Movie>() .Add(Restrictions.Eq("Director", directorName)) .List<Movie>();
In the above 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 Steven Spielberg. Finally, we call the List
method, which executes the query and returns our Steven Spielberg movies. Because of the generic parameter Movie
, NHibernate returns a strongly typed IList<Movie>
instead of an IList
.
In Microsoft SQL Server, 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 = 'Eg.Core.Movie' AND this_.Director = 'Steven Spielberg' /* @p0 */
_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 of 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. Remember from SQL that 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 in Microsoft SQL Server:
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 = 'Eg.Core.Movie' AND actorrole1_.Actor = 'Morgan Freeman' /* @p0 */
_session.CreateCriteria<Book>() .Add(Restrictions.Eq("ISBN", isbn)) .UniqueResult<Book>();
In this criteria query, we're searching for a particular book by its ISBN. Because we use UniqueResult<Book>
instead of List<Book>
, NHibernate returns a single Book
object, or null if it's not found. This query assumes 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' /* @p0 */
_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 could also use the Between
restriction to create an equivalent criteria query like 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 in Microsoft SQL Server:
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 many retail websites, where the user may choose any number of filter 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.