How to insert data in an many-to-many Association table using NHibernate

Answer 1

You don't need to map your join table with its own type. And also, you shouldn't expose Formula.ID from a property of another class. Here is what you need

3 database Tables

Ingredients:  PK:ID(Identity(1,1), Name, Description
Formula: PK:ID(Identity(1,1), Name, Description
IngredientFormulaJoin: FK:IngredientID, FK:FormulaID

create table dbo.Ingredients(id int primary key identity(1,1), Name varchar(100))
create table dbo.formulas(id int primary key identity(1,1), Name varchar(100))
create table dbo.ingredientformulajoin(ingredientid int foreign key references dbo.ingredients(id), formulaid int foreign key refernces dbo.formula(id))

public class Forumula() {
  public Formula(){
  Ingredients = new List<Ingredient>();
}
  public int PK:ID(Identity(1,1) { get; set; }
  public IList<Ingredient> Ingredients{ get; set; }
}

public class Ingredient() {
public Ingredient(){
   Formulas = new List<Formula>
}


  public int ID { get; set; }
  public IList<Forumula> Formulas { get; set; }
}

Here is the mapping:

<class name="App.Core.Domain.Ingredient, App.Core" table="ingredients">
    <set name="Formulas" table="formulaingredientjoin" inverse="false" cascade="all">
      <key column="ingredientid"/>
      <many-to-many class="App.Core.Domain.Forumula, App.Core" column="formulaid"/>
    </set>
</class>


<class name="App.Core.Domain.Formula, App.Core" table="formulas">
    <set name="Ingredients" table="formulaingredientjoin" inverse="false" cascade="all">
      <key column="formulaid"/>
      <many-to-many class="App.Core.Domain.Ingredient, App.Core" column="ingredientid"/>
    </set>
</class>

Add to the collections like this:

Formula formula = new Formula();
formula.Ingredients.Add(ingredient1)
formula.Ingredients.Add(ingredient2)
formula.Ingredients.Add(ingredient3)
session.Save(formula);
session.Flush();

Ingredient ingredient = new Ingredient();
ingredeient.Formulas.Add(formula1);
ingredeient.Formulas.Add(formula2);
session.Save(ingredient);
session.Flush();

You should never need to Map a join table as its own class. Also allow classes to encapsulate their own types. The only thing that should return a formula ID is a formula. You wouldn't put 'FormulaID' in ingredients for example, rather, call formula.ID.

Answer 2

An assosiation table should ALWAYS be mapped at the moment it gets an extra column other than the foreign keys (as in your case the IngredientAmount column). It is necessary to map the association column not only for the sake of saving the data in the extra column but also to be able to retrieve it. If you leave the association table un-mapped then you have no easy way to read its extra data (IngredientAmount column) from the database.

Having the association table mapped though will mean that you have to create a one-to-many property from Formula to the association table (FormulaIngredient) and then you have the option to include also or not (it is up to you) the many-to-many property to the Ingredients table.

<bag name="FormulaIngredients" lazy="true" inverse="true" cascade="all-delete-orphan">
    <key column="FormulaID" />
    <one-to-many class="FormulaIngredient" />
</bag>

<bag name="Ingredients" table="FormulaIngredient" lazy="true" cascade="all">
    <key column="FormulaID" />
    <many-to-many column="IngredientID" class="Ingredient" />
</bag>

Saving the data can happen though cascading or manually (again your choise).

If you use the Ingredients property to save Ingredient objects using cascading you will not be able to save any data to the IngredientAmount column of the association table (FormulaIngredient).

The way to save into the association table is to first save the Formula object, then save as many Ingredient objects as you need and then using the IDs of the saved Formula and Ingredient objects you can save FormulaIngredient objects that you have populated also their IngredientAmount property/column.

I believe if there are alternatives they will involve complicated mechanisms (Listeners, manual SQL insert statements etc) just to save and retrieve data that belong to the association and NOT the associated main entities and fetch it as part of one of the main entities (Formula or Ingredient).