C# EF：包含 where 子句

EF: Include with where clause

提问by Beejee

As the title suggest I am looking for a way to do a where clause in combination with an include.

正如标题所暗示的那样，我正在寻找一种将 where 子句与 include 结合使用的方法。

Here is my situations: I am responsible for the support of a large application full of code smells. Changing too much code causes bugs everywhere so I am looking for the safest solution.

这是我的情况：我负责支持一个充满代码异味的大型应用程序。更改太多代码会导致到处都是错误，所以我正在寻找最安全的解决方案。

Let's say I have an object Bus and an object People(Bus has a navigation prop Collection of People). In my Query I need to select all the Busses with only the Passengers that are awake. This is a simplistic dummy example

假设我有一个对象 Bus 和一个对象 People（Bus 有一个导航道具 Collection of People）。在我的查询中，我需要选择只有醒着的乘客的所有巴士。这是一个简单的虚拟示例

In the current code:

在当前代码中：

var busses = Context.Busses.Where(b=>b.IsDriving == true);
foreach(var bus in busses)
{
   var passengers = Context.People.Where(p=>p.BusId == bus.Id && p.Awake == true);
   foreach(var person in passengers)
   {
       bus.Passengers.Add(person);
   }
}

After this code the Context is disposed and in the calling method the resulting Bus entities are Mapped to a DTO class (100% copy of Entity).

在此代码之后，上下文被处理，并在调用方法中将生成的总线实体映射到 DTO 类（实体的 100% 副本）。

This code causes multiple calls to DB which is a No-Go, so I found this solution ON MSDN Blogs

这段代码导致多次调用 DB，这是一个 No-Go，所以我在MSDN 博客上找到了这个解决方案

This worked great when debugging the result but when the entities are mapped to the DTO (Using AutoMapper) I get an exception that the Context/Connection has been closed and that the object can't be loaded. (Context is always closed can't change this :( )

