Mapping tree structure with Fluent NHibernate

Question

I am having trouble to map a tree structure using Fluent NHibernate, without also having to put in what I believe is a hack.

Consider the following classes:

• Node (abstract, has int Id and GroupNode Parent (if null, the node resides in the root node))
• GroupNode (inherits from Node, has IList<GroupNode> Groups and IList<ItemNode> Items)
• ItemNode (inherits from Item)

Ideally, this would have the following database structure:

• GroupNodes (integer Id, nullable integer ParentId)
• ItemNodes (integer Id, nullable integer ParentId)

My mappers looks like this:

public class GroupNodeMap : ClassMap<GroupNode>
{
 public GroupNode()
 {
  Id(x => x.Id);
  References(x => x.Parent);
  HasMany(x => x.Groups).LazyLoad();
  HasMany(x => x.Items).LazyLoad();
 }
}

public class ItemNodeMap : ClassMap<ItemNode>
{
 public ItemNodeMap()
 {
  Id(x => x.Id);
  References(x => x.Parent);
 }
}

Unfortunately, this is creating a duplicate set of references (each table gets both a ParentId and a GroupNodeId. I can tweak this behaviour by adding .KeyColumn("ParentId") after .LazyLoad(), but this feels like a hack, and I would like it to be expressed using either conventions or lambdas instead of a magic string.

Could anyone point me in the right direction?

Answer 1

Fluent NHibernate has naming conventions. If you don't like to use database column names (it's not a hack) you should rename database columns. Check http://wiki.fluentnhibernate.org/Available%5Fconventions

Answer 2

I have exactly the same type of structure in my database and I also used the KeyColumn method. It's not really a hack it's just explaining fluentnhibernate what is the name of the other side of the collection.

This is a bit of a special case since usually if you set "HasMany(x => x.Groups)", he is going to search for "GroupId" in the table Group. But in your case GroupId is already the key of that column and it's not the column you want to use for this. That's why you have to set the "KeyColumn" to tell fnh which column he should use for the relation.

The KeyColumn method is used often, especially when working on database which have not been built for NHibernate and columns have weird names.

Answer 3

Here's a sample you may try using AutoMap conventions and SQLite:

namespace Entities
{
    public abstract class Node
    {
        public virtual int Id { get; set; }
        public virtual GroupNode Parent { get; set; }
    }

    public class ItemNode : Node
    {
    }

    public class GroupNode : Node
    {
        public virtual IList<GroupNode> Groups { get; set; }
        public virtual IList<ItemNode> Items { get; set; }
    }
}

class Program
{
    static void Main()
    {
        if (File.Exists("data.db3"))
        {
            File.Delete("data.db3");
        }

        using (var factory = CreateSessionFactory())
        {
            using (var connection = factory.OpenSession().Connection)
            {
                ExecuteQuery("create table GroupNode(Id integer primary key, Parent_Id integer)", connection);
                ExecuteQuery("create table ItemNode(Id integer primary key, Parent_Id integer)", connection);

                ExecuteQuery("insert into GroupNode(Id, Parent_Id) values (1, null)", connection);
                ExecuteQuery("insert into GroupNode(Id, Parent_Id) values (2, 1)", connection);
                ExecuteQuery("insert into GroupNode(Id, Parent_Id) values (3, 1)", connection);

                ExecuteQuery("insert into ItemNode(Id, Parent_Id) values (1, 1)", connection);
                ExecuteQuery("insert into ItemNode(Id, Parent_Id) values (2, 1)", connection);
            }

            using (var session = factory.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var node = session.Get<GroupNode>(1);
                tx.Commit();
            }
        }
    }

    private static ISessionFactory CreateSessionFactory()
    {
        return Fluently.Configure()
            .Database(
                SQLiteConfiguration.Standard.UsingFile("data.db3").ShowSql()
            )
            .Mappings(
                m => m.AutoMappings.Add(
                    AutoMap
                        .AssemblyOf<Program>()
                        .Where(t => t.Namespace == "Entities")
                )
            ).BuildSessionFactory();
    }

    static void ExecuteQuery(string sql, IDbConnection connection)
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = sql;
            command.ExecuteNonQuery();
        }
    }
}