Problem with NHibernate

Question

I am trying to get a list of Products that share the Category.

NHibernate returns no product which is wrong.

Here is my Criteria API method :

public IList<Product> GetProductForCategory(string name)
        {

            return _session.CreateCriteria(typeof(Product))
                .CreateCriteria("Categories")
                .Add(Restrictions.Eq("Name", name))
                .List<Product>();

        }

Here is my HQL method :

public IList<Product> GetProductForCategory(string name)
        {
            return _session.CreateQuery("select from Product p, p.Categories.elements c where c.Name = :name").SetString("name",name).List<Product>();


        }

Both methods return no product when they should return 2 products.

Here is the Mapping for the Product class :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="CBL.CoderForTraders.DomainModel" namespace="CBL.CoderForTraders.DomainModel">
  <class name="Product" table="Products" >
    <id name="_persistenceId" column="ProductId" type="Guid" access="field" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="assigned" />
    </id>
    <version name="_persistenceVersion" column="RowVersion" access="field" type="int" unsaved-value="0" />

    <property name="Name" column="ProductName" type="String" not-null="true"/>
    <property name="Price" column="BasePrice" type="Decimal" not-null="true" />
    <property name="IsTaxable" column="IsTaxable" type="Boolean" not-null="true" />
    <property name="DefaultImage" column="DefaultImageFile" type="String"/>

    <bag name="Descriptors" table="ProductDescriptors">
      <key column="ProductId" foreign-key="FK_Product_Descriptors"/>
      <one-to-many class="Descriptor"/>
    </bag>
    <bag name="Categories"  table="Categories_Products" >
      <key column="ProductId" foreign-key="FK_Products_Categories"/>
      <many-to-many class="Category" column="CategoryId"></many-to-many>
    </bag>

    <bag name="Orders" generic="true" table="OrderProduct" >
      <key column="ProductId" foreign-key="FK_Products_Orders"/>
           <many-to-many column="OrderId" class="Order" />
    </bag>


  </class>
</hibernate-mapping>

And finally the mapping for the Category class :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="CBL.CoderForTraders.DomainModel" namespace="CBL.CoderForTraders.DomainModel" default-access="field.camelcase-underscore" default-lazy="true">

  <class name="Category" table="Categories" >
    <id name="_persistenceId" column="CategoryId" type="Guid" access="field" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="assigned" />
    </id>
    <version name="_persistenceVersion" column="RowVersion" access="field" type="int" unsaved-value="0" />

    <property name="Name" column="Name" type="String" not-null="true"/>
    <property name="IsDefault" column="IsDefault" type="Boolean" not-null="true" />
    <property name="Description" column="Description" type="String" not-null="true" />

    <many-to-one name="Parent" column="ParentID"></many-to-one>

    <bag name="SubCategories" inverse="true">
      <key column="ParentID" foreign-key="FK_Category_ParentCategory" />
      <one-to-many class="Category"/>
    </bag>
    <bag name="Products" table="Categories_Products">
      <key column="CategoryId" foreign-key="FK_Categories_Products" />
      <many-to-many column="ProductId" class="Product"></many-to-many>
    </bag>
  </class>
</hibernate-mapping>

Can you see what could be the problem ?

if I remove the add line my query is :

return _session.CreateCriteria(typeof(Product))
                .CreateCriteria("Categories")
                .List<Product>();

looking at my watch window now I return 5 products which have Categories attached to them. The name of the category I am looking for in my initial query appears on 2 products.

So there is something wrong when I add the line : .Add(Restrictions.Eq("Name", name))

Here is the Sql generated including the Restriction line :

NHibernate: SELECT this_.ProductId as ProductId23_1_, this_.RowVersion as RowVersion23_1_, this_.ProductName as ProductN3_23_1_, this_.BasePrice as BasePrice23_1_, this_.IsTaxable as IsTaxable23_1_, this_.DefaultImageFile as DefaultI6_23_1_, categories3_.ProductId as ProductId, category1_.CategoryId as CategoryId, category1_.CategoryId as CategoryId16_0_, category1_.RowVersion as RowVersion16_0_, category1_.Name as Name16_0_, category1_.IsDefault as IsDefault16_0_, category1_.Description as Descript5_16_0_, category1_.ParentID as ParentID16_0_ FROM Products this_ inner join Categories_Products categories3_ on this_.ProductId=categories3_.ProductId inner join Categories category1_ on categories3_.CategoryId=category1_.CategoryId WHERE category1_.Name = @p0; @p0 = 'Momemtum'

Answer 1

try a subquery using detached criteria to produce the equivalent to the following plain sql

select * from Products where ProductId in (select distinct cp.ProductId from Categories_Products cp inner join Categories c on cp.CategoryId = c.CategoryId and c.Name = name)

something like this ...

DetachedCriteria subCriteria = DetachedCriteria.For(typeof(Category))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .SetProjection(Projections.Property("Category.Products.ProductId"))
    .Add(Restrictions.PropertyEq("Name", name));

return _session.CreateCriteria.For(typeof(Product))
    .Add(Subqueries.PropertyIn("ProductId", subCriteria))
    .List<Product>();

Ack, the Category.Products.ProductId might not be correct, but maybe you can see what I'm trying to do here and figure out the correct projection to use in the subquery.

Answer 2

Your Criteria query looks fine - just like dozens I've written.

The problem is with your many-to-many link table.

In the Product mapping you have this:

<bag name="Categories"  table="Categories_Products" >
  <key column="ProductId" foreign-key="FK_Products_Categories"/>
  <many-to-many class="Category" column="CategoryId"></many-to-many>
</bag>

In the Category mapping, you have this:

<bag name="Products" table="Categories_Products">
  <key column="CategoryId" foreign-key="FK_Categories_Products" />
  <many-to-many column="ProductId" class="Product"></many-to-many>
</bag>

It looks to me as though you've reversed your foreign keys, linking ProductId to FK_Products_Categories instead of FK_Categories_Products.

Something I find useful in this kind of situation is to turn on SQL logging for NHibernate, then look at the generated SQL. Usually this gives me a pretty big clue as to my mapping error. For more, see Configure Log4Net for use with NHibernate.

Answer 3

It's hard to say just from the query and the mappings. Also interesting would be how you create the data.

The HQL should actually look like this:

select p
from Product p join p.Categories c 
where c.Name = :name

which should work, assumed that your data in the database is correct.

By the way, you could (should) map the bidirectional many-to-many relation between products and categories in the same table, because it it most probably the same data. Just map it to the same table/columns and make one of the man-to-many relations inverse="true".

You need to make sure that both collections are updated in synch. If you only add the product to the categories, the link is missing in the product. Maintaining the consistency of the model is not responsibility of NHibernate.

To find an error like this:

• Take a look at the generated SQL (set show_sql to true in the configuration. You'll see it in the console.) When you use SqlServer, you can also use the Profiler.
• Execute the query in an SQL console.
• Take a look into the data in the database.

Answer 4

Really sorry guys. My Mapping and code is fine. The problem was the name parameter.