NHibernate Entity with Composite-Id Saving Issue

Question

In order to make myself clear, I have created a most basic case to describe my problem. Let's say I have 3 tables:

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [ProductID] ASC )
) ON [PRIMARY]

CREATE TABLE [dbo].[OrderHeader](
    [HeaderID] [int] IDENTITY(1,1) NOT NULL,
    [Comment] [varchar](100) NULL,
 CONSTRAINT [PK_OrderHeader] PRIMARY KEY CLUSTERED ( [HeaderID] ASC )
) ON [PRIMARY]

CREATE TABLE [dbo].[OrderDetail](
    [HeaderID] [int] NOT NULL, /* FK to OrderHeader table */
    [ProductID] [int] NOT NULL, /* FK to Product table */
    [CreatedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
(
    [HeaderID] ASC, 
    [ProductID] ASC 
)
) ON [PRIMARY]

And I have created correponding entity classes and mapping classes.

public class Product {
    public virtual int? Id { get; set; }

    public virtual string Name { get; set; }
}

public class ProductMap : ClassMap<Product> {
    public ProductMap() {
        Table("Product");

        Id(x => x.Id, "ProductID").GeneratedBy.Identity();
        Map(x => x.Name, "ProductName");
    }
}

public class OrderHeader {
    public virtual int? Id { get; set; }

    public virtual string Comment { get; set; }

    public virtual IList<OrderDetail> Details { get; set; }
}

public class OrderHeaderMap : ClassMap<OrderHeader> {
    public OrderHeaderMap() {
        Table("OrderHeader");

        Id(x => x.Id, "HeaderID").GeneratedBy.Identity();
        Map(x => x.Comment, "Comment");

        HasMany<OrderDetail>(x => x.Details)
            .KeyColumn("HeaderID")
            .Inverse()
            .Cascade
            .All();
    }
}

public class OrderDetail {
    public virtual OrderHeader OrderHeader { get; set; }

    public virtual Product Product { get; set; }

    public virtual DateTime? CreatedOn { get; set; }

    public override bool Equals(object obj) {
        OrderDetail other = obj as OrderDetail;
        if (other == null) {
            return false;
        } else {
            return this.Product.Id == other.Product.Id && this.OrderHeader.Id == other.OrderHeader.Id;
        }
    }

    public override int GetHashCode() {
        return (OrderHeader.Id.ToString() + "|" + Product.Id.ToString()).GetHashCode();
    }
}

public class OrderDetailMap : ClassMap<OrderDetail> {
    public OrderDetailMap() {
        Table("OrderDetail");

        CompositeId()
            .KeyReference(x => x.Product, "ProductID")
            .KeyReference(x => x.OrderHeader, "HeaderID");

        References<OrderHeader>(x => x.OrderHeader, "HeaderID").ForeignKey().Not.Nullable().Fetch.Join();
        References<Product>(x => x.Product, "ProductID").ForeignKey().Not.Nullable();

        Version(x => x.CreatedOn).Column("CreatedOn").Generated.Always();
    }
}

I have also created NH Session Provider

public class NHibernateSessionProvider {
    private static ISessionFactory sessionFactory;

    public static ISessionFactory SessionFactory {
        get {
            if (sessionFactory == null) {
                sessionFactory = createSessionFactory();
            }
            return sessionFactory;
        }
    }

    private static ISessionFactory createSessionFactory() {
        return Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ShowSql()
            .ConnectionString(c => c.FromConnectionStringWithKey("TestDB")))
            .Mappings(m => m.FluentMappings.AddFromAssemblyOf<OrderHeaderMap>())
            .BuildSessionFactory();
    }
}

And a NH repository class is also created

public class NHibernateRepository<T, TId> {
        protected ISession session = null;
        protected ITransaction transaction = null;

        public NHibernateRepository() {
            this.session = NHibernateSessionProvider.SessionFactory.OpenSession();
        }

        public void Save(T entity) {
            session.SaveOrUpdate(entity);
        }

        public void AddNew(T entity) {
            session.Save(entity);
        }

        public void BeginTransaction() {
            transaction = session.BeginTransaction();
        }

        public void CommitTransaction() {
            transaction.Commit();
            closeTransaction();
        }

        public void RollbackTransaction() {
            transaction.Rollback();
            closeTransaction();
            closeSession();
        }

        private void closeTransaction() {
            transaction.Dispose();
            transaction = null;
        }

