用NHibernate查询 [英] Querying with NHibernate

问题描述

我是NHibernate的新手，我正在尝试学习如何查询我的数据.

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

下面是配置xml.仅显示食谱.

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%'";

我也希望加载收藏集.

我要查询正确吗?? 我需要能够根据我的搜索条件构建查询字符串. 在SQL中，这对我来说很容易.

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.

马尔科姆

  <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>

推荐答案

要构建动态查询，我将使用条件API.这使动态查询更加稳定，因为您不需要字符串操作即可构建它.

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>();

对于急切的加载，您可以设置提取模式:

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)

但是我不会这样做，因为您得到的结果乘以图像，注释和成分的数量.因此，如果您有4张图片，2条评论和12种成分，则可以得到96次食谱.您不会意识到这一点，因为NHibernate再次将它们放在一起，但是会在应用程序和数据库之间产生流量.因此，最好让NHibernate通过单独的查询加载它.

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));
}

这篇关于用NHibernate查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！