NHibernate - Exclude subclass using Criteria with Joined Subclass

Question

I have a fluent nhibernate configuration in my app. Running latest of both. I am trying to create a criteria statement to pull back a specific class in a joined subclass hierarchy. This is an example of my inheritance structure:

Admin is an Employee is a Person (Admin : Employee, Employee : Person)

What I am trying to get is the Employees but not the Admins, but since admins are employees, they are coming back in the query. I do not have a discriminator column to use. Is there any way to accomplish this using the Criteria API?

Thanks in advance.

Schema as requested (just an example):

Person table: Id, Name

Employee table: PersonId, EmployeeNumber

Admin: PersonId, AdminNumber

NHibernate relates those properly. Everything else works except this specific type of query.

If you're not using a discriminator type column, how is NHibernate supposed to differentiate between the different person types? — csano
Well, it is aware that the different classes belong to different tables, so you would think that the Criteria API would support that kind of exclusion via the SQL query that is generated. — a432511
Thanks for including the schema. Have you looked at the SQL that NHibernate is outputting? I think this will give you a clue as to why you're not getting the output you're expecting. — csano

a432511 a432511 · Accepted Answer · 2011-06-08T19:14:14

It appears that Criteria does not support that functionality. I was able to solve the issue by adding a SQL Restriction to the query to filter out the subclass results.

criteria.Add(
    Expression.SQL("{alias}.MyPrimaryKey NOT IN (SELECT MyPrimaryKey FROM Admin)"));

This essentially excludes and results from the Employee SQL query where that Employee exists in the Admin table, thus returning only Employees that are not Admins.

I originally tried separately querying the Admin table via Criteria to get a list of Ids which I then fed into the Employee query using a NOT IN Criteria statement Restrictions.Not(Restrictions.In()) but SQL Server restricts the number of parameters to 2100 and blows up if that collection of Ids that you are trying to exclude has more than 2100 items.

NHibernate - Exclude subclass using Criteria with Joined Subclass

1 Answers