Using CriteriaQueries

In the last chapter, we fetched our entities by their Id. In this recipe, we'll show you a few basic criteria queries to fetch entities by other properties.

Getting ready

Complete the Getting Ready section at the beginning of this chapter.

How to do it…

  1. Add a new folder named QueryByCriteria to the project.
  2. Add a new class CriteriaQueries to the folder:
    using System.Collections.Generic;
using NH4CookbookHelpers.Queries;
using NH4CookbookHelpers.Queries.Model;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.SqlCommand;

namespace QueryRecipes.QueryByCriteria
{
    public class CriteriaQueries : IQueries
    {
        private readonly ISession _session;

        public CriteriaQueries(ISession session)
        {
            _session = session;
        }

        public IEnumerable<Movie> 
GetMoviesDirectedBy(string directorName)
        {
            return _session.CreateCriteria<Movie>()
              .Add(Restrictions.Eq("Director", 
               directorName))
              .List<Movie>();
        }


        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> 
GetProductsByPrice(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>();
        }
    }
}
  3. Add a new class Recipe to the folder.
    using NH4CookbookHelpers.Queries;
using NHibernate;

namespace QueryRecipes.QueryByCriteria
{
    public class Recipe : QueryRecipe
    {
        protected override void Run(ISession session)
        {
            var queries=new CriteriaQueries(session);
            ShowQueryResults(queries);
        }
    }
}
  4. Build and run your application.
  5. Follow the instructions in the Using CriteriaQueries recipe.
  6. You should see the following output:
    How to do it…

How it works…

Again, let's work through each of these four queries individually:

  • GetMoviesDirectedBy query:
    _session.CreateCriteria<Movie>()
        .Add(Restrictions.Eq("Director", directorName))
        .List<Movie>();

    In the preceding 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 n. Finally, we call the List method, which executes the query and returns our Steven Spielberg movies. Due to the generic parameter Movie, NHibernate returns a strongly typed IList<Movie> instead of an IList.

    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 = 'Movie'
         AND this_.Director = 'Steven Spielberg'
  • 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 the 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. In SQL 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:

    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 = 'Movie'
       AND actorrole1_.Actor = 'Morgan Freeman'
  • 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. However, since we use UniqueResult<Book> instead of List<Book>, NHibernate returns a single Book object or null if it's not found. This query assumes that 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'
  • 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 And operation to return products priced between two values. The And restriction takes two child restrictions as parameters.

    We can also use the Between restriction to create an equivalent criteria query, such as 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:

    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

There's more…

The criteria API is intended for dynamically built queries, such as the advanced search feature we see on retail websites, where the user may choose any number of filters 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.

See also

  • Using QueryOver
  • Using QueryOver projections and aggregates
  • Using MultiCriteria
  • Using named queries
  • Using detached queries
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.163.238