C# – Mapping tree structure with Fluent NHibernate

cfluent-nhibernatenhibernatenhibernate-mapping

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?

Best Answer

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();
        }
    }
}
Related Topic