NHibernate 3.0 includes a new LINQ provider. In this recipe, I'll show you how to execute LINQ queries with NHibernate.
Queries
class:public IEnumerable<Movie> GetMoviesDirectedBy( string directorName) { var query = from m in _session.Query<Movie>() where m.Director == directorName select m; return query.ToList(); } public IEnumerable<Movie> GetMoviesWith( string actorName) { var query = from m in _session.Query<Movie>() where m.Actors.Any( ar => ar.Actor == actorName) select m; return query.ToList(); } public Book GetBookByISBN(string isbn) { var query = from b in _session.Query<Book>() where b.ISBN == isbn select b; return query.SingleOrDefault(); } public IEnumerable<Product> GetProductByPrice( decimal minPrice, decimal maxPrice) { var query = from p in _session.Query<Product>() where p.UnitPrice >= minPrice && p.UnitPrice <= maxPrice orderby p.UnitPrice ascending select p; return query.ToList(); } public IEnumerable<NameAndPrice> GetMoviePriceList() { var query = from m in _session.Query<Movie>() select new NameAndPrice( m.Name, m.UnitPrice); return query.ToList(); } public decimal GetAverageMoviePrice() { return _session.Query<Movie>() .Average(m => m.UnitPrice); } public IEnumerable<NameAndPrice> GetAvgDirectorPrice() { var query = from m in _session.Query<Movie>() group m by m.Director into g select new NameAndPrice( g.Key, g.Average(i => i.UnitPrice)); return query.ToList(); }
Program.cs
, use the following code in 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)); Show("Movie Price List:", queries.GetMoviePriceList()); Show("Average Movie Price:", queries.GetAverageMoviePrice()); Show("Average Price by Director:", queries.GetAvgDirectorPrice()); }
session.Query<>
returns an IQueryable
for the new, fully functional NHibernate LINQ provider. This recipe uses the same well-known LINQ syntax supported by many LINQ providers. MSDN's 101 LINQ samples, found at http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx, provide an excellent beginner's reference to LINQ syntax.