Subqueries are often extremely useful when building queries. Unfortunately there’s not much out there on how to work with subqueries in QueryOver. In this post, I’ll dive into how to create the subqueries using QueryOver’s various subquery APIs.

This article is part of an ongoing series on NHibernate Queryover. Click here to see the table of contents.

There are two places you can use subqueries in a QueryOver query: the SELECT clause and the WHERE clause. Unfortunately you cannot use a subquery in the FROM clause of a QueryOver query.

Subqueries in the WHERE clause

As usual, there are several ways to generate identical SQL using QueryOver. I’ll go over each one in detail.

.WithSubquery

.WithSubquery is the most straightforward and natural way to create a subquery with QueryOver. Basically, using .WithSubquery allows you to use some property on TSubType (or a property belonging to an alias) with a subquery expression. After calling .WithSubquery, there are several methods that become available:

  • .Where
  • .WhereAll
  • .WhereSome
  • .WhereExists
  • .WhereNotExists
  • .WhereProperty

For the most part these are pretty straightforward, but I’ll go over each one.

.Where

You should use .WithSubquery.Where when your subquery returns a scalar value and you want to compare that value (using =, !=, <, >, >=, or <=) with a value from your outer query.

For example, here’s a query that finds the Product with the Transaction containing the highest quantity (one Product has many Transactions):

QueryOver:

var popularProduct = session.QueryOver<Product>()
    .WithSubquery.Where(pr => pr.Id ==
        QueryOver.Of<TransactionHistory>()
            .Select(tx => tx.Product.Id)
            .OrderBy(tx => tx.Quantity)
            .Desc
            .Take(1)
            .As<int>())
    .SingleOrDefault<Product>();

SQL:

SELECT
    *
FROM
    Production.Product this_
WHERE
    this_.ProductID = (
        SELECT
            TOP (1)  this_0_.ProductID as y0_
        FROM
            Production.TransactionHistory this_0_
        ORDER BY
            this_0_.Quantity desc
    );

The interesting thing to note about the subquery is the .As<T> method at the end. This allows us to compare the Id property from the outer query with the inner query’s Product.Id property. This method’s sole purpose is to be used inside of a subquery like this so that we can treat the entire QueryOver query as a scalar value for comparison purposes.

.WhereAll and .WhereSome

.WithSubquery.WhereAll and .WithSubquery.WhereSome generate SQL that looks similar to the SQL generated by Where, but it uses the all or some operator to modify the comparison. The subquery should return a resultset of one column. Each row in that result is compared with the scalar value from the outer query.

For example, here’s a query that finds all Products who’s ListPrice is greater than all of the related ProductCostHistory entries’ StandardCost. ProductCostHistory tracks product cost over time:

QueryOver

Product productAlias = null;
var products = session.QueryOver<Product>(() => productAlias)
    .WithSubquery.WhereAll(p => p.ListPrice >
        QueryOver.Of<ProductCostHistory>()
            .Where(ch => ch.Product.Id == productAlias.Id)
            .Select(ch => ch.StandardCost)
            .As<decimal>())
    .List<Product>();

SQL

SELECT
    *
FROM
    Production.Product this_
WHERE
    this_.ListPrice > all (
        SELECT
            this_0_.StandardCost as y0_
        FROM
            Production.ProductCostHistory this_0_
        WHERE
            this_0_.ProductID = this_.ProductID
    )

If you look carefully at the QueryOver, you’ll notice that I assigned an alias to the outer Product. This is so that I could correlate the outer query and the inner query.

Using .WhereSome instead would generate SQL using the some operator. This operator returns true if at least one of the values in the resultset from the subquery returns true.

.WhereExists and .WhereNotExists

These two are straightforward. These will generate the SQL you’d expect with exists or not exists, respectively. If you’re familiar with SQL, you’ve probably used these a fair amount. Use .WhereExists to return rows where there’s at least one result in the subquery, and use .WhereNotExists to return rows where the subquery returns no results.

Here’s an example that returns all products with no transaction history:

QueryOver

Product productAlias = null;

var productsWithNoTransactions = session.QueryOver<Product>(() => productAlias)
    .WithSubquery.WhereNotExists(
        QueryOver.Of<TransactionHistory>()
            .Where(tx => tx.Product.Id == productAlias.Id)
            .Select(tx => tx.Id))
    .List<Product>();

SQL

SELECT
    *
FROM
    Production.Product this_
WHERE
    not exists (
        SELECT
            this_0_.TransactionID as y0_
        FROM
            Production.TransactionHistory this_0_
        WHERE
            this_0_.ProductID = this_.ProductID
    )

Using .WithSubquery is the most straightforward way to work with subqueries in the WHERE clause of a query.

With the Restrictions class

You can use the Restrictions class to generate SQL within a QueryOver query as well. I won’t show everything you can do with the Restrictions class– it should be pretty straightforward after showing a few examples.

Here’s the first example from the previous section:

