NHibernate 3.0 added a new fluent syntax to criteria queries. Although it's not an actual LINQ provider, it does bring the familiar lambda syntax to criteria queries, eliminating the magic strings problem. In this recipe, we'll show you the QueryOver syntax for the criteria queries from our last recipe.
- Add a new folder named
QueryByQueryOverto the project. - Add a new class named
QueryOverQueriesto the folder:using System.Collections.Generic; using NH4CookbookHelpers.Queries; using NH4CookbookHelpers.Queries.Model; using NHibernate; namespace QueryRecipes.QueryByQueryOver { public class QueryOverQueries : IQueries { private readonly ISession _session; public QueryOverQueries(ISession session) { _session = session; } } } - Add the following method to the
QueryOverQueriesclass:public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { return _session.QueryOver<Movie>() .Where(m => m.Director == directorName) .List(); } - In the
QueryOverQueriesclass, add the following method to query for movies by actor's name:public IEnumerable<Movie> GetMoviesWith(string actorName) { return _session.QueryOver<Movie>() .OrderBy(m => m.UnitPrice).Asc .Inner.JoinQueryOver<ActorRole>(m => m.Actors) .Where(a => a.Actor == actorName) .List(); } - So we can query for a book by its ISBN by adding the following method:
public Book GetBookByISBN(string isbn) { return _session.QueryOver<Book>() .Where(b => b.ISBN == isbn) .SingleOrDefault(); } - Add the following method to find all the products in a price range:
public IEnumerable<Product> GetProductsByPrice( decimal minPrice, decimal maxPrice) { return _session.QueryOver<Product>() .Where(p => p.UnitPrice >= minPrice && p.UnitPrice <= maxPrice) .OrderBy(p => p.UnitPrice).Asc .List(); } - Add a
newclass namedRecipeto the folder:using NH4CookbookHelpers.Queries; using NHibernate; namespace QueryRecipes.QueryByQueryOver { public class Recipe : QueryRecipe { protected override void Run(ISession session) { var queries = new QueryOverQueries(session); ShowQueryResults(queries); } } } - Build and run your application. You should see the same screen as in the previous recipe.
In the preceding code, we've implemented the queries from the last recipe using NHibernate's QueryOver syntax. Using this syntax, most restrictions can be represented using the Where method, which takes a lambda expression as input. For example, to filter our movies by director's name, we use .Where(m => m.Director == directorName). In many cases, we can combine multiple restrictions in a single Where. To get products within a particular price range, we can write this:
.Where(p => p.UnitPrice >= minPrice) .And(p => p.UnitPrice <= maxPrice)
We can also combine it into one Where clause, as shown:
.Where(p => p.UnitPrice >= minPrice && p.UnitPrice <= maxPrice)
Some restrictions, such as Between, don't have equivalent lambda expressions. For these operations, we begin with WhereRestrictionOn to specify the property we'll use. Then, we follow it with a call to the restriction's method. For example, we could write this same price range filter using criteria's Between restriction:
.WhereRestrictionOn(p => p.UnitPrice) .IsBetween(minPrice).And(maxPrice)
To create a join, we use JoinQueryOver, as shown:
.Inner.JoinQueryOver<ActorRole>(m => m.Actors)
In QueryOver, UniqueResult is replaced with the LINQ-like SingleOrDefault.
QueryOver is built on top of NHibernate's existing criteria queries. Should we need to use the criteria API directly, we can get to the criteria query inside through the QueryOver objects's UnderlyingCriteria property.