在EF $ C C $首先过滤导航性能 [英] Filtering navigation properties in EF Code First
问题描述
我用code首先在EF。比方说，我有两个实体：
I'm using Code First in EF. Let's say I have two entities:
public class Farm
{
....
public virtual ICollection<Fruit> Fruits {get; set;}
}
public class Fruit
{
...
}
我的DbContext是这样的：
My DbContext is something like this:
public class MyDbContext : DbSet
{
....
private DbSet<Farm> FarmSet{get; set;}
public IQueryable<Farm> Farms
{
get
{
return (from farm in FarmSet where farm.owner == myowner select farm);
}
}
}
我这样做，这样每个用户只能看到他的农场，我没有打电话给每个查询到的分贝在哪里。
I do this so that each user can only see his farms, and I don't have to call the Where on each query to the db.
现在，我想从一个农场过滤所有的水果，我想这（在农场类）：
Now, I want to filter all the fruits from one farm, I tried this (in Farm class):
from fruit in Fruits where fruit .... select fruit
而产生的不包括查询的WHERE子句，这是非常重要的，因为我有几十数千行和效率不高加载它们和它们进行过滤时，他们的对象。
but the query generated doesn't include the where clause, which is very important because I have dozens of thousands of rows and is not efficient to load them all and filter them when they're Objects.
我读了延迟加载性能得到填补，他们正在访问的第一次，但他们读的所有数据，没有过滤器可以应用，除非你做这样的事情：
I read that lazy loaded properties get filled the first time they're accessed but they read ALL the data, no filters can be applied UNLESS you do something like this:
from fruits in db.Fruits where fruit .... select fruit
但我不能这样做，因为农场毫不知情的DbContext的（我不认为它应该（？）），而且对我来说，只是失去了使用的导航性能，如果我有工作的全部目的所有的数据，而不是仅仅属于我的农场所述一个
But I can't do that, because Farm has no knowledge of DbContext (I don't think it should(?)) but also to me it just loses the whole purpose of using navigation properties if I have to work with all the data and not just the one that belongs to my Farm.
因此，
- 是我做错什么/作出错误的假设？
- 有没有什么办法可以应用过滤器来获取生成的实际查询导航属性？ （我使用的是大量的数据）
感谢您的阅读！
推荐答案
不幸的是，我觉得你可能会采取任何方法必须涉及的方面，而不仅仅是实体的摆弄。正如你所看到的，你不能过滤导航属性直接，因为它是一个的ICollection＆LT; T＆GT; ，而不是的IQueryable＆LT; T＆GT; ，所以它被一次性全部装载你必须申请任何过滤器的机会了。
Unfortunately, I think any approach you might take would have to involve fiddling with the context, not just the entity. As you've seen, you can't filter a navigation property directly, since it's an ICollection<T> and not an IQueryable<T>, so it gets loaded all at once before you have a chance to apply any filters.
有一件事情你可能做的是建立在你的农场实体持有过滤的水果列表中映射的属性：
One thing you could possibly do is to create an unmapped property in your Farm entity to hold the filtered fruit list:
public class Farm
{
....
public virtual ICollection<Fruit> Fruits { get; set; }
[NotMapped]
public IList<Fruit> FilteredFruits { get; set; }
}
然后，在你的环境/资源库，添加一个方法来加载农场实体，并填充 FilteredFruits 与数据你想要的：
And then, in your context/repository, add a method to load a Farm entity and populate FilteredFruits with the data you want:
public class MyDbContext : DbContext
{
....
public Farm LoadFarmById(int id)
{
Farm farm = this.Farms.Where(f => f.Id == id).Single(); // or whatever
farm.FilteredFruits = this.Entry(farm)
.Collection(f => f.Fruits)
.Query()
.Where(....)
.ToList();
return farm;
}
}
...
var myFarm = myContext.LoadFarmById(1234);
这应该填充 myFarm.FilteredFruits 只用过滤收集，所以你可以使用它，你希望你的实体中的方式。但是，我还没有尝试过这种方法我自己，所以有可能是陷阱，我没有想到的。一个主要的缺点是，它只会与农场工作是你加载使用该方法，而不是你的 MyDbContext执行任何普通的LINQ查询。农场数据集。
That should populate myFarm.FilteredFruits with only the filtered collection, so you could use it the way you want within your entity. However, I haven't ever tried this approach myself, so there may be pitfalls I'm not thinking of. One major downside is that it would only work with Farms you load using that method, and not with any general LINQ queries you perform on the MyDbContext.Farms dataset.
所有这一切说，我认为你要做到这一点可能是，你把过多的业务逻辑到实体类，而实际上它可能属于不同的层更好地迹象的事实。很多的时候，最好是把基本的只是贮数据库记录的内容实体，并留下所有的过滤/处理到资源库或其它地方的业务/显示逻辑的生活。我不知道你的工作是什么样的应用，所以我真的不能提供任何具体的建议，但它的一些思考。
All that said, I think the fact that you're trying to do this might be a sign that you're putting too much business logic into your entity class, when really it might belong better in a different layer. A lot of the time, it's better to treat entities basically as just receptacles for the contents of a database record, and leave all the filtering/processing to the repository or wherever your business/display logic lives. I'm not sure what kind of application you're working on, so I can't really offer any specific advice, but it's something to think about.
一个很常见的方法，如果你决定要搬东西了农场实体使用投影：
A very common approach if you decide to move things out the Farm entity is to use projection:
var results = (from farm in myContext.Farms
where ....
select new {
Farm = farm,
FilteredFruits = myContext.Fruits.Where(f => f.FarmId == farm.Id && ...).ToList()
}).ToList();
...然后使用生成的匿名对象为任何你想做的事，而不是自己额外的数据添加到农场实体本身。
这篇关于在EF $ C C $首先过滤导航性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
