Fluent NHibernate Many to Many with extra column does not insert

Question

I'm struggling with fluent nhibernate from Fluent Nhibernate Many-to-Many mapping with extra column

I've copied the mappings and written the smallest program I can... but it wont save... Would anybody be able to provide some insight ???

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<Inventory> Inventory { get; set; }

    public Product()
    {
        Inventory = new List<Inventory>();
    }
}

public class Warehouse
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<Inventory> Inventory { get; set; }

    public Warehouse()
    {
        Inventory = new List<Inventory>();
    }
}

public class Inventory
{
    public Product Product { get; set; }
    public Warehouse Warehouse { get; set; }
    public bool StockInHand { get; set; }


    // override object.Equals
    public override bool Equals(object obj)
    {
        if (obj == null || GetType() != obj.GetType())
        {
            return false;
        }
        var i = obj as Inventory;

        return ((i.Product.Id == this.Product.Id) 
             && (i.Warehouse.Id == this.Warehouse.Id));
    }

    // override object.GetHashCode
    public override int GetHashCode()
    {
        return 9999;
    }
}
public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        Not.LazyLoad();
        Table("Product");
        Id(x => x.Id).GeneratedBy.Assigned();
        Map(x => x.Name);
        HasMany(x => x.Inventory).AsBag()
         .Cascade.All()
         //.Inverse()
         .Table("Inventory");
    }
}
public class WarehouseMap : ClassMap<Warehouse>
{
    public WarehouseMap()
    {
        Not.LazyLoad();
        Table("Warehouse");
        Id(x => x.Id).GeneratedBy.Assigned();
        Map(x => x.Name);
        HasMany(x => x.Inventory).AsBag()
         .Cascade.All()
         .Inverse()
         .Table("Inventory");
    }
}
public class InventoryMap : ClassMap<Inventory>
{
    public InventoryMap()
    {
        Not.LazyLoad();
        Table("Inventory");
        CompositeId()
          .KeyReference(x => x.Product, "Product_id")
          .KeyReference(x => x.Warehouse, "Warehouse_id");

        Map(x => x.StockInHand);
    }
}

And the program...

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        Product p = new Product() { Id = 1, Name="product" };
        Inventory i = new Inventory() { StockInHand = true };
        i.Product = p;
        p.Inventory.Add(i);
        Warehouse w = new Warehouse() { Id = 1, Name = "warehouse" };
        i.Warehouse = w;
        w.Inventory.Add(i);

        session.SaveOrUpdate(p);

        session.Flush();

        transaction.Commit();
    }
}

The exception I get is

constraint failed\r\nforeign key constraint failed

I've also output the create statements, which look correct to me...

create table Inventory (
    Product_id INT not null,
   Warehouse_id INT not null,
   StockInHand BOOL,
   primary key (Product_id, Warehouse_id),
   constraint FK2B4C61665C5B845 foreign key (Product_id) references Product,
   constraint FK2B4C616A6DE7382 foreign key (Warehouse_id) references Warehouse)

create table Product (
    Id INT not null,
   Name TEXT,
   primary key (Id)
)

create table Warehouse (
    Id INT not null,
   Name TEXT,
   primary key (Id)
)

And the SQL that is run prior to the exception....

NHibernate:
INSERT
INTO
    Warehouse
    (Name, Id)
VALUES
    (@p0, @p1);
@p0 = 'warehouse' [Type: String (0)], @p1 = 1 [Type: Int32 (0)]
NHibernate:
INSERT
INTO
    Inventory
    (StockInHand, Product_id, Warehouse_id)
VALUES
    (@p0, @p1, @p2);
@p0 = True [Type: Boolean (0)], @p1 = 1 [Type: Int32 (0)], @p2 = 1 [Type: Int32 (0)]

So how is this supposed to work correctly?!?

Answer 1

The cause of your issue is that NHibernate is trying to insert the Inventory record before the Warehouse record. This is because the order of insertions is governed by the order in which session.Save is called. Based on this information I tried a number of code variations that will prevent the Foreign Key Constraint error. I have posted my nicest solution below.

using (var session = sessionFactory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    var warehouse = new Warehouse() { Id = 1, Name = "warehouse" };
    session.Save(warehouse);

    var product = new Product() {Id = 1, Name = "product"};
    var inventory = new Inventory 
                     { StockInHand = true, Product = product, Warehouse = warehouse};

    product.Inventory.Add(inventory);
    warehouse.Inventory.Add(inventory);

    session.Save(product);

    transaction.Commit();
}

One thing I discovered, which surprised me quite a bit, is that if you put the session.Save(warehouse) after warehouse.Inventory.Add(inventory) then NHibernate doesn't insert the Warehouse record first and the Foreign Key error is thrown.

