Querying a self referencing join with NHibernate Linq

Question

In my application I have a Category domain object. Category has a property Parent (of type category).

So in my NHibernate mapping I have:

    <many-to-one name="Parent" column="ParentID"/>

Before I switched to NHibernate I had the ParentId property on my domain model (mapped to the corresponding database column).

This made it easy to query for say all top level categories (ParentID = 0):

where(c => c.ParentId == 0)

However, I have since removed the ParentId property from my domain model (because of NHibernate) so I now have to do the same query (using NHibernate.Linq) like so:

        public IList<Category> GetCategories(int parentId) {
        if (parentId == 0)
            return _catalogRepository.Categories.Where(x => x.Parent == null).ToList();
        else
            return _catalogRepository.Categories.Where(x => x.Parent.Id == parentId).ToList();
    }

The real impact that I can see, is the SQL generated. Instead of a simple 'select x,y,z from categories where parentid = 0' NHibernate generates a left outer join:

SELECT this_.CategoryId    as CategoryId4_1_,
   this_.ParentID      as ParentID4_1_,
   this_.Name          as Name4_1_,
   this_.Slug          as Slug4_1_,
   parent1_.CategoryId as CategoryId4_0_,
   parent1_.ParentID   as ParentID4_0_,
   parent1_.Name       as Name4_0_,
   parent1_.Slug       as Slug4_0_

FROM Categories this_ left outer join Categories parent1_ on this_.ParentID = parent1_.CategoryId WHERE this_.ParentID is null

Which doesn't seems much less efficient that what I had before.

Is there a better way of querying these self referencing joins as it's very tempting to drop the ParentID back onto my domain model for this reason.

Thanks

bernhardrusch bernhardrusch · Accepted Answer · 2010-06-10T08:29:58

My first reaction would've been: yes - this is the way it is. Without doing anything NHibernate always tries to load the whole element - and this means that it loads the parent element too. Is this really a performance problem or is it just an aesthetical problem ? And I don't think that including the parent id would help you - because it still would load the parent item with it.

But if you would really like to optimize this read the following article http://www.javalobby.org/java/forums/t20533.html. It is about Hibernate, but it gives you some ideas about how to handle this and a (possible) solution for your problem.

Querying a self referencing join with NHibernate Linq

1 Answers