Adding criteria to filter by a self referencing parent?

Question

Stumped on this one. I have a many to many self referencing relationship(FluentNH) mapped for a given class:

public class Folder
{
    public IList<Folder> ParentFolders{get;set;}
}

I'm looking to create a query that pulls root folders(ie folders that have ParentFolders.Count() == 0). The self reference is done via lookup table, so ParentFolderID is not actually accessible from the class(only shows up in the mapping).

public IList<Folder> GetRootFolders()
{
    return session.CreateCriteria<Folder>()
     .outofideas()
     .List<Folder>();
}

Any assistance or thoughts on a self referencing hierarchy via lookup table are greatly appreciated. The tables themselves are legacy and as such, unable to be modified.

Answer 1

I've always found NHibernate's Criteria difficult to work with. (That is to say - even more difficult than the rest of NHibernate.) Try creating an HQL query instead. Without seeing the mapping file I can't write the query for you, but it would be something like:

session.CreateQuery("FROM Folder f WHERE COUNT(f.ParentFolders) = 0")

You'd have to play around with the syntax a bit, I'm writing this from memory. Note that NHibernate is smart enough to do a sub-query for the COUNT - it won't actually load all your parent folders and then count them.

Answer 2

If your mapping has something like this:

<bag name="ParentFolders" table="FolderFolder" lazy="true">
    <key column="FolderID"/>
    <many-to-many class="Folder" column="ParentFolderID"/>
</bag>

then if you write:

var folders = Session.CreateCriteria<Folder>("folder")
    .Add(Restrictions.IsEmpty("ParentFolders")).List<Folder>();

you will get this SQL:

SELECT *
FROM Folder folder 
WHERE not exists (select 1 from FolderFolder where folder.FolderID=FolderID)

that I believe does the trick.