NHibernate relationship has an issue with two-directional getting data

Question

I have 2 tables which have many-to-many relation.

Code of entities

public class Product : BaseEntity
{     
    public virtual string Name { get; set; }
    public virtual IList<Category> ProductCategory { get; set; }
    public virtual float Price { get; set; }
    public virtual string Description { get; set; }
    public virtual DateTime DateOfAdd { get; set; }
    public virtual float Discount { get; set; }
    public virtual int SaleCount { get; set; }
    public virtual byte[] Image { get; set; }
}

public class Category : BaseEntity
{
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
    public virtual IList<Product> CategoryProducts { get; set; }
    public virtual void AddProduct(Product product)
    {
        this.CategoryProducts.Add(product);
    }
    public virtual void DeleteProduct(Product product)
    {
        this.CategoryProducts.Remove(product);
    }
}

I map this classes as many-to-many in the conform mapping.

  relationalMapper.ManyToMany<Product, Category>();

In xml this mapping compiles into this:

  <class name="Product">
    <id name="Id" type="Int32">
      <generator class="identity" />
    </id>
    <property name="Name" />
    <list name="ProductCategory" table="ProductCategory">
      <key column="product_key" />
      <list-index />
      <many-to-many class="Category" column="category_key" />
    </list>
    <property name="Price" />
    <property name="Description" />
    <property name="DateOfAdd" />
    <property name="Discount" />
    <property name="SaleCount" />
    <property name="Image" lazy="true" />
  </class>
<class name="Category">
    <id name="Id" type="Int32">
      <generator class="identity" />
    </id>
    <property name="Name" />
    <property name="Description" />
    <list name="CategoryProducts" table="ProductCategory" inverse="true">
      <key column="category_key" />
      <list-index />
      <many-to-many class="Product" column="product_key" />
    </list>
  </class>

The issue is that I can get categories from product entity, but when I try get products from category it's doesn't work and the list is empty.

Answer 1

I don't think you can have a list on both sides of the many-to-many. Only one side can be a list - the other side should just be an bag or set. Consider the following data in the ProductCategory table:

Category_id Product_id Index
=========== ========== =====
1           3          0
1           4          1
2           3          0
2           4          1

If you access Category.CategoryProducts, all is well. Category #1 has two products: the first product is #3 and the second is #4.

However, if you try to access Product.ProductCategory, the same Index column cannot also be used for this list. Our data says that Product #3 has two categories: #1 and #2 - but both of them want to be the first category in the list, with Index = 0. Product #4 also has two categories, but neither of them want to be the first category in the list because they both have Index = 1.

The Index values in a list should be sequential starting from zero. I don't think it's possible to do this for two lists driven by the same table.

Answer 2

You are using the <key column="category_key" /> instead of the <key column="product_key" /> in your mapping for

<list name="CategoryProducts" table="ProductCategory" inverse="true">       
    <key column="category_key" />       
    <list-index />       
    <many-to-many class="Product" column="product_key" />     
</list> 

Not sure of the rest of the solution looks like may be an issue.

Answer 3

The inverse attribute shouldn't have anything to do with a select, it should only affect which entity controls the relationship.

I've got the exact same configuration, although I'm using <set> instead of <list>:

<set name="Organizations" table="ORGANIZATIONS_LOCATIONS" inverse="true">
    <key column="LOCATION_ID" />
    <many-to-many class="Organization" column="ORGANIZATION_ID" />
</set>

<set name="Locations" table="ORGANIZATIONS_LOCATIONS" inverse="false" cascade="all">
    <key column="ORGANIZATION_ID" />
    <many-to-many class="Location" column="LOCATION_ID" />
</set>

Perhaps try changing the mapping to a set, and remove the <list-index /> since it isn't needed.