Typically with NHibernate, I’m always querying over one table and eager fetching it’s child collections. I’m also only performing WHERE conditions on that initial table I am querying over.

The other day though, I needed to solve a problem where I was adding WHERE conditions to the child collections. Now in SQL, this is quite easy but in NHibernate, it took me a while to figure out how to do this efficiently.

Example in SQL:

SELECT  *
FROM    Customer c
        JOIN CustomerAddresses ca ON c.CustomerId = ca.CustomerId
        JOIN CustomerPhoneNumbers cpn ON c.CustomerId = cpn.CustomerId
WHERE   c.Status = 'Active'
        AND ca.City = 'Dallas'
        AND cpn.AreaCode = '972'

So let’s say that I have a Customer entity with a collection of CustomerAddress entities and a collection of CustomerPhoneNumber entities which might look something like this:

public class Customer
{
    public virtual int CustomerId { get; set; }

    public virtual IList Addresses { get; set; }

    public virtual IList PhoneNumbers { get; set; }
}

So my first thought is to use the .Any() method to access the child collections and put my where conditions into. Doing so I came up with the following code and although it works, the SQL it creates is inefficient by creating EXISTS clauses in the WHERE clause… very messy.

var customers = session.Query<Customer>()
    .Where( c => c.Status == "Active"
                    && c.Addresses.Any( a => a.City == "Dallas" )
                    && c.PhoneNumbers.Any( pn => pn.AreaCode == "972" ) )
    .ToList();

And here’s an example of the SQL it produces:

SELECT

    customer0_.CustomerId as Customer1_135_0_,
    customer0_.Status as Customer1_135_1_
FROM
    Customer customer0_
WHERE
    customer0_.Status='Active' /* @p0 */
    AND (
        EXISTS (
            SELECT
                customeraddr1_.CustomerAddressId
            FROM
                CustomerAddresses customeraddr1_
            WHERE
                customer0_.CustomerId=customeraddr1_.CustomerId
                AND customeraddr1_.City = 'Dallas' /* @p1 */
        )
    AND (
        EXISTS (
            SELECT
                customerphon2_.CustomerPhoneNumberId
            FROM
                CustomerPhoneNumbers customerphon2_
            WHERE
                customer0_.CustomerId=customerphon2_.CustomerId
                AND customerphon2_.AreaCode = '972' /* @p2 */;
        )
    );

But a much more efficient way would be to simply JOIN on the tables that represent the child collection properties instead of using EXISTS. To do so, I used the LINQ query syntax which allowed me to easily add multiple FROM clauses which NHibernate will interpret as INNER JOINs:

var customers =
    (
        from c in session.Query<Customer>()
        from a in c.Addresses
        from pn in c.PhoneNumbers
        where c.Status == "Active"
                && a.City == "Dallas"
                && pn.AreaCode == "972"
        select c )
        .ToList();

And here’s an example of the SQL it produces which looks remarkably like the SQL we hand coded in the beginning:

SELECT
    customer0_.CustomerId as Customer1_135_0_,
    customer0_.Status as Customer1_135_1_
FROM
    Customer customer0_
INNER JOIN
    CustomerAddresses customeraddr1_
        ON customer0_.CustomerId=customeraddr1_.CustomerId
INNER JOIN
    CustomerPhoneNumbers customerphon2_
        ON customer0_.CustomerId=customerphon2_.CustomerId
WHERE
    customer0_.Status='Active' /* @p0 */
    AND customeraddr1_.City = 'Dallas' /* @p1 */
    AND customerphon2_.AreaCode = '972' /* @p2 */;