Filtering entities by subtype with NHibernate.Linq

Question

Given this inheritance mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="User" table="[User]" abstract="true">
        <joined-subclass name="SubUser1" table="SubUser1">
            <key column="UserId" />
            ...
        </joined-subclass>
        <joined-subclass name="SubUser2" table="SubUser2">
            <key column="UserId" />
            ...
        </joined-subclass>
        <joined-subclass name="SubUser3" table="SubUser3">
            <key column="UserId" />
            ...
        </joined-subclass>
    </class>
</hibernate-mapping>

how do I query for all instances of SubUser2 and SubUser3? I realize I can do this:

session.Linq<User>().OfType<SubUser2>()

but that only allows me to filter by a single type. I tried this:

session.Linq<User>().Where(user => user is SubUser2)

but that resulted in this error:

could not resolve property:  of: User

Any ideas on how to express a query against multiple sub-types?

Answer 1

I'm not familiar with NHibernate, but here is a trick I use for Linq. Create an extension method class with no namespace.

example:

using System;
using System.Linq;
using Linq;

public static class LinqExtensions
{
    public static IQueryable<User> FilterBySubUsers(this IQueryable<User> query,
        params Type[] subusers)
    {
        return (from u in query
                where subusers.Contains(u.GetType())
                    select u).AsQueryable();
    }
}

This is a separate class with no namespace, which makes it easier to use with Queryable objects (you don't have to remember to include any namespace. I've had troubles with this working in the Project's namespace.

Since I don't have any objects of Usuer, Subuser2, Subuser3, etc... I just created a LinqToSql mapping layer with three empty classes with these names.

Then, in my main project file, I added this code:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Linq
{
    class Program
    {
        static void Main(string[] args)
        {
            List<User> users = new List<User> {
                new User(), new Subuser2(), new Subuser2(),
                new Subuser3(), new Subuser3(), new User() };

            var filteredUsers = users.AsQueryable().FilterBySubUsers(
                typeof(Subuser2), typeof(Subuser3));

            var a = filteredUsers;
        }
    }
}

The reason I have var a = filteredUsers is so I could debug at that line and make sure there were only 4 users in the Queryable object.

I hope that helps a little and applies to NHibernate.

Answer 2

What about something like this:

// Get them in 2 queries
var sub2 = session.Linq<SubUser2>().Select(x => x).ToList();
var sub3 = session.Linq<SubUser3>().Select(x => x).ToList();

// Join together in memory
var sub2And3 = sub2.OfType<User>().Union( sub3.OfType<User>() );

Answer 3

As far as I can tell, this is not possible. Perhaps it will be in the future. I know they accept patches :-)

I only have about 500 records and 4 types, and am usually querying on 2 or 3 types at the same time. For now, I am applying the filter on each type in the database, then concatenating the result sets and doing ordering and paging in-memory.

It is not the most efficient solution, but works just fine. I chose not to add a superfluous discriminator column to the User table solely to enable this query.