As a final note, to obtain the three insert statements as listed below the Inverse() has to be re-instated in the ProductMap mapping class. Otherwise an additional update statement will be emitted by NHibernate.

INSERT INTO Warehouse (Name, Id) VALUES (@p0, @p1);@p0 = 'warehouse' 
[Type: String (4000)], @p1 = 1 [Type: Int32 (0)]

INSERT INTO Product (Name, Id) VALUES (@p0, @p1);
@p0 = 'product' [Type: String (4000)], @p1 = 1 [Type: Int32 (0)]

INSERT INTO Inventory (StockInHand, Product_id, Warehouse_id) VALUES (@p0, @p1, @p2);
@p0 = True [Type: Boolean (0)], @p1 = 1 [Type: Int32 (0)], @p2 = 1 [Type: Int32 (0)]

Answer 2

For anyone that might follow in my footsteps...

My problem is that I want to store some information about a relationship between entities in a link table. Classically a many to many relationship in database terms has a link table between domain objects.

With the structure outlined in the question a warehouse and a product need to be inserted before the inventory item can be inserted. An Inventory item must be inserted last so that both foreign key constraints are in place before the save occurs.

Insert into Product

Insert into Warehouse

Insert into Inventory (Note this happens **after** the primary keys are 
inserted in Warehouse and Product!)

However I work my mappings Fluent NHibernate generates the following...

Insert into Product

Insert into Inventory (Foreign Key constraint violated, no Warehouse)

.. which has to be incorrect because there is no primary key on warehouse! I understand the problem, but not the solution... I've managed to produce two solutions as below, but I consider both to be suboptimal.

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        Not.LazyLoad();
        Table("Product");
        Id(x => x.Id, "Product_id").GeneratedBy.Assigned();
        Map(x => x.Name).Column("Name").Length(10);
        HasMany(x => x.Inventory)
            .Cascade.Delete()
            .KeyColumn("Product_id");
    }
}
public class WarehouseMap : ClassMap<Warehouse>
{
    public WarehouseMap()
    {
        Not.LazyLoad();
        Table("Warehouse");
        Id(x => x.Id, "Warehouse_id").GeneratedBy.Assigned();
        Map(x => x.Name).Column("Name").Length(10);
        HasMany(x => x.Inventory)
            .Cascade.All()
            .KeyColumn("Warehouse_id");
    }
}
public class InventoryMap : ClassMap<Inventory>
{
    public InventoryMap()
    {
        Not.LazyLoad();
        Table("Inventory");
        CompositeId()
          .KeyReference(x => x.Product, "Product_id")
          .KeyReference(x => x.Warehouse, "Warehouse_id");
        Map(x => x.StockInHand);
    }
}

I can perform a save and have half of the code work as I expect, but it presupposes ordinality. Ie, I must have some knowledge of the order in which these objects need saving. I haven't tested what happens with the delete, but this does mean I have to follow a correct order for saving these objects. /* works */ session.Save(product); session.Save(warehouse); // will also save Inventory

/* would fail */
session.Save(warehouse);
session.Save(product);

Alternatively (and I like this even less) I can tell Nhibernate that I want to be responsible for everything...

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        Not.LazyLoad();
        Table("Product");
        Id(x => x.Id, "Product_id").GeneratedBy.Assigned();
        Map(x => x.Name).Column("Name").Length(10);
        HasMany(x => x.Inventory).Inverse();
    }
}
public class WarehouseMap : ClassMap<Warehouse>
{
    public WarehouseMap()
    {
        Not.LazyLoad();
        Table("Warehouse");
        Id(x => x.Id, "Warehouse_id").GeneratedBy.Assigned();
        Map(x => x.Name).Column("Name").Length(10);
        HasMany(x => x.Inventory).Inverse();
    }
}
public class InventoryMap : ClassMap<Inventory>
{
    public InventoryMap()
    {
        Not.LazyLoad();
        Table("Inventory");
        CompositeId()
          .KeyReference(x => x.Product, "Product_id")
          .KeyReference(x => x.Warehouse, "Warehouse_id");
        Map(x => x.StockInHand);
    }
}

Now I get the following

/* works */
session.save(Product);
session.Save(Warehouse);
session.Save(Inventory);

/* works */
session.Save(Warehouse);
session.Save(Product);
session.Save(Inventory);

/* fails */
session.Save(Inventory);
session.Save(Warehouse);
session.Save(Product);

Can anyone improve on this and give me the mappings I really want, such that I can save a warehouse or a product and Fluent NHibernate that will get the ordering correct!?? eg.

session.Save(warehouse); // or session.Save(product);

so that this results in

Insert into Warehouse... 
Insert into Product...
Insert into Inventory... // where NHibernate determines this goes last so that primary keys are in place on both previous tables!