我用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 Farm
s 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();
...然后使用生成的匿名对象为任何你想做的事,而不是自己额外的数据添加到农场
实体本身。