Using QueryOver projections and aggregates

In some cases, we only need specific properties of an entity. In other cases, we may need the results of an aggregate function, such as average or count. In this recipe, we'll show you how to write QueryOver queries with projections and aggregates.

Getting ready

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

How to do it…

  1. Add a new folder named QueryOverProjections to the project.
  2. Add a new class named QueryOverAggregateQueries to the folder, having the following code:
    using System;
using System.Collections.Generic;
using System.Linq;
using NH4CookbookHelpers.Queries;
using NH4CookbookHelpers.Queries.Model;
using NHibernate;
using NHibernate.Criterion;

namespace QueryRecipes.QueryOverProjections
{
  public class QueryOverAggregateQueries : 
    IAggregateQueries
  {
    private readonly ISession _session;

    public QueryOverAggregateQueries(ISession session)
    {
      _session = session;
    }
  }
}
  3. Add the following method to the class:
    public IEnumerable<NameAndPrice> GetMoviePriceList()
{
  return _session.QueryOver<Movie>()
    .Select(m => m.Name, m => m.UnitPrice)
    .List<object[]>()
    .Select(props =>
      new NameAndPrice()
      {
        Name = (string)props[0],
        Price = (decimal)props[1]
      });
}
  4. Add the following method to the class to fetch a simple average of movie prices:
    public decimal GetAverageMoviePrice()
{
  var result = _session.QueryOver<Movie>()
    .Select(Projections.Avg<Movie>(m => m.UnitPrice))
    .SingleOrDefault<double>();
  return Convert.ToDecimal(result);
}
  5. Add the following method to get a list of directors and the average price of their movies:
    public IEnumerable<NameAndPrice> GetAvgDirectorPrice()
{
  return _session.QueryOver<Movie>()
    .Select(list => list
      .SelectGroup(m => m.Director)
      .SelectAvg(m => m.UnitPrice)
    )
    .List<object[]>()
    .Select(props =>
      new NameAndPrice()
      {
        Name = (string)props[0],
        Price = Convert.ToDecimal(props[1])
      });
}
  6. Add a new class named Recipe to the folder:
    using NH4CookbookHelpers;
using NHibernate;

namespace QueryRecipes.QueryOverProjections
{
  public class Recipe : QueryRecipe
  {
    protected override void Run(ISession session)
    {
      var queries = new QueryOverAggregateQueries(session);
      ShowAggregateQueryResults(queries);
    }
  }
}
  7. Build and run your application. You should see the following output:
    How to do it…

How it works…

Again, we'll discuss each query separately. The queries are as follows:

  • GetMoviePriceList query:

    Here's the code we used for our Movie Price List query:

    _session.QueryOver<Movie>()
.Select(m => m.Name, m => m.UnitPrice)
.List<object[]>()
.Select(props =>
  new NameAndPrice()
  {
    Name = (string)props[0],
    Price = (decimal)props[1]
  });

    In this query, we want to return a list containing only movie names and their prices. To accomplish this, we project two properties from our Movie object: Name and UnitPrice. We do this using QueryOver objects Select method. Our QueryOver ends with a call to List. As we are returning the values of individual properties instead of entire Movie objects, our generic argument specifies that we'll return a list of object arrays. Each element in the list represents a row in our query results. The first element of each of those object arrays is the movie's Name. The second is the movie's UnitPrice.

    The resulting SQL query for Microsoft SQL Server is as follows:

    SELECT this_.Name      as y0_,
       this_.UnitPrice as y1_
FROM   Product this_
WHERE  this_.ProductType = 'Eg.Core.Movie'

    To return a list of strongly typed objects instead of these object arrays, we use a standard LINQ to Objects Select from System.Linq to put our query results into neat NameAndPrice objects.

  • GetAverageMoviePrice query:
    _session.QueryOver<Movie>()
.Select(Projections.Avg<Movie>(m => m.UnitPrice))
.SingleOrDefault<double>();

    In the preceding code, we query for the average price of all movies in the database. We call our aggregate functions through Projections.Avg and then project the result.

    Since we have projected a single aggregate result, we execute the query and get the result with a call to .SingleOrDefault<double>(). We expect a double to be returned by the average aggregate function. However, because we're dealing with money, we'll convert it to a decimal before returning it to our application.

    This QueryOver query results in the following SQL query:

    SELECT avg(cast(this_.UnitPrice as DOUBLE PRECISION)) as y0_
FROM   Product this_
WHERE  this_.ProductType = 'Eg.Core.Movie'
  • GetAvgDirectorPrice query:

    With the following code, we query for a list of movie directors and the average price of their movies:

    _session.QueryOver<Movie>()
.Select(list => list
  .SelectGroup(m => m.Director)
  .SelectAvg(m => m.UnitPrice)
)
.List<object[]>()
.Select(props =>
  new NameAndPrice()
  {
    Name = (string)props[0],
    Price = Convert.ToDecimal(props[1])
  });

    In this case, we will group and project the Director property and the average UnitPrice using the following syntax:

    .Select(list => list
  .SelectGroup(m => m.Director)
  .SelectAvg(m => m.UnitPrice)
)

    Just as we did in our first query, we return a list of object arrays and then transform them into a list of NameAndPrice objects with LINQ to Objects.

    Here is the resulting SQL query:

    SELECT   this_.Director as y0_,
         avg(cast(this_.UnitPrice as DOUBLE PRECISION)) as y1_
FROM     Product this_
WHERE    this_.ProductType = 'Eg.Core.Movie'
GROUP BY this_.Director

See also

  • Using CriteriaQueries
  • Using QueryOver
  • 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
3.147.66.128