Querying a self referencing join with NHibernate Linq
- by Ben
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,
Ben