        private void closeSession() {
            session.Close();
            session.Dispose();
            session = null;
        }

        public void Dispose() {
            if (transaction != null) {
                CommitTransaction();
            }

            if (session != null) {
                session.Flush();
                closeSession();
            }
        }
    }

In my code, I have created 2 different ways to save this master/detail structure with composite-id.

    private static void method1() {
        NHibernateRepository<Product, int?> repoProduct = new NHibernateRepository<Product, int?>();
        NHibernateRepository<OrderHeader, int?> repo = new NHibernateRepository<OrderHeader, int?>();
        OrderHeader oh = new OrderHeader();
        oh.Comment = "Test Comment " + DateTime.Now.ToString();
        oh.Details = new List<OrderDetail>();
        for (int i = 0; i < 2; i++) {
            oh.Details.Add(new OrderDetail
            {
                OrderHeader = oh,
                Product = repoProduct.GetById(i + 3)
            });
        }

        repo.AddNew(oh);
    }

    private static void method2() {
        NHibernateRepository<OrderHeader, int?> repoHeader = new NHibernateRepository<OrderHeader, int?>();
        OrderHeader oh = new OrderHeader();
        oh.Comment = "Test Comment " + DateTime.Now.ToString();
        repoHeader.Save(oh);

        NHibernateRepository<OrderDetail, int?> repoDetail = new NHibernateRepository<OrderDetail, int?>();
        for (int i = 0; i < 2; i++) {
            OrderDetail od = new OrderDetail
            {
                OrderHeaderId = oh.Id,
                OrderHeader = oh,
                ProductId = i + 3,
                Product = new Product
                {
                    Id = i + 3
                },
            };

            repoDetail.AddNew(od);
        }
    }

But for both methods, the OrderDetail table is never saved. I have turned on ShowSql() to see SQL statement executed on console, no SQL generated to save OrderDetail table at all.

I did quite a lot of search everywhere and could not have a clear conclusion what is wrong.

Anybody has some clue, what exactly do I need to do to save an entity with composite-id?

Thanks

Hardy

Answer 1

I don't think that the composite-id is what is causing you issues. I think it's the way you have your OrderDetails mapped in your OrderHeader map.

I think it should be something like this instead:

HasMany<OrderDetail>(x => x.Details).KeyColumn("HeaderID").Inverse().Cascade.AllDeleteOrphan();

Edit:

You should listen to Diego below and change your mapping to:

public class OrderDetailMap : ClassMap<OrderDetail> {
    public OrderDetailMap() {
        Table("OrderDetail");

        CompositeId()
            .KeyReference(x => x.Product, "ProductID")
            .KeyReference(x => x.OrderHeader, "HeaderID");

        Version(x => x.CreatedOn).Column("CreatedOn").Generated.Always();
    }
}

The code you have in your above mapping of OrderDetails is what is causing you the error "Invalid index 2 for this SqlParameterCollection with Count=2."

References<OrderHeader>(x => x.OrderHeader, "HeaderID").ForeignKey().Not.Nullable().Fetch.Join();
References<Product>(x => x.Product, "ProductID").ForeignKey().Not.Nullable();

Answer 2

well firstly, your OrderDetail is mapped wrongly: You may not map one column multiple times. Here you both assign it for composite-id as well as have a many-to-one. Your composite-id can (and should) have 2 many-to-one properties and not just value properties.

This is evident in your last comment on Diego's answer, see also IndexOutOfRangeException Deep in the bowels of NHibernate

Secondly you are setting an inverse on the OrderHeader.Details collection which if i remember correctly means method1 would not cause an insert on the OrderDetail

Answer 3

Both the model and the mapping are incorrect.

Remove OrderHeaderId and ProductId from OrderDetail.

Then, the Composite id should include OrderHeader and Product as references (I think with Fluent it's KeyReference instead of KeyProperty; in XML it's key-many-to-one instead of key-property)

Then, add a proper Cascade setting, as Cole suggested.

Sample usage:

using (var session = GetSessionFromSomewhere())
using (var tx = session.BeginTransaction())
{
    var orderHeader = new OrderHeader();
    ...
    orderHeader.Details.Add(new OrderDetail
                            {
                                OrderHeader = orderHeader;
                                Product = session.Load<Product>(someProductId);
                            });
    session.Save(orderHeader);
    tx.Commit();
}

Everything in that block is required.