过滤包含在 EF Core 中 [英] Filtering on Include in EF Core

问题描述

我正在尝试过滤初始查询.我在模型上嵌套了包含叶子.我正在尝试根据其中一个包含的属性进行过滤.例如:

I'm trying to filter on the initial query. I have nested include leafs off a model. I'm trying to filter based on a property on one of the includes. For example:

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
            .ThenInclude(post => post.Author)
        .ToList();
}

我还能怎么说.Where(w => w.post.Author == "me")?

推荐答案

Entity Framework core 5 是 支持过滤Include.

Entity Framework core 5 is the first EF version to support filtered Include.

支持的操作:

• 哪里
• OrderBy(Descending)/ThenBy(Descending)
• 跳过
• 拿

一些使用示例(来自原始功能请求和github 提交):

Some usage examples (from the original feature request and the github commmit) :

每个导航只允许使用一个过滤器，因此对于需要多次包含相同导航的情况(例如，在同一导航上包含多个 ThenInclude)仅应用一次过滤器，或为该导航应用完全相同的过滤器.

Only one filter allowed per navigation, so for cases where the same navigation needs to be included multiple times (e.g. multiple ThenInclude on the same navigation) apply the filter only once, or apply exactly the same filter for that navigation.

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders).ThenInclude(o => o.Customer)

或

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.Customer)

另一个重要说明:

使用新过滤器操作包含的集合被视为已加载.

Collections included using new filter operations are considered to be loaded.

这意味着如果启用了延迟加载，则从上一个示例中解决一个客户的 Orders 集合不会触发整个 Orders 集合的重新加载.

That means that if lazy loading is enabled, addressing one customer's Orders collection from the last example won't trigger a reload of the entire Orders collection.

此外，同一上下文中的两个后续过滤的 Include 将累积结果.例如...

Also, two subsequent filtered Includes in the same context will accumulate the results. For example...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...接着是...

context.Customers.Include(c => c.Orders.Where(o => o.IsDeleted))

...将导致 customers 与包含所有订单的 Orders 集合.

...will result in customers with Orders collections containing all orders.

如果其他 Order 被加载到相同的上下文中，由于关系修复customers.Orders 集合中>.这是不可避免的，因为 EF 的变更跟踪器是如何工作的.

If other Orders are loaded into the same context, more of them may get added to a customers.Orders collection because of relationship fixup. This is inevitable because of how EF's change tracker works.

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...接着是...

context.Orders.Where(o => o.IsDeleted).Load();

...将再次导致 customers 与包含所有订单的 Orders 集合.

...will again result in customers with Orders collections containing all orders.

过滤器表达式应包含可用作集合的独立谓词的谓词.一个例子将说明这一点.假设我们想要包含由 Customer 的某些属性过滤的订单:

The filter expression should contain predicates that can be used as a stand-alone predicate for the collection. An example will make this clear. Suppose we want to include orders filtered by some property of Customer:

context.Customers.Include(c => c.Orders.Where(o => o.Classification == c.Classification))

它可以编译，但它会抛出一个非常技术性的运行时异常，基本上是在告诉 o.Classification == c.Classification 无法翻译，因为 c.Classification> 找不到.必须使用从 Order 到 Customer 的反向引用重写查询:

It compiles, but it'll throw a very technical runtime exception, basically telling that o.Classification == c.Classification can't be translated because c.Classification can't be found. The query has to be rewritten using a back-reference from Order to Customer:

context.Customers.Include(c => c.Orders.Where(o => o.Classification == o.Customer.Classification))

谓词o =>o.Classification == o.Customer.Classification) 是独立的"；从某种意义上说，它可用于独立过滤 Orders:

The predicate o => o.Classification == o.Customer.Classification) is "stand alone" in the sense that it can be used to filter Orders independently:

context.Orders.Where(o => o.Classification == o.Customer.Classification) // No one would try 'c.Classification' here

此限制可能会在比当前稳定版本(EF 核心 5.0.7)更高的 EF 版本中更改.

This restriction may change in later EF versions than the current stable version (EF core 5.0.7).

由于 Where 是 IEnumerable 上的扩展方法，很明显只能过滤集合.无法过滤参考导航属性.如果我们想获取订单并且只在客户处于活动状态时填充他们的 Customer 属性，我们就不能使用 Include:

Since Where is an extension method on IEnumerable it's clear that only collections can be filtered. It's not possible to filter reference navigation properties. If we want to get orders and only populate their Customer property when the customer is active, we can't use Include:

context.Orders.Include(c => c.Customer.Where( ... // obviously doesn't compile

过滤的包含与过滤查询

Filtered Include 在它如何影响整个查询的过滤方面引起了一些混乱.经验法则是:不会.

Filtered Include vs filtering the query

Filtered Include has given rise to some confusion on how it affects filtering a query as a whole. The rule of the thumb is: it doesn't.

声明...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...从上下文中返回所有客户，而不仅仅是订单未删除的客户.Include 中的过滤器不会影响主查询返回的项目数.

...returns all customers from the context, not only the ones with undeleted orders. The filter in the Include doesn't affect the number of items returned by the main query.

另一方面，声明...

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders)

...只返回至少有一个未删除的订单，但在 Orders 集合中所有的订单的客户.主查询的过滤器不会影响 Include 返回的每个客户的订单.

...only returns customers having at least one undeleted order, but having all of their orders in the Orders collections. The filter on the main query doesn't affect the orders per customer returned by Include.

要让客户获得未删除的订单并且只加载他们未删除的订单，两个过滤器都是必需的:

To get customers with undeleted orders and only loading their undeleted orders, both filters are required:

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders.Where(o => !o.IsDeleted))

过滤的包含和投影

另一个混淆领域是过滤的 Include 和投影 (select new { ... }) 是如何相关的.简单的规则是:投影忽略 Include s，过滤与否.查询如...

Filtered Include and projections

Another area of confusion is how filtered Include and projections (select new { ... }) are related. The simple rule is: projections ignore Includes, filtered or not. A query like...

context.Customers
    .Include(c => c.Orders)
    .Select(c => new { c.Name, c.RegistrationDate })

...将在没有连接到 Orders 的情况下生成 SQL.至于EF，就跟...

...will generate SQL without a join to Orders. As for EF, it's the same as...

context.Customers
    .Select(c => new { c.Name, c.RegistrationDate })

当过滤Include 时会让人感到困惑，但Orders 也用于投影:

It gets confusing when the Include is filtered, but Orders are also used in the projection:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        c.Name, 
        c.RegistrationDate,
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

人们可能期望 OrderDates 只包含来自未删除订单的日期，但它们包含来自所有 Orders 的日期.同样，投影完全忽略了 Include.投影和 Include 是不同的世界.

One might expect that OrderDates only contains dates from undeleted orders, but they contain the dates from all Orders. Again, the projection completely ignores the Include. Projection and Include are separate worlds.

这个查询有趣地展示了他们过着自己的生活是多么严格:

How strictly they lead their own lives is amusingly demonstrated by this query:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        Customer = c, 
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

现在暂停片刻并预测结果......

Now pause for a moment and predict the outcome...

不那么简单的规则是:投影不要总是忽略Include.当投影中存在可以应用 Include 的实体时，它会被应用.这意味着投影中的 Customer 包含其未删除的 Orders，而 OrderDates 仍包含所有日期.你做对了吗?

The not so simple rule is: projections don't always ignore Include. When there is an entity in the projection to which the Include can be applied, it is applied. That means that Customer in the projection contains its undeleted Orders, whereas OrderDates still contains all dates. Did you get it right?

这篇关于过滤包含在 EF Core 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！