EF:包含 where 子句 [英] EF: Include with where clause
问题描述
正如标题所示，我正在寻找一种将 where 子句与 include 结合使用的方法.
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.
假设我有一个对象 Bus 和一个对象 People(Bus 有一个导航道具 Collection of People).在我的查询中，我需要选择只有醒着的乘客的所有巴士.这是一个简单的虚拟示例
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);
}
}
在此代码之后，上下文被处理，并在调用方法中将生成的总线实体映射到 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，所以我找到了这个解决方案 在 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).如果我检查Passengers 集合，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 都会返回所有乘客.所以把结果放回实体的时候肯定有问题.
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();
编辑2
经过一番努力，Gert Arnold 的答案终于出来了！正如 Gert Arnold 建议的那样，您需要禁用延迟加载并保持关闭.这将要求对应用程序进行一些额外的更改，因为上一个开发人员喜欢延迟加载 -_-
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 -_-
推荐答案
现在 EF Core 5.0 的 过滤包含 方法现在支持过滤包含的实体
Now EF Core 5.0's Filter Include method now supports filtering of the entities included
var busses = _Context.Busses
.Include(b => b.Passengers
.Where(p => p.Awake))
.Where(b => b.IsDriving);
这篇关于EF:包含 where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
