过滤然后包括向下三个嵌套级别 [英] Filtering On ThenInclude Three Nested Levels down

问题描述

我正在尝试向下过滤三个子级别并仅查找 PropertyMailingAddress.Status== True 的子元素.

I am trying to filter three child levels down and find only child elements where PropertyMailingAddress.Status== True.

它仍然返回在 PropertyMailingAddress.Status 下为 False 的值

It is still returning values which are False under PropertyMailingAddress.Status

如何将过滤器向下转换三级并使用 ThenInclude 进行嵌套过滤?

How do I convert filter three levels down and conduct nested filtering with ThenInclude?

类结构嵌套如下:

1. 财产
2. 物业派对
3. 派对
4. 派对邮寄地址
5. PropertyMailingAddress <--- Status 应该等于 true(任何具有 Status == False 的孙子 PropertyMailingAddress 节点都应该从这个嵌套的孙子分支中删除，保留为 True 的 PropertyMailingAddress 节点)

var result = await propertyRepository.GetAll()
                .Include(pm => pm.PropertyParty).ThenInclude(x => x.Party).ThenInclude(x => x.PartyMailingAddress).ThenInclude(x => x.PropertyMailingAddress)
                .Where (a=> a.PropertyParty.Any(x=> (x.Party.PartyMailingAddress.Any(z => z.PropertyMailingAddress.Any(h => h.Status.HasValue && h.Status.Value && h.Status == true))))

推荐答案

人们倾向于使用 Include 作为 Select 的某种快捷方式.但是，包含所有属性通常会非常浪费处理能力，因为您不会使用其中的几个属性或因为您已经知道其值.

People tend to use Include as some kind of Shortcut for a Select. However, it is usually quite a waste of processing power to include all properties, because you won't use several of them or because you already know the value.

以有学生的学校为例，这是一种直接的一对多关系.每所学校都有零个或多个学生，每个学生就读于一所学校，即外键 SchoolId 所指的学校.

Take for instance a School with Students, a straightforward one-to-many relation. Every School has zero or more Students, every Student attends exactly one School, namely the School that the foreign key SchoolId refers to.

因此，如果 School [10] 有 2000 个学生，那么每个 Student 的 SchoolId 值都将等于 10.如果您查询 School [10] 及其学生，您将传递该值 [10] 超过 2000 次.多么浪费处理能力！

So if School [10] has 2000 Students, then every Student will have a value for SchoolId equal to 10. If you query School [10] with its Students, you will be transferring this value [10] more than 2000 times. What a waste of processing power!

在实体框架中，使用Select查询数据，只选择你实际打算使用的值.如果您计划更新包含的数据，请仅使用 Include.

In entity framework, use Select to query data, and only select the values that you actually plan to use. Only use Include if you plan to update the included data.

当然不要使用包含作为选择所有属性"的某种快捷方式！

Certainly don't use Include as some kind of Shortcut for "Select all properties"!

回到你的问题

• 每个属性都有零个或多个 PropertyParties.
• 每个 PropertyParty 都有零个或多个属性.
• 每个属性都有零个或多个 PartyMailingAddress
• 每个 PartyMailingAddress 都有零个或多个 PropertyMailingAddress
• 每个 PropertyMailingAddress 都有一个布尔属性 Status

您想查询所有属性(其中的几个属性)，这些属性的深处至少有一个具有真实状态值的 PropertyMailingAddress.

You want to query (several properties of) all Properties, that have deep inside at least one PropertyMailingAddress with a true Status value.

每当您有一个 Item 序列，其中每个 Item 都有一个 OtherItems 的子序列，并且您想调查所有其他项目，就好像它是一个序列一样，请考虑使用 SelectMany:

Whenever you have a sequence of Items where every Item has a subsequence of OtherItems, and you want to investigate all OtherItems as if it were one sequence, consider using SelectMany:

var propertiesWithTrueStatus = propertyRepository.GetAll()
    .Where(property => property.SelectMany(property => property.PropertyParties)

        // result: one big sequence of all PropertyParties of all properties
        .SelectMany(propertyParty => propertyParty.PartyMailingAddresses)

        // result: one big sequence of all PartyMailingAddresses of all 
        // PropertyParties of all Properties
        .SelectMany(partyMailingAddress => partyMailingAddress.PropertyMailingAddresses)
        .Select(propertyMailingAddress => propertyMailingAddress.Status)

        // result: one big sequence of all Statusses of all PropertyMailingAddresses
        // of all ... of all Properties

        // Keep only the true status values:
        .Where(status => status)

        // keep only the Properties that have at least one true Status value
        .Any())
        // end of Where(...)

所以现在你只有那些在内部深处至少有一个真实状态值的属性.使用 Select 继续查询(或者如果你真的想要:Include)

So now you have only those Properties that deep inside have at least one true Status value. Continue the query with a Select (or if you really want: Include)

    .Select(property => new
    {
        // Select only the properties that you actually plan to use
        Id = property.Id,
        Name = property.Name,
        ...

        PropertyParties = property.PropertyParties.Select(propertyParty => new
        {
            // again only the properties that you plan to use:
            Id = propertyParty.Id,
            ...

            // no need to Select this, you already know the value
            // PropertyId = propertyParty.PropertyId

            PartyMailingAddresses = propertyParty.PartyMailingAddresses
                .Select( partyMailingAddress => new { ... } )
                .ToList(),
        })
        .ToList(),
    });

除了 Select 比 Include 更有效之外，它还让您在需要时可以更自由地偏离数据库表.如果最终结果中不需要所有 PropertyMailingAddresses，只需不要选择它们.如果您只需要 PropertyParties 的总数，请使用 PropertyPartyCount = propertyParties.Count.使用 Select 返回的数据不必与您的数据库表相似.

Apart from that Select is more efficient than Include, it gives you more freedom to deviate from your database tables if needed. If you don't need all PropertyMailingAddresses in your end result, simply don't select them. If you only want the total number of PropertyParties, use PropertyPartyCount = propertyParties.Count. With Select the returned data does not have to be similar to your database tables.

这样做的好处是您可以隐藏数据库中的更改:只需更改 Select，您的所有用户都不会注意到您在内部更改了表.

This has the advantage that you can hide changes in your database: simply change the Select, and all your users won't notice that deep inside you have changed your tables.

这篇关于过滤然后包括向下三个嵌套级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！