Fluent NHibernate and composite ID with single column name

fluent-nhibernatenhibernate

I've crossed posted this in the FNH Google Group but here's the scoop:

I have a table in a legacy database that has a composite ID. Well, the
PK is implied since the table doesn't actually have a PK defined, but
the natural PK is the combination of the WORKORDERID and IMAGEPATH
columns:

CREATE TABLE [WORKORDERIMG](
       [WORKORDERID] [varchar](50) NULL,
       [IMAGEPATH] [varchar](250) NULL,
       [WOTASKID] [numeric](10, 0) NULL,
       [ATTACHEDBY] [nvarchar](100) NULL,
       [COMMENTS] [nvarchar](256) NULL,
       [DATETIMEATTACHED] [datetime] NULL
) ON [PRIMARY]

I have this class map defined:

   /// <summary>
   /// NHibernate mapping for workorder attachments
   /// </summary>
   public class AttachmentMap : ClassMap<Attachment>
   {
       public AttachmentMap()
       {
           SchemaIs(Resources.DatabaseSchema);
           WithTable(ObjectNames.TableWorkorderAttachment);
           UseCompositeId()
               .WithKeyProperty(x => x.ParentId, "WORKORDERID")
               .WithKeyProperty(x => x.FileLocation, "IMAGEPATH");
           Map(x => x.AttachedByUser, "ATTACHEDBY").WithLengthOf(100).Nullable();
           Map(x => x.AttachedOn, "DATETIMEATTACHED").Nullable();
           Map(x => x.Comments).Nullable().WithLengthOf(256);
           References(x => x.ParentWorkorder).FetchType.Join();
       }
   }

For this class:

public class Attachment
{
   public virtual string ParentId { get; set; }
   public virtual string FileLocation { get; set; }
   public virtual Workorder ParentWorkorder { get; set; }
   public virtual string AttachedByUser { get; set; }
   public virtual string Comments { get; set; }
   public virtual DateTime? AttachedOn { get; set; }

   public override bool Equals(object obj)
   {
       return this.IsEqual(obj);
   }

   public override int GetHashCode()
   {
       return HashCodeGenerator.GenerateHashCode(new object[] { FileLocation, ParentId });
   }
}

Now I know that I should be defining the WORKORDERID column in the
classmap for the References line as well. With it excluded I get the
expected error:

NHibernate: INSERT INTO azteca.WorkOrderImg (ATTACHEDBY,
DATETIMEATTACHED, Comments, ParentWorkorder_id, WORKORDERID,
IMAGEPATH) VALUES (@p0, @p1, @p2, @p3, @p4, @p5);@p0 = 'SYSTEM', @p1 = 3/15/2009 12:00:00 AM, @p2 = 'Some comment', @p3 = NULL, @p4 = NULL, @p5 = 'some ile\location'

System.Data.SqlClient.SqlException: Invalid column name 'ParentWorkorder_id'.

However, when I include it I get Fluent NHibernate trying to add the
WORKORDERID column to the query twice:

System.IndexOutOfRangeException: Invalid index 5 for this SqlParameterCollection with Count=5.

I've been trying every combination I can think of to no avail. I'm
testing the mapping with this code:

       [Test]
       public void Can_persist_an_attachment()
       {
           var sf = ObjectFactory.GetNamedInstance<ISessionFactory>   (ObjectMother.Datasource);

           using (ISession session = sf.OpenSession())
           {
               new PersistenceSpecification<Attachment>(session)
                   .CheckProperty(x => x.AttachedByUser, "SYSTEM")
                   .CheckProperty(x => x.AttachedOn, new DateTime(2009, 3, 15))
                   .CheckProperty(x => x.Comments, "Some comment")
                   .CheckProperty(x => x.FileLocation, "some\file\\location")
                   .VerifyTheMappings();
           }
       }

Any help appreciated.

Best Answer

Figured it out. You don't need to add the reference property for the parent. Notice how the many-to-one is missing in the adjusted mapping:

public AttachmentMap()
{
    SchemaIs(Resources.CityworksDatabaseSchema);
    WithTable(ObjectNames.TableWorkorderAttachment);
    UseCompositeId()
        .WithKeyReference(x => x.ParentWorkorder, "WORKORDERID")
        .WithKeyProperty(x => x.FileLocation, "IMAGEPATH");
    Map(x => x.AttachedByUser, "ATTACHEDBY").WithLengthOf(100).Nullable();
    Map(x => x.AttachedOn, "DATETIMEATTACHED").Nullable();
    Map(x => x.Comments).Nullable().WithLengthOf(256);
    //References(x => x.ParentWorkorder, "WORKORDERID").FetchType.Join();
}

This results in the following HBM mapping file. The fix is to have the composite-id have a key-many-to-one line pointing back to the parent class. Now I can persist my Attachment class.

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="">
  <class name="Woolpert.Cityworks.Core.Entities.Azteca.Attachment, Woolpert.Cityworks.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="WorkOrderImg" xmlns="urn:nhibernate-mapping-2.2" schema="azteca">
    <composite-id>
      <key-many-to-one class="Woolpert.Cityworks.Core.Entities.Azteca.Workorder, Woolpert.Cityworks.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" name="ParentWorkorder" column="WORKORDERID" />
      <key-property type="String" name="FileLocation" column="IMAGEPATH" />
    </composite-id>
    <property name="AttachedByUser" type="String">
      <column name="ATTACHEDBY" length="100" not-null="false" />
    </property>
    <property name="AttachedOn" type="System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]">
      <column name="DATETIMEATTACHED" not-null="false" />
    </property>
    <property name="Comments" type="String">
      <column name="Comments" length="256" not-null="false" />
    </property>
  </class>
</hibernate-mapping>

Related Solutions

C# – Mapping tree structure with Fluent NHibernate

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

C# – Fluent NHibernate Join with Constraint

I know this doesn't quite answer what you were asking, but...

If both LastName and FirstName were on the same row of the CustomerName table (but they're not), you could accomplish this with the <join> mapping. Ayende describes this HBM mapping on his blog. There is a fluent-nhibernate example of this here: Entity spanning multiple tables. The method name has since changed from WithTable() to Join(). Here's what it would look like:

public class CustomerMap : ClassMap<Customer>
{
    public CustomerMap()
    {
        Id(x => x.CustomerId);
        Join("CustomerName", m =>
        {
            m.Map(x => x.FirstName);
            m.Map(x => x.LastName);
        });
        // ... other properties ...
    }
}

I'm not sure yet how to address the problem of the two names being on separate rows.

Best Answer

Related Solutions

C# – Mapping tree structure with Fluent NHibernate

C# – Fluent NHibernate Join with Constraint

Related Topic