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

本文是小编为大家收集整理的关于是否可以用NHibernate查询所有有一个或多个可能的子女的对象?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个名为Recipes的表,每行包含一个食谱.我还有一个称为RecipeIngredients的表,该表包含特定食谱所使用的一种成分.因此,每个Recipe行有一个或多个孩子RecipeIngredients行.

我要做的是创建一个查询,以查找所需成分列表中所有成分的所有配方.例如,向我展示所有使用面粉,鸡蛋或香蕉的食谱.

SQL看起来像这样:

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方言能够生成正确的代码.

有什么想法?

推荐答案

nhibrnate对此SQL语句有支持,称为

语法就是这样:

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)

    ;

具有上述子查询,我们可以像这样使用

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

注意:让我们在此处检查更深的子查询(IES)用法 Hasmany参考上查询

查询

其他推荐答案

更多细节:

Radim的答案是表达子问题的最佳方法,但是有几个 gotchas 花了我一段时间来弄清楚.因此,我也会发布答案以填写细节.

首先,线:

.Where(Restrictions.In("ID", new[] { 5, 10, 15 }))

不实际上ID是指实体本身.换句话说:

.Where(Restrictions.In("Ingredient", arrayOfIds))

将投掷一个非常令人困惑的零引用异常,因为 impredient 字段映射到Ingredients对象.使用"IngredientId"也无法正常工作.在这种情况下,您必须使用此信息:

.Where(Restrictions.In("Ingredient", arrayOfIds
   .Select(id => new Ingredients(id)).ToArray()))

将ID数组铸成Ingredients对象的数组.之后,事情开始工作.

我还发现了一个简单的性能改进,至少在PostgreSQL上,查询运行明显.如果您从:

更改子查询

存在的地方(从配方中选择配方 recipeid = r.recipeid和ingredientid in(:p0,:p1))

to:

存在的地方(从配方中选择配方 recipeid = r.recipeid和ingredientid in(:p0,:p1)限制1)

它只需要检查嵌套查询中的一行即可.查询对我来说速度的速度大约是两倍.这很容易表达:

var subquery = QueryOver.Of<RecipeIngredients>()
   .Where(item => item.Recipe.RecipeId == recipe.RecipeId)
   .Where(Restrictions.In("Ingredient", allowedIngs))
   .Select(i => i.RecipeIngredientId).Take(1);

希望这会有所帮助!

其他推荐答案

尝试此linq查询:

        recipes.Where(r => r.RecipeIngredients.Any(i => new long[]{5, 10, 15}.Contains(i.Id)));

本文地址:https://www.itbaoku.cn/post/1557126.html

问题描述

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.

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

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.

Any ideas?

推荐答案

NHibernate has support for this SQL statements, called

The syntax would be like this:

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

NOTE: let's check even more deeper subquery(ies) usage here Query on HasMany reference

其他推荐答案

A Few More Details:

Radim's answer turns out to be the best way to express the sub-query, however there's a few gotchas that took me a while to figure out. Thus, I'll post an answer as well to fill in the details.

First off, the line:

.Where(Restrictions.In("ID", new[] { 5, 10, 15 }))

Doesn't actually work if ID refers to an entity itself. In other words:

.Where(Restrictions.In("Ingredient", arrayOfIds))

Will throw a very confusing null reference exception since the Ingredient field maps to a Ingredients object. Using "IngredientId" doesn't work either. In that case, you have to use this:

.Where(Restrictions.In("Ingredient", arrayOfIds
   .Select(id => new Ingredients(id)).ToArray()))

To cast the ID array to an array of Ingredients objects. After that, things start working.

I also found an easy performance improvement that made the query run noticably faster, at least on PostgreSQL. If you change the sub-query from:

WHERE exists (SELECT RecipeIngredientId FROM recipeingredients WHERE RecipeId = r.RecipeId and IngredientId in (:p0, :p1))

To:

WHERE exists (SELECT RecipeIngredientId FROM recipeingredients WHERE RecipeId = r.RecipeId and IngredientId in (:p0, :p1) LIMIT 1)

It will only have to check a single row within the nested query. The query ran about twice as fast for me. This is easy to express:

var subquery = QueryOver.Of<RecipeIngredients>()
   .Where(item => item.Recipe.RecipeId == recipe.RecipeId)
   .Where(Restrictions.In("Ingredient", allowedIngs))
   .Select(i => i.RecipeIngredientId).Take(1);

Hope this helps!

其他推荐答案

Try this Linq query:

        recipes.Where(r => r.RecipeIngredients.Any(i => new long[]{5, 10, 15}.Contains(i.Id)));