是否可以使用NHibernate查询具有一个或多个可能子对象的所有对象? [英] Is it possible to query all objects that have one or more possible children using NHibernate?

问题描述

我有一个名为Recipes的表，该表每行包含一个配方.我还有一个名为RecipeIngredients的表，其中包含一种特定配方所使用的成分.因此，每个Recipe行都有一个或多个子RecipeIngredients行.

I have a table called Recipes which contain one recipe per row. I also have a table called RecipeIngredients which contain one ingredient as used by a particular recipe. Thus, each Recipe row has one or more children RecipeIngredients rows.

我想要做的是创建一个查询，以查找包含所需成分列表中所有成分的所有配方.例如，告诉我所有使用面粉，鸡蛋或香蕉的食谱.

What I'm trying to do is create a query to find all recipes that contain any ingredients in a list of desired ingredients. For example, show me all recipes that use either flour, eggs, or bananas.

SQL看起来像这样:

The SQL would look something like this:

SELECT * FROM Recipes r
   WHERE EXISTS (select 1 from RecipeIngredients where RecipeId = r.RecipeId and IngredientId = ANY (5, 10, 15) limit 1);

但是，我很难弄清楚如何将其表示为LINQ查询或使用.QueryOver<T>方法.我不想在SQL中进行硬编码，因为这需要与数据库无关，并且我希望配置的NHibernate方言生成正确的代码.

However, I'm having a tough time figuring out how to express this as a LINQ query, or using the .QueryOver<T> method. I don't want to hard code in the SQL since this needs to be database agnostic and I want the configured NHibernate dialect to generate the correct code.

有什么想法吗?

推荐答案

NHibernate支持此SQL语句，称为

NHibernate has support for this SQL statements, called

• 15.8.分离的查询和子查询，
• 16.8.子查询

• 15.8. Detached queries and subqueries,
• 16.8. Subqueries

语法如下:

var session = ...// get a ISession 

Reciepe reciepe = null; // this will be a reference to parent

// the SELECT inside of EXISTS
var subquery = QueryOver.Of<ReciepeIngredient>()
    // The PARENT handling here
    // the filter, to find only related ingredients
    .Where(item => item.ReciepeId == reciepe.ID)
    .Where(Restrictions.In("ID", new[] { 5, 10, 15 }))
    // Select clause
    .Select(ing => ing.ID)

    ;

有了上面的子查询，我们可以像这样使用它

Having the above subquery, we can use it like this

// the '() => reciepe' setting is essential here, it represents parent in a subquery
var query = session.QueryOver<Reciepe>(() => reciepe);

query.WithSubquery
    // our EXISTS (...
    .WhereExists(subquery);

var list = query
    .List<Reciepe>();

注意:让我们在这里在HasMany参考上查询更深入的子查询

这篇关于是否可以使用NHibernate查询具有一个或多个可能子对象的所有对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！