Fluent Nhibernate Many-to-Many mapping with extra column

Question

I want to map sth like this using fluent Nhibernate but I am not sure how to map the inventory table

This is the tables I have :

Product (Id,Name, ...)

Warehouse(Id, Name, ...)

Inventory(Product_id, Warehouse_id, StockInHand)

and Mappings like below

 Public ProductMap()
 {
     Id(x => x.Id);
     Map(x => x.Name);
  
     HasManyToMany(x => x.StoresStockedIn)
       .Cascade.All()
      .Inverse()
      .Table("Inventory");
 }

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

The problem I face is that how can I map the StockInHand (how should the inventory model mapping?).

or are there other way to model this scenario ?

I have read some existing questions but not yet get clear understand what to do.

Thanks

Answer 1

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