Many-To-Many with Extra Columns Nhibernate

Fluent Nhibernate Many-to-Many mapping with extra column

Your relationship is not a many-to-many as far as NHibernate is concerned. A true many-to-many has no additional columns, such as StockInHand in your example.

You have to map this as two one-to-many relationships, and map Inventory as an entity.

Something like (i've skipped the other properties):

public class Product
{
  public List<Inventory> Inventory { get; set; }
}

public class Warehouse
{
  public List<Inventory> Inventory { get; set; }
}

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

public ProductMap() {
    Id(x => x.Id);
    Map(x => x.Name);
    HasMany(x => x.Inventory)
     .Cascade.All()
     .Inverse()
     .Table("Inventory");
}

public WarehouseMap()
{
    Id(x => x.Id);
    Map(x => x.Name);      
    HasMany(x => x.Inventory)
     .Cascade.All()
     .Inverse()
     .Table("Inventory");
}

public InventoryMap()
{
    CompositeId()
      .KeyReference(x => x.Product, "Product_id")
      .KeyReference(x => x.Warehouse, "Warehouse_id")

    Map(x => x.StockInHand);
}

many-to-many with extra columns nhibernate

The many-to-many, without the explicit mapping of the pairing table as an entity - is in NHibernate of course suported. So, in case, that the Date column is autogenerated, or nullable (does not have to be inserted by app/NHiberante), we can do it like here: 6.8. Bidirectional Associations

<class name="User">
    <id name="Id" column="Uid"/>
    ...
    <bag name="Groups" table="UGlink" lazy="true">
        <key column="Uid"/>
        <many-to-many class="Group" column="Gid"/>
    </bag>
</class>

<class name="Group">
    <id name="id" column="Gid"/>
    ...

    <!-- inverse end -->
    <bag name="Users" table="UGlink" inverse="true" lazy="true">
        <key column="Gid"/>
        <many-to-many class="User" column="Uid"/>
    </bag>
</class>

So, what we have is a mapping, in which NHiberante does care about the pairing table, and we can do:

thisUser.Groups

But if I could suggest, do not go with many-to-many. The many-to-one with pairing object is (I'd say) better solution, because it will support searching Users by Groups and vice versa.

See Chapter 24. Best Practices, cite:

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

Here is some more detailed explanation how to do it without many-to-many: Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?

Add custom columns on many to many relationship on NHibernate

The answer is:

NHibernate native many-to-many mapping does not support any additional setting on the pairing table

But, it could be replaced with a pairing object being first level citizen

public class MovieActor 
{
    public virtual Movie Movie { get; set; }
    public virtual Actor Actor { get; set; }
    ... // more properties here
    public virtual int Rating { get; set; }
}

public class Actor
{
    public virtual IList<MovieActor> Movies { get; set; }
}

public class Movie 
{
    public virtual IList<MovieActor> Actors { get; set; }    
}

That would be standard HasMany and References mapping. And the queriyng later will be more easier

Also check these:

• Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?
• nhibernate many to many with multiple table
• many-to-many with extra columns nhibernate

Fluent NHibernate Many to Many with extra column does not insert

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)]

Fluent NHibernate: mapping complex many-to-many (with additional columns) and setting fetch

I don't understand why it doesn't work the way you do it, but I can tell you how I would map it:

<class name="Parent">

    <id .../>

    <list name="Children" table="ParentChildRelationship">
        <key column="parent_id"/>
        <index column="Sequence"/>

        <composite-element>
            <property name="CustomerId"/>
            <many-to-one name="Child"/>
        </composite-element>
    </list>

</class>

<class name="Child">
    <id .../>
    <property .../>
</class>

To enhance performance, try to make it fetch the many-to-one by a join:

      <many-to-one name="Child" fetch="join" />

How mapping many-to-many with additional columes in NHibernate

This is definitely a duplicate of several other questions on StackOverflow. These are 3 of the many.

nhibernate many-to-many mapping - additional column in the mapping table?

additional fields in NHibernate many-to-many relation tables

Fluent Nhibernate Many-to-Many mapping with extra column

Brought to you by google:

site:StackOverflow.com nhibernate many to many additional columns

Many-to-many with extra columns on join table

You need to split your many-to-many relationship into two many-to-one relationships, just like in your database.

So, one User has many UserProduct items, and one Product has many UserProduct items.
A UserProduct has one User and one Product.

---

Related Topics