EF：包括where子句 [英] EF: Include with where clause
问题描述
正如标题所暗示我正在寻找一种方式做，其中有子句中组合的包括。
As the title suggest I am looking for a way to do a where clause in combination with an include.
下面是我的情况：
我负责支持一个大的应用充满了code气味。
改变太多code使虫子到处都是，所以我在寻找最安全的解决方案。
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
在目前的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);
}
}
在此code中的语境配置和调用方法所产生的总线实体映射到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).
这code使多次调用DB这是一个不走，所以我觉得这个解决方案<一个href=\"http://blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx\">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）。如果我检查的乘客收集伯爵还抛出异常，但也有对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?
任何暗示欢迎...
格特·阿诺德的回答不工作，因为数据没有即时加载。
但是，当我把它简化并删除它被加载。由于执行sql返回在两种情况下所有的乘客这是真的奇怪。所以把结果时返回到该实体必须有一个问题
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
在很多奋斗格特·阿诺德的答案工作！
随着格特Arnold认为你需要禁用延迟加载和保持它关闭。
这将要求一些额外的改动，因为$ P $光伏开发商appliaction爱延迟加载-_-
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 =＆GT; x.b）。，而不是乘客
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具有此功能的关系修正的这需要设置，可在上下文物化对象之间的所有关联的照顾。这意味着，每个总线现在只有其清醒的乘客被加载。
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 ，关系链接地址不能与许多-to-many关联工作。如果总线 - 乘客是多到很多，你唯一可以做的事情是你自己解决它：
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屋！
