EF：包含where子句 [英] EF: Include with where clause
问题描述
正如标题所示，我正在寻找一种与include相结合的where子句的方法。
As the title suggest I am looking for a way to do a where clause in combination with an 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
在当前代码中：
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);
}
}
在这段代码之后，Context被处理并在调用方法将生成的总线实体映射到DTO类（实体的100％副本）。
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).
此代码导致多次调用DB，这是一个No-Go，所以我发现此解决方案 ON MSDN博客
This code causes multiple calls to DB which is a No-Go, so I found this solution ON MSDN Blogs
在调试结果时，当实体映射到DTO（使用AutoMapper）时，这很有效，我得到了上下文/连接已关闭，并且无法加载对象。 （上下文总是关闭不能改变这个:(）
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 :( )
所以我需要确保已选择的乘客已经加载（导航属性IsLoaded也是False）如果我检查乘客集合，Count也会抛出异常，但是还收集了一些Passegers集合，称为包装相关实体，其中包含我的过滤对象。
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.
有没有办法将这些包装的相关实体加载到整个集合？
（我不能更改automapper映射配置，因为这在整个应用程序中使用）。
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).
有另一种方式来获得有效的乘客吗？
Is there another way to Get the Active Passengers?
欢迎任何提示...
Gert Arnold的答案不起作用，因为数据没有加载加载
但是当我简化它并删除它的位置这是真的很奇怪，因为执行sql返回所有的乘客在这两种情况下，所以必须有一个pr将结果重新放入实体时的oblem。
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.
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
经过很多的努力格尔阿诺德的答案工作！
由于Gert Arnold建议您需要禁用Lazy Loading并保持关闭。
这将要求对应用程序进行一些额外的更改，因为prev开发人员喜欢Lazy Loading -_-
Edit2
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 -_-
推荐答案
你可以通过
Context.Configuration.LazyLoadingEnabled = 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();
这里发生的一件事是你先从驾驶巴士上取下数据库中的乘客。然后， AsEnumerable（）从LINQ到实体切换到LINQ到对象，这意味着公共汽车和乘客将被实现，然后在内存中进行处理。这很重要，因为没有它，EF只会实现最终投影，选择（x => xb）而不是乘客。
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.
现在，EF具有这个功能关系fixup ，它负责设置在上下文中实现的对象之间的所有关联。这意味着对于每个巴士，现在只有其清醒乘客才能加载。
Now EF has this feature relationship fixup that takes care of setting all associations between objects that are materialized in the context. This means that for each Bus now only its awake passengers are loaded.
当您收集公共汽车时 ToList 你有你想要的乘客的巴士，你可以用AutoMapper来映射他们。
When you get the collection of buses by ToList you have the buses with the passengers you want and you can map them with AutoMapper.
延迟加载被禁用。否则EF将在转换为DTO期间访问乘客时，为每辆公共汽车懒惰加载所有乘客。
This only works when lazy loading is disabled. Otherwise EF will lazy load all passengers for each bus when the passengers are accessed during the conversion to DTOs.
但是...多对多
如上所述，这种解决方法依赖于关系修复。但是，如 此处所述Slauma ，关系修复不适用于多对多关联。如果总线 - 乘客是多对多，唯一可以做的就是自己修复： p>
As said, this work-around relies on relationship fixup. However, as explained here by Slauma, relationship fixup doesn't work with many-to-many associations. If Bus-Passenger is many-to-many, the only thing you can do is fix it yourself:
Context.Configuration.LazyLoadingEnabled = 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.
稍后添加：
有一个库， EntityFramework.DynamicFilters ，这使得这更容易。它允许您为实体定义全局过滤器，随后在实体被查询时随后应用。在你的情况下，这可能看起来像：
Later addition: There is a library, EntityFramework.DynamicFilters that 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:
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.
这篇关于EF：包含where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
