NHibernate 3.0 has 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, I'll show you the new QueryOver syntax for the criteria queries from our last recipe.
- 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.QueryOver<Movie>() .Where(m => m.Director == directorName) .List(); } - In the
Queriesclass, 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> GetProductByPrice( decimal minPrice, decimal maxPrice) { return _session.QueryOver<Product>() .Where(p => p.UnitPrice >= minPrice && p.UnitPrice <= maxPrice) .OrderBy(p => p.UnitPrice).Asc .List(); } - 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 screenshot:
In the previous code, we've implemented the queries from the last recipe using NHibernate's new 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 on director 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 could write this:
.Where(p => p.UnitPrice >= minPrice) .And(p => p.UnitPrice <= maxPrice)
We could also combine it into one Where, like this:
.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, like this:
.Inner.JoinQueryOver<ActorRole>(m => m.Actors)
In QueryOver, UniqueResult is replaced with the LINQ-like SingleOrDefault.