How do I use Fluent NHibernate to map a self-referencing folder hierarchy?

Question

I have a folder hierarchy represented by the following class:

public class Folder
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Folder ParentFolder { get; set; }
    public virtual ICollection<Folder> SubFolders { get; set; }
}

In other words, each Folder can belong to a ParentFolder, as well as have SubFolders under it. I'm using Fluent NHibernate's Automapper and automatically generating the database schema using SchemaExport. I get the following table when I try to save some test folders:

Id | Name                        | ParentFolder_id | Folder_id
----------------------------------------------------------------
1  | Root Folder (has children)  | NULL            | NULL
2  | Root Folder (no children)   | NULL            | NULL
3  | Sub Folder                  | 1               | NULL
4  | Sub Sub Folder              | 2               | NULL

So far so good, the ParentFolder_id column is being set correctly, although I don't know why it created another Folder_id column. Now, when I try to run the following code:

using (var session = SessionFactory.OpenSession())
{
    // I'm using NHibernate 3
    var rootFolder = session.Query<Folder>()
                            .Where(x => x.Name.StartsWith("root").First();

    Console.WriteLine(rootFolder.SubFolders.Count());
}

The count returned is 0, and the following SQL is executed:

SELECT count(Id) FROM [Folder] WHERE Folder_id = 1

This SQL statement is wrong. It should be doing:

SELECT count(Id) FROM [Folder] WHERE ParentFolder_id = 1

Can anyone tell me why Fluent NHibernate is creating the extra Folder_id column and querying on it, and how I can fix it so that it properly queries the ParentFolder_id column instead? I've tried the following override with no luck:

public class FolderOverride : IAutoMappingOverride<Folder>
{
    public void Override(AutoMapping<Folder> mapping)
    {
        mapping.HasMany(x => x.SubFolders).Inverse(); // I thought inverse might fix it, but no dice
        mapping.References(x => x.ParentFolder);
    }
}

Answer 1

I think you will have to specify the columns on your mapping overrides.

mapping.HasMany(x => x.SubFolders).KeyColumn("ParentFolder_id");