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.

I notice there's an inverse=true attribute on the CategoryProducts, but not on the ProductCategory... does it work if you remove that? — jbehren, Feb 01 '11 at 14:12
I'm not familiar with the `` element, and am having trouble finding documentation for it. Is it just a ConfORM thing? — Daniel Schilling, Aug 12 '11 at 03:25

score 1 · Accepted Answer · answered Aug 12 '11 at 03:55

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.

score 0 · Answer 2 · answered Feb 02 '11 at 15:50

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.

score 0 · Answer 3 · answered Feb 02 '11 at 16:20

0

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.

answered Feb 02 '11 at 16:20

Jonathan

2,318
7
25
44

unless I'm missing something, the key column is supposed to match the side of the equation he's searching from, so in the case of the Category side, his mapping is correct. Same holds true from the Products side. The key identifies all records belonging to the specified entity. – nkirkes Feb 02 '11 at 16:51

NHibernate relationship has an issue with two-directional getting data

3 Answers3