Querying with NHibernate

Question

I am new to NHibernate and I am trying to learn how to query my data.

Below is the configuration xml. Only the recipe is shown.

I want to be able to query recipes by recipetitle from keywords entered and also ingredients from ingredientname.

So you might enter "pasta wine" for example.

This is what I have tried but gives me an error.

    hql = "from Recipe r " +
    "left join r.Images " +
    "inner join r.User " +
    "inner join r.Ingredients i " +
    "where i.IngredientName Like '%pasta%' OR i.IngredientName Like '%wine%' OR r.RecipeTitle Like '%pasta' OR r.RecipeTitle Like '%wine%'";

I want to eager load the collections as well.

Am I going about querying right?? I need to able to build the query string from my search criteria. This would be easy form me in SQL.

Malcolm

  <class name="Recipe" table="Recipes" xmlns="urn:nhibernate-mapping-2.2">
    <id name="RecipeID" type="Int32" column="RecipeID">
      <generator class="identity" />
    </id>
    <property name="RecipeTitle" type="String">
      <column name="RecipeTitle" />
    </property>
    <property name="Completed" type="Boolean">
      <column name="Completed" />
    </property>
    <property name="ModifiedOn" type="DateTime">
      <column name="ModifiedOn" />
    </property>
    <property name="Rating" type="Double">
      <column name="Rating" />
    </property>
    <property name="PrepTime" type="Int32">
      <column name="PrepTime" />
    </property>
    <property name="CookTime" type="Int32">
      <column name="CookTime" />
    </property>
    <property name="Method" type="String">
      <column name="Method" />
    </property>
    <bag name="Images" inverse="true" cascade="all">
      <key column="RecipeID" />
      <one-to-many class="OurRecipes.Domain.RecipeImage, OurRecipes.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
    </bag>
    <many-to-one name="Category" column="CategoryID" />
    <bag name="Comments" inverse="true" cascade="all">
      <key column="RecipeID" />
      <one-to-many class="OurRecipes.Domain.Comment, OurRecipes.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
    </bag>
    <many-to-one name="User" column="EnteredByID" />
    <bag name="Ingredients" inverse="true" cascade="all">
      <key column="RecipeID" />
      <one-to-many class="OurRecipes.Domain.Ingredient, OurRecipes.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
    </bag>
  </class>

Harridan

Answer 1

To build dynamic queries, I would use the criteria API. This makes the dynamic query much more stable, because you don't need string operations to build it up.

ICriteria query = Session.CreateCriteria(typeof(Recipe), "r")
  .CreateCriteria("Ingredients", "i", JoinType.InnerJoin)
  .Add(
    Expression.Disjunction() // OR
      .Add(Expression.Like("i.IngredientName", "%pasta%"))
      .Add(Expression.Like("i.IngredientName", "%wine%"))
      .Add(Expression.Like("r.RecipeTitle", "%pasta%"))
      .Add(Expression.Like("r.RecipeTitle", "%wine%")));

List<Recipe> result = query.List<Recipe>();

Edit:

For eager loading you could set the fetch-mode:

ICriteria query = Session.CreateCriteria(typeof(Recipe), "r")
  .SetFetchMode("Images", FetchMode.Join)
  .SetFetchMode("Comments", FetchMode.Join)
  .SetFetchMode("Ingredients", FetchMode.Join)

But I wouldn't do this because you get the results multiplied by the number of Images, Comments and Ingredients. So if you had 4 Images, 2 Comments and 12 Ingredients, you get your recipe 96 times. You don't recognize this, because NHibernate puts the things together again, but it generates traffic between the application and the database. So better let NHibernate load it with separate queries.

---

One more edit to show dynamic query composition.

// filter arguments, all are optional and should be omitted if null
List<string> keywords;
TimeSpan? minCookingTime;
TimeSpan? maxCookingTime;
int? minRating;
int? maxRating;

ICriteria query = Session.CreateCriteria(typeof(Recipe), "r");

