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.
- Complete the setup steps in the introduction at the beginning of this chapter.
- In the
Queriesclass, add the following method:public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.CreateCriteria<Movie>() .Add(Restrictions.Eq("Director", directorName)) .List<Movie>(); } - In the
Queriesclass, 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>(); } - To query for a book by its ISBN, add the following method:
public Book GetBookByISBN(string isbn) { return _session.CreateCriteria<Book>() .Add(Restrictions.Eq("ISBN", isbn)) .UniqueResult<Book>(); } - Add the following method to find all the products in a price range:
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>(); } - In
Program.cs, use the following code for theRunQueriesmethod: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)); } - Build and run your application. You should see the following output:
Let's work through each of these four queries individually.
- GetMoviesDirectedBy query
_session.CreateCriteria<Movie>() .Add(Restrictions.Eq("Director", directorName)) .List<Movie>();In the above code, we use
session.CreateCriteriato get anICriteriaobject. 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 theListmethod, which executes the query and returns our Steven Spielberg movies. Because of the generic parameterMovie, NHibernate returns a strongly typedIList<Movie>instead of anIList.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 */ - 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 of Morgan Freeman's movies. In terms of our model, we want to return all of the
Movieswith an associatedActorRoleobject where theActorproperty equals the string'Morgan Freeman'.The second line sets up an inner join between
MoviesandActorRolesbased on the contents of aMovie's Actorscollection. Remember from SQL that an inner join only returns the rows with a match.CreateCriteriaalso changes the context of the query fromMovietoActorRole. This allows us to filter ourActorRolesfurther on the third line.On the third line, we simply filter the
ActorRoleobjects down to only Morgan Freeman's roles. Because of the inner join, this also filters theMovies. Finally, we execute the query and get the results with a call toList<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 */ - 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. Because we use
UniqueResult<Book>instead ofList<Book>, NHibernate returns a singleBookobject, 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 */ - 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
Andoperation to return products priced between two values. TheAndrestriction takes two child restrictions as parameters.We could also use the
Betweenrestriction to create an equivalent criteria query like this:.Add(Restrictions.Between("UnitPrice", minPrice, maxPrice))We use the
AddOrdermethod 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.