Databases
StackOverflow2013
Postsdbo
POProblem with NHibernate
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblem with NHibernate
    primarykey
    data
    text
    <p>I am trying to get a list of Products that share the Category.</p> <p>NHibernate returns no product which is wrong.</p> <p>Here is my Criteria API method : </p> <pre><code>public IList&lt;Product&gt; GetProductForCategory(string name) { return _session.CreateCriteria(typeof(Product)) .CreateCriteria("Categories") .Add(Restrictions.Eq("Name", name)) .List&lt;Product&gt;(); } </code></pre> <p>Here is my HQL method :</p> <pre><code>public IList&lt;Product&gt; GetProductForCategory(string name) { return _session.CreateQuery("select from Product p, p.Categories.elements c where c.Name = :name").SetString("name",name).List&lt;Product&gt;(); } </code></pre> <p>Both methods return no product when they should return 2 products.</p> <p>Here is the Mapping for the Product class :</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="CBL.CoderForTraders.DomainModel" namespace="CBL.CoderForTraders.DomainModel"&gt; &lt;class name="Product" table="Products" &gt; &lt;id name="_persistenceId" column="ProductId" type="Guid" access="field" unsaved-value="00000000-0000-0000-0000-000000000000"&gt; &lt;generator class="assigned" /&gt; &lt;/id&gt; &lt;version name="_persistenceVersion" column="RowVersion" access="field" type="int" unsaved-value="0" /&gt; &lt;property name="Name" column="ProductName" type="String" not-null="true"/&gt; &lt;property name="Price" column="BasePrice" type="Decimal" not-null="true" /&gt; &lt;property name="IsTaxable" column="IsTaxable" type="Boolean" not-null="true" /&gt; &lt;property name="DefaultImage" column="DefaultImageFile" type="String"/&gt; &lt;bag name="Descriptors" table="ProductDescriptors"&gt; &lt;key column="ProductId" foreign-key="FK_Product_Descriptors"/&gt; &lt;one-to-many class="Descriptor"/&gt; &lt;/bag&gt; &lt;bag name="Categories" table="Categories_Products" &gt; &lt;key column="ProductId" foreign-key="FK_Products_Categories"/&gt; &lt;many-to-many class="Category" column="CategoryId"&gt;&lt;/many-to-many&gt; &lt;/bag&gt; &lt;bag name="Orders" generic="true" table="OrderProduct" &gt; &lt;key column="ProductId" foreign-key="FK_Products_Orders"/&gt; &lt;many-to-many column="OrderId" class="Order" /&gt; &lt;/bag&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>And finally the mapping for the Category class :</p> <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt; &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="CBL.CoderForTraders.DomainModel" namespace="CBL.CoderForTraders.DomainModel" default-access="field.camelcase-underscore" default-lazy="true"&gt; &lt;class name="Category" table="Categories" &gt; &lt;id name="_persistenceId" column="CategoryId" type="Guid" access="field" unsaved-value="00000000-0000-0000-0000-000000000000"&gt; &lt;generator class="assigned" /&gt; &lt;/id&gt; &lt;version name="_persistenceVersion" column="RowVersion" access="field" type="int" unsaved-value="0" /&gt; &lt;property name="Name" column="Name" type="String" not-null="true"/&gt; &lt;property name="IsDefault" column="IsDefault" type="Boolean" not-null="true" /&gt; &lt;property name="Description" column="Description" type="String" not-null="true" /&gt; &lt;many-to-one name="Parent" column="ParentID"&gt;&lt;/many-to-one&gt; &lt;bag name="SubCategories" inverse="true"&gt; &lt;key column="ParentID" foreign-key="FK_Category_ParentCategory" /&gt; &lt;one-to-many class="Category"/&gt; &lt;/bag&gt; &lt;bag name="Products" table="Categories_Products"&gt; &lt;key column="CategoryId" foreign-key="FK_Categories_Products" /&gt; &lt;many-to-many column="ProductId" class="Product"&gt;&lt;/many-to-many&gt; &lt;/bag&gt; &lt;/class&gt; &lt;/hibernate-mapping&gt; </code></pre> <p>Can you see what could be the problem ?</p> <p>if I remove the add line my query is :</p> <pre><code>return _session.CreateCriteria(typeof(Product)) .CreateCriteria("Categories") .List&lt;Product&gt;(); </code></pre> <p>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.</p> <p>So there is something wrong when I add the line : .Add(Restrictions.Eq("Name", name))</p> <p>Here is the Sql generated including the Restriction line :</p> <p>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'</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBernard Larouche
    1. USBernard Larouche
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou should post the generated sql
      singulars
      1. POProblem with NHibernate
      1. USdotjoe
    2. COsql looks good to me. Are you sure the Categories_Products table has the correct data?
      singulars
      1. POProblem with NHibernate
      1. USdotjoe
    3. COYes. On the watch window I can see the data
      singulars
      1. POProblem with NHibernate
      1. USBernard Larouche
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload