How do I retrieve a list of base class objects without joins using NHibernate ICriteria?

Question

Let's say I have a base class called Pet and two subclasses Cat and Dog that inherit Pet.

I simply map these to three tables Pet, Cat and Dog, where the Pet table contains the base class properties and the Cat and Dog tables contain a foreign key to the Pet table and any additional properties specific to a cat or dog. A joined subclass strategy.

Now, using NHibernate and ICriteria, how can I get a list of all the different pets as Pet objects, not Cat or Dog objects - just plain Pet objects, without making any joins to the other tables? I am only interested in the information contained in Pet.

Answer 1

I just did something similar and found this blog post by Ayende extremely helpful! I went with a joined-subclass approach and it nicely supports querying the base class.

However, to find "only pets" without joining I think you'd need the table-per-class hierarchy approach. Or, you could use the table-per-subclass and just do left joins on the subclasses and restrict it to rows that had no matches on any of the left joins.

Answer 2

Hmm, selecting 'as pet' would I think bring back all object (not just the pets that are not subclassed). I don't know of a clean way of doing it, but then I'm not an NH expert.

Maybe you could query the discriminator column in the parent table directly? Although perhaps you would have to resport to a custom SQL query rather than HQL or ICriteria.

Answer 3

good enough for many situations: project the properties into the object itself.

USE WITH CARE BECAUSE THE OBJECT IS BROKEN IN THE SENSE THAT IT HAS NOT THE CORRECT TYPE

var propsToSelect = Projections.ProjectionList();
foreach (var prop in sessionfactory.GetClassMetadata(typeof(Pet)).PropertyNames)
{
    propsToSelect.Add(Projections.Property(prop.Name), prop.Name);
}
var results = session.CreateCriteria<Pet>()
    .SetProjection(propsToSelect)
    .SetResultTransformer(Transformers.AliasToBean<Pet>())
    .List<Pet>();

Answer 4

Why not create a new map for "PetsOnly" and and remove your discriminator and other non required fields? I think Kristoffer above aluded to the same thing; should still give you full insert update and delete select support. The pet is only a Pet...

Answer 5

I don't believe you can do this with the Criteria API. However, you can do it with HQL:

var hql = @"from Pet p where p.class = Pet";

Answer 6

This seems to work ok:

I created a view that made selected all columns on the Pet table. I then created a simple DTO class that I mapped to the view. After that, ICriteria can be used.

Not the prettiest or cleanest solution perhaps, but certainly easier to work with than dynamic query strings.