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, I'll show you how to write QueryOver queries with projections and aggregates.
Queries
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] }); }
Queries
to fetch a simple average movie price:public decimal GetAverageMoviePrice() { var result = _session.QueryOver<Movie>() .Select(Projections.Avg<Movie>(m => m.UnitPrice)) .SingleOrDefault<double>(); return Convert.ToDecimal(result); }
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]) }); }
Program.cs
, use the following code in the RunQueries
method:static void RunQueries(ISession session) { var queries = new Queries(session); Show("Movie Price List:", queries.GetMoviePriceList()); Show("Average Movie Price:", queries.GetAverageMoviePrice()); Show("Average Price by Director:", queries.GetAvgDirectorPrice()); }
Again, we'll discuss each query separately. The queries are as follows:
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's Select
method. Our QueryOver ends with a call to List
. Because 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.
_session.QueryOver<Movie>() .Select(Projections.Avg<Movie>(m => m.UnitPrice)) .SingleOrDefault<double>();
In the previous code, we query for the average price of all movies in the database. We call our aggregate function through Projections.Avg
, and then project the result.
Because 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 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'
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 by and project the Director
property and project the average UnitPrice
, using this 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