如何有效地使用过滤器查询不同级别的孩子? [英] How to efficiently query with filter on children at different levels?

问题描述

我有3个班级，一对一的关系

I have 3 classes with one to many relationships

A has many B has many C

我想在两个条件之一为真的情况下加载我的所有数据:

I want to load all of my data where either of two conditions are true:

B.someField is in a list of strings

C.someOtherField is in that same list of strings

有时B是匹配项，有时C是匹配项.我需要加载所有匹配项，并为完整级别加载它们.

Sometimes B will be the match, sometimes C will be the match. I need to load all matches, and load them for the full levels.

换句话说，如果B.someField匹配，我想加载该B及其父A和所有子C.

In other words, if B.someField matches, I want to load that B, and its parent A, and all children C.

同样，如果C.someOtherField匹配，我想加载其父B和B的父A.

Likewise, if C.someOtherField matches, I want to load its parent B and B's parent A.

有没有一种有效的方法来进行此查询?您将如何在nHibernate中做到这一点?

Is there an efficient way make this query? How would you do it in nHibernate?

推荐答案

使用NHibernate对多个子集合进行查询时，我倾向于使用lamq而不是lamqda来使用LINQ查询语法，因为它不仅容易编写，而且代码看起来更干净.

When quering over multiple child collections using NHibernate, I tend to prefer to use the LINQ query syntax over lambdas because not only is it much easier to write but the code looks cleaner.

以这个示例为例，它会为您指明正确的方向:

Take this example which should point you in the right direction:

var listOfString = new List<string>() { "String1", "String2" };

var customers =
(
    from a in session.Query<A>()
    from b in a.B
    from c in b.C
    where a.Status == "Active"
            && listOfStrings.Contains( b.SomeField )
            && listOfStrings.Contains( c.someOtherField )
    select a )
    .ToList();

通过使用LINQ查询语法，我们可以轻松地添加多个FROM，以获得对子集合的访问权限.然后，只需应用您的WHERE条件即可.对于您的特殊情况，您只需使用LINQ的Contains()方法，该方法等同于SQL的IN运算符.

By using the LINQ query syntax, we can easily add multiple FROMs to gain access to the child collections. Then it's just a matter of apply your WHERE conditions. For your particular case, you would simply use LINQ's Contains() method which equates to SQL's IN operator.

听起来您也想将所有子集合都加载到内存中，因此请确保使用NHibernate的.Fetch()方法来像这样急切地加载该数据:

It also sounds like you are wanting to load all the child collections into memory as well so be sure to use NHibernate's .Fetch() method to eagerly load that data like so:

var customers =
(
    from a in session.Query<A>()
    from b in a.B
    from c in b.C
    where a.Status == "Active"
            && listOfStrings.Contains( b.SomeField )
            && listOfStrings.Contains( c.someOtherField )
    select a )
    .Fetch( x => x.B )
    .ThenFetchMany( x => x.First().C )
    .ToList();

FYI，使用上述.ThenFetchMany()方法内的.First()是我从NHibernate的Jira问题跟踪网站上学到的一个技巧:

FYI, using the .First() inside the .ThenFetchMany() method above is a trick I learned from the NHibernate's Jira issue tracker website: https://nhibernate.jira.com/browse/NH-2972?focusedCommentId=22150&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-22150

这篇关于如何有效地使用过滤器查询不同级别的孩子?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！