How to use NHibernate ManyToMany with properties (columns) on Join Table (Fluent NHibernate)

Question

I have the following classes that I need NHibernate to play nicely with. How do I do it?

public class Customer
{
   public int ID { get; set; }
   public string Name {get;set;}
}

public class Product
{
   public int ID { get; set; }
   public string Name {get;set;}
}

public class CustomerPricing
{
   public int ID { get; set; }
   public decimal Price {get;set;}
   public Customer Customer { get; set; }
   public Product Product {get;set;}
   public datetime ExpiresOn { get; set; }
   public string ApprovedBy {get;set;}
}

I am using fluent mappings and the HasManyToMany doesn't work for this (that I can tell). I'm currently working around it by using a HasMany then doing some LINQ queries in the model (yuck).

Thanks in advance.

Kyle

Answer 1

No idea how to do it in Fluent, but because you're storing data in the joining table, you'll need to go Many-to-one from CustomerPricing to both Customer and Product. In an hbm.xml, the mapping for CustomerPricing would look like

<many-to-one name="Product" column="ProductID" not-null="true" />
<many-to-one name="Customer" column="CustomerID" not-null="true" />

Then in your Customer class (or in both, if desired) you'd add:

<set name="CustomerPricing" table="CustomerPricing" inverse="true">
        <key column="CustomerID"></key>
        <one-to-many class="CustomerPricing" />
</set>

Answer 2

Try this

public class Customer
{
    public int ID { get; set; }
    public string Name { get; set; }
    public IList<Product> ProductsOwned { get; set; }
}

public class Product
{
    public int ID { get; set; }
    public string Name { get; set; }
    public IList<Customer> Owners { get; set; }
}

with Customer mapping as

 HasManyToMany<Product>(x => x.ProductsOwned) 
.WithTableName("CustomerPricing") 
.WithParentKeyColumn("CustomerID") 
.WithChildKeyColumn("ProductID")

and Product mapping as

 HasManyToMany<Customer>(x => x.Owners) 
.WithTableName("CustomerPricing") 
.WithParentKeyColumn("ProductID") 
.WithChildKeyColumn("CustomerID")