session.QueryOver<Product>()
    .Where(
        Restrictions.EqProperty(
            Projections.Property<Product>(pr => pr.Id),
            Projections.SubQuery(
                QueryOver.Of<TransactionHistory>()
                    .Select(tx => tx.Product.Id)
                    .OrderBy(tx => tx.Quantity)
                    .Desc
                    .Take(1))))
    .SingleOrDefault<Product>();

I’m not sure about you, but to me this is much harder to read and looks much more complex than the example in the previous section.

Usually this syntax is not necessary, but you may find it useful if you’re building queries dynamically.

One way to build a list of conditions joined by AND operators is to use Restrictions.Conjunction(). You can call .Add on the Conjunction instance you get back, building a conjunction based on various conditions.

Here’s an example:

Conjunction conjunction = Restrictions.Conjunction();

if (onlyBiggestTransaction)
{
    conjunction.Add(
        Restrictions.EqProperty(
            Projections.Property<Product>(pr => pr.Id),
            Projections.SubQuery(
                QueryOver.Of<TransactionHistory>()
                    .Select(tx => tx.Product.Id)
                    .OrderBy(tx => tx.Quantity)
                    .Desc
                    .Take(1))));
}

session.QueryOver<Product>()
    .Where(conjunction)
    .List<Product>();

If you have lots of conditions you’re evaluating to build a WHERE clause, I would consider going this route. It’s easy to read the final query (starting on L16 above), and the various parts of the Conjunction are also nicely separated from the main query.

Subqueries in the SELECT clause

There are two ways to use subqueries in the SELECT clause: SelectSubquery and Projections.Subquery.

SelectSubquery

If you’re using a select list (built using QueryOver<TRoot, TSubType>.SelectList(...)), the easiest way to create a subquery in the SELECT clause is to use .SelectSubquery. This works exactly how you’d expect it to.

This example gets information about a Product and the highest ProductReview.Rating associated with that product:

QueryOver

session.QueryOver<Product>(() => productAlias)
    .SelectList(list => list
        .SelectSubQuery(
            QueryOver.Of<ProductReview>()
                .Where(pr => pr.Product.Id == productAlias.Id)
                .Select(pr => pr.Rating)
                .OrderBy(pr => pr.Rating).Desc
                .Take(1))
        .Select(pr => pr.Id)
        .Select(pr => pr.Name))
    .List<object[]>();

Note that I’m creating a correlated subquery by assigning productAlias in the outer query and then using it in the subquery.

SQL

SELECT
    (SELECT
        TOP (1)  this_0_.Rating as y0_
    FROM
        Production.ProductReview this_0_
    WHERE
        this_0_.ProductID = this_.ProductID
    ORDER BY
        this_0_.Rating desc) as y0_,
    this_.ProductID as y1_,
    this_.Name as y2_
FROM
    Production.Product this_;

Projections.Subquery

Projections.Subquery can come in handy when building the SELECT clause dynamically. This is especially useful when you’re building a SELECT clause using Projections.ProjectionList. Here’s the same query from the SelectSubquery example above:

session.QueryOver<Product>(() => productAlias)
    .Select(Projections.ProjectionList()
        .Add(Projections.SubQuery(
            QueryOver.Of<ProductReview>()
                .Where(pr => pr.Product.Id == productAlias.Id)
                .Select(pr => pr.Rating)
                .OrderBy(pr => pr.Rating)
                .Desc
                .Take(1)))
        .Add(Projections.Property(() => productAlias.Id))
        .Add(Projections.Property(() => productAlias.Name)))
    .List<object[]>();

Refactoring tip

Working with subqueries in QueryOver can quickly become overwhelming, especially if you’re building a complex query.

One great way to easily make things a bit more readable is to simply store subquery definitions in variables. Keeping with the same example:

Product productAlias = null;

// Store the reviews subquery in a variable
QueryOver<ProductReview> reviewSubquery = 
    QueryOver.Of<ProductReview>()
        .Where(pr => pr.Product.Id == productAlias.Id)
        .Select(pr => pr.Rating)
        .OrderBy(pr => pr.Rating)
        .Desc
        .Take(1);

// use that variable in the main query
session.QueryOver<Product>(() => productAlias)
    .Select(Projections.ProjectionList()
        .Add(Projections.SubQuery(reviewSubquery))
        .Add(Projections.Property(() => productAlias.Id))
        .Add(Projections.Property(() => productAlias.Name)))
    .List<object[]>();

That’s an easy improvement to make, especially when you’re dealing with larger queries.

Summary

Working with subqueries can quickly become overwhelming with QueryOver. Hopefully this post will help you write and maintain more advanced QueryOver queries involving subqueries. To summarize:

  • With QueryOver you can use subqueries in the SELECT clause or the WHERE clause. Unfortunately you cannot query from a derived table in the FROM clause
  • In the WHERE clause, you can use .WithSubquery, followed by a .Where variety to control how the results of the subquery are treated
  • You can also use the Restrictions class along with Projections.Subquery
  • In the SELECT clause, you can use .SelectSubQuery with .SelectList.
  • You can also use Projections.Subquery to generate a subquery in the SELECT clause.