这在调试结果时效果很好，但是当实体映射到 DTO（使用 AutoMapper）时，我得到一个异常，即上下文/连接已关闭并且无法加载对象。（上下文总是关闭不能改变这一点:(）

So I need to make sure that the Selected Passengers are already loaded (IsLoaded on navigation property is also False). If I inspect the Passengers collection The Count also throws the Exception but there is also a collection on the Collection of Passegers called “wrapped related entities” which contain my filtered objects.

所以我需要确保选定的乘客已经加载（导航属性上的 IsLoaded 也是 False）。如果我检查Passengers 集合，Count 也会抛出异常，但Passegers 集合上还有一个集合，称为“包装的相关实体”，其中包含我过滤的对象。

Is there a way to load these wrapped related entities into the whole collection? (I can't change the automapper mapping config because this is used in the whole application).

有没有办法将这些包装的相关实体加载到整个集合中？（我无法更改 automapper 映射配置，因为它在整个应用程序中使用）。

Is there another way to Get the Active Passengers?

有没有另一种方法来获得活跃的乘客？

Any hint is welcome...

欢迎任何提示...

Edit

编辑

Answer of Gert Arnold doesn't work because the data isn't loaded eagerly. But when I simplify it and delete the where it is loaded. This is realy strange since the execute sql returns all the passengers in both cases. So there must be a problem when putting the results back into the entity.

Gert Arnold 的回答不起作用，因为数据没有急切加载。但是当我简化它并删除它的加载位置时。这真的很奇怪，因为在这两种情况下，执行 sql 都会返回所有乘客。所以把结果放回实体的时候肯定有问题。

Context.Configuration.LazyLoadingEnabled = false;
var buses = Context.Busses.Where(b => b.IsDriving)
        .Select(b => new 
                     { 
                         b,
                         Passengers = b.Passengers
                     })
        .ToList()
        .Select(x => x.b)
        .ToList();

Edit2

编辑2

After a lot of struggle the answer of Gert Arnold work! As Gert Arnold suggested you need to disable Lazy Loading and Keep it OFF. This will ask for some extra changes to the appliaction since the prev developer loved Lazy Loading -_-

经过大量的斗争，Gert Arnold 的工作得到了答案！正如 Gert Arnold 建议的那样，您需要禁用延迟加载并保持关闭。这将要求对应用程序进行一些额外的更改，因为上一个开发人员喜欢延迟加载-_-

采纳答案by Gert Arnold

Breaking news: this feature has now been added to Entity Framework core.

突发新闻：此功能现已添加到 Entity Framework 核心。

You can query the required objects by

您可以通过以下方式查询所需的对象

Context.Configuration.LazyLoadingEnabled = false;
// Or: Context.Configuration.ProxyCreationEnabled = false;
var buses = Context.Busses.Where(b => b.IsDriving)
            .Select(b => new 
                         { 
                             b,
                             Passengers = b.Passengers
                                           .Where(p => p.Awake)
                         })
            .AsEnumerable()
            .Select(x => x.b)
            .ToList();

What happens here is that you first fetch the driving buses and awake passengers from the database. Then, AsEnumerable()switches from LINQ to Entities to LINQ to objects, which means that the buses and passengers will be materialized and then processed in memory. This is important because without it EF will only materialize the final projection, Select(x => x.b), not the passengers.

这里发生的事情是您首先从数据库中获取正在行驶的公交车和醒着的乘客。然后，AsEnumerable()从 LINQ to Entities 切换到 LINQ to objects，这意味着公交车和乘客将被物化，然后在内存中进行处理。这很重要，因为没有它，EF 只会实现最终投影Select(x => x.b)，而不是乘客。

Now EF has this feature relationship fixupthat takes care of setting all associations between objects that are materialized in the context. This means that for each Busnow only its awake passengers are loaded.

现在 EF 具有此功能关系修复，负责设置上下文中具体化的对象之间的所有关联。这意味着对于每个Bus现在只有清醒的乘客被装载。

When you get the collection of buses by ToListyou have the buses with the passengers you want and you can map them with AutoMapper.

当您获得公交车的集合时，ToList您就拥有了载有您想要的乘客的公交车，您可以使用 AutoMapper 将它们映射到地图上。

This only works when lazy loading is disabled. Otherwise EF will lazy load allpassengers for each bus when the passengers are accessed during the conversion to DTOs.

这仅在禁用延迟加载时有效。否则，在转换为 DTO 期间访问乘客时，EF 将延迟加载每辆公共汽车的所有乘客。

There are two ways to disable lazy loading. Disabling LazyLoadingEnabledwill re-activate lazy loading when it is enabled again. Disabling ProxyCreationEnabledwill create entities that aren't capable of lazy loading themselves, so they won't start lazy loading after ProxyCreationEnabledis enabled again. This may be the best choice when the context lives longer than just this single query.

有两种方法可以禁用延迟加载。禁用LazyLoadingEnabled将在再次启用时重新激活延迟加载。禁用ProxyCreationEnabled将创建不能自己延迟加载的实体，因此它们在ProxyCreationEnabled再次启用后不会开始延迟加载。当上下文的生命周期比单个查询更长时，这可能是最佳选择。

But... many-to-many

但是……多对多

As said, this work-around relies on relationship fixup. However, as explained hereby Slauma, relationship fixup doesn't work with many-to-many associations. If Bus-Passengeris many-to-many, the only thing you can do is fix it yourself:

如前所述，这种变通方法依赖于关系修复。但是，正如Slauma在此处解释的那样，关系修复不适用于多对多关联。如果Bus-Passenger是多对多的，你唯一能做的就是自己修复它：

Context.Configuration.LazyLoadingEnabled = false;
// Or: Context.Configuration.ProxyCreationEnabled = false;
var bTemp = Context.Busses.Where(b => b.IsDriving)
            .Select(b => new 
                         { 
                             b,
                             Passengers = b.Passengers
                                           .Where(p => p.Awake)
                         })
            .ToList();
foreach(x in bTemp)
{
    x.b.Pasengers = x.Passengers;
}
var busses = bTemp.Select(x => x.b).ToList();

...and the whole thing becomes even less appealing.

...整个事情变得更不吸引人了。

Third-party tools

第三方工具

There is a library, EntityFramework.DynamicFiltersthat makes this a lot easier. It allows you to define global filters for entities, that will subsequently be applied any time the entity is queried. In your case this could look like:

有一个库EntityFramework.DynamicFilters使这更容易。它允许您为实体定义全局过滤器，随后在查询实体时将应用该过滤器。在您的情况下，这可能如下所示：

modelBuilder.Filter("Awake", (Person p) => p.Awake, true);

Now if you do...

现在如果你...

Context.Busses.Where(b => b.IsDriving)
       .Include(b => b.People)

...you'll see that the filter is applied to the included collection.

...您将看到过滤器已应用于包含的集合。

You can also enable/disable filters, so you have control over when they are applied. I think this is a very neat library.

您还可以启用/禁用过滤器，因此您可以控制何时应用它们。我认为这是一个非常整洁的图书馆。

There is a similar library from the maker of AutoMapper: EntityFramework.Filters

AutoMapper 的制造商有一个类似的库：EntityFramework.Filters

Entity Framework core

实体框架核心

Since version 2.0.0, EF-core has global query filters. Although this is a great addition to its features, so far the limitation is that a filter can't contain references to navigation properties, only to the root entity of a query. Hopefully in later version these filters will attain wider usage.

从 2.0.0 版本开始，EF-core 具有全局查询过滤器。尽管这是对其功能的一个很好的补充，但到目前为止的限制是过滤器不能包含对导航属性的引用，只能包含对查询的根实体的引用。希望在以后的版本中这些过滤器将获得更广泛的使用。

Filtered includes is a long-standing feature request. The EF-core issue can be found here.

过滤包含是一个长期存在的功能请求。可以在此处找到 EF 核心问题。

回答by Jonathan Magnan

Disclaimer: I'm the owner of the project Entity Framework Plus

免责声明：我是Entity Framework Plus项目的所有者

EF+ Query IncludeFilter feature allows filtering related entities.

EF+ Query IncludeFilter 功能允许过滤相关实体。

var buses = Context.Busses
                   .Where(b => b.IsDriving)
                   .IncludeFilter(x => x.Passengers.Where(p => p.Awake))
                   .ToList();

Wiki: EF+ Query IncludeFilter

Wiki：EF+ 查询 IncludeFilter

回答by Bjarke Handsdal

For any one still curious about this. there builtin functionality for doing this in EF Core. using .Any inside of a where clause so the code would like similar to something like this

对于仍然对此感到好奇的任何人。在 EF Core 中有用于执行此操作的内置功能。在 where 子句中使用 .Any ，因此代码会类似于这样的内容

<!-- language: language: c# -->

_ctx.Parent
    .Include(t => t.Children)
    .Where(t => t.Children.Any(t => /* Expression here */))