if (keyword != null)
{
  // optional join
  query.CreateCriteria("Ingredients", "i", JoinType.InnerJoin);

  // add keyword search on ingredientName and RecipeTitle
  var disjunction = Expression.Disjunction();
  foreach (string keyword in keywords)
  {
    string pattern = String.Format("%{0}%", keyword);
    disjunction
      .Add(Expression.Like("i.IngredientName", pattern))
      .Add(Expression.Like("r.RecipeTitle", pattern)); 
  }
  query.Add(disjunction)
}

if (minCookingTime != null)
{
  query.Add(Expression.Ge(r.CookingTime, minCookingTime.Value));
}
if (maxCookingTime != null)
{
  query.Add(Expression.Le(r.CookingTime, maxCookingTime.Value));
}

if (minRating != null)
{
  query.Add(Expression.Ge(r.Rating, minRating.Value));
}
if (maxRating != null)
{
  query.Add(Expression.Le(r.Rating, maxRating.Value));
}

Roberto

In order to solve this problem, you can get the DistinctRootEntityResultTransformer – Palaeobotany

Why would you use FetchMode.Join instead of FetchMode.Eager for these loads if you were truly intending to use the child objects? – Leboff

I don't see how that is dynamic when you have hard coded the keywords. How would you query if i gave you a string of words seperated by spaces??? – Harridan

@Malcolm: I don't really understand the question. This implementation is not dynamic, it's an example to show how it works. It is very easy to add expressions dynamically - instead of building up an HQL string which could have syntax errors (eg. when no filter is given and you write an empty WHERE clause). In our project, I even add subqueries and joins dynamically, only if they are needed. Using criteria, this is simple, with HQL you'd have troubles. – Roberto

@Chad: FetchMode.Join and FetchMode.Eager are the same. hibernate.org/hib_docs/v3/api/org/hibernate/FetchMode.html – Roberto

Ok fine. Can you give me an exmaple of how you would do as I said. Given a string of words create a Criteria query, assume that the field being searched is RecipeTitle. I do not understand how you would do this???? – Harridan

Let me explain what i am doing. I have a search form where the user can enter keywords which searches RecipeTitle or an IngredientName or enter a range for cooking time or a range for the recipe rating etc. So I want to build a criteria query from this search information. So this would be dynamic right. Could you explain one way of doing this please? – Harridan

@Malcolm: added an example how the dynamic query could be composed. Of course it depends on the exact situation, but I think you can now build it up yourself. – Roberto

I want to add that alot of people will get something out of this post I'm sure. – Harridan

Answer 2

Both Stefan's and Sathish's examples concatenate % operators into the SQL. This is unnecesary as Restrictions.Like (nhib 2.0+) and Expression.Like (before v2.0) have 3 parameter versions with a MatchMode.

Disjunction keywordsCriteria = Restrictions.Disjunction();
foreach (var keyword in keywords)
{
    keywordsCriteria.Add(Restrictions.Like("i.IngredientName", keyword, MatchMode.Anywhere));
    keywordsCriteria.Add(Restrictions.Like("r.RecipeTitle", keyword, MatchMode.Anywhere));
}

Full text queries are also available with NHibernate Search. See Ayende's example for more details.

Dentiform

Answer 3

Here is the above criteria with dynamic keywords

string searchQuery = "wine pasta";

ICriteria query = Session.CreateCriteria(typeof(Recipe), "r")
                    .CreateCriteria("Ingredients", "i", JoinType.InnerJoin)
                    .SetFetchMode("Images", FetchMode.Join)
                    .SetFetchMode("Comments", FetchMode.Join)
                    .SetFetchMode("Ingredients", FetchMode.Join)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer());

var keywords = searchQuery.Split(' ');

Disjunction keywordsCriteria = Restrictions.Disjunction();
foreach (var keyword in keywords)
{
    keywordsCriteria.Add(Restrictions.Like("i.IngredientName", string.Format("%{0}%", keyword)));
    keywordsCriteria.Add(Restrictions.Like("r.RecipeTitle", string.Format("%{0}%", keyword)));
}

query.Add(keywordsCriteria);

List<Recipe> result = query.List<Recipe>();

Manifesto