Filtering navigation properties in EF Code First

Question

I am using Code First in EF. Let's say I have two objects:

public class Farm { .... public virtual ICollection<Fruit> Fruits {get; set;} } public class Fruit { ... } 

My DbContext looks 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 see only his farms, and I do not need to call "Where" on every request on db.

Now I want to filter out all the fruits from one farm, I tried this (in the Farm class):

 from fruit in Fruits where fruit .... select fruit 

but the generated query does not include the where clause, which is very important because I have tens of thousands of lines and it is inefficient to load them all and filter when they are objects.

I read that the first time they are accessed, the full loaded properties are filled, but they read ALL the data, no filters can be applied IF you do something like this:

 from fruits in db.Fruits where fruit .... select fruit 

But I can’t do this because Fermat does not know about DbContext (I don’t think it should (?)), But for me it just loses the purpose of using navigation properties if I need to work with all the data, and not just what belongs to my farm.

So,

• Am I doing something wrong / making wrong assumptions?
• Is there a way to apply a filter to the navigation property that is generated for a real request? (I work with a lot of data)

Thanks for reading!

Answer 1

Unfortunately, I think that any approach that you could take should include a struggle with the context, and not just with the essence. As you saw, you cannot filter the navigation property directly, since it is ICollection<T> , not IQueryable<T> , so it loads immediately before you can apply any filters.

One thing you could do is create an uninsulated property in your Farm object to store a list of filtered fruits:

 public class Farm { .... public virtual ICollection<Fruit> Fruits { get; set; } [NotMapped] public IList<Fruit> FilteredFruits { get; set; } } 

And then in your context / repository add a method to load the Farm object and fill FilteredFruits necessary data:

 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); 

This should populate myFarm.FilteredFruits just a filtered collection so that you can use it the way you want in your entity. However, I have never tried this approach myself, so there may be pitfalls that I don’t think about. One of the main drawbacks is that it will only work with Farm , which you load using this method, and not with any general LINQ queries that you execute in the MyDbContext.Farms .

All that said, I think that the fact that you are trying to do this may be a sign that you are putting too much business logic in your entity class when it really can be better in another layer. In most cases, it is better to process objects mainly as capacities for the contents of the database record and leave all filtering / processing in the repository or wherever your business logic lives. I'm not sure which application you are running, so I cannot offer any specific advice, but there is something to think about.

A very common approach if you decide to move things from a Farm object 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(); 

... and then use the created anonymous objects for everything you want to do, instead of trying to add additional data to the Farm objects themselves.

Answer 2

Lazy loading does not support filtering; use filtered explicit loading :

 Farm farm = dbContext.Farms.Where(farm => farm.Owner == someOwner).Single(); dbContext.Entry(farm).Collection(farm => farm.Fruits).Query() .Where(fruit => fruit.IsRipe).Load(); 

An explicit download approach requires two round trips to the database, one for the wizard and one for the details. If it’s important to stick to a single query, use the forecast instead:

 Farm farm = ( from farm in dbContext.Farms where farm.Owner == someOwner select new { Farm = farm, Fruit = dbContext.Fruit.Where(fruit => fruit.IsRipe) // Causes Farm.Fruit to be eager loaded }).Single().Farm; 

EF always associates navigation properties with loaded objects. This means that farm.Fruit will contain the same filtered collections as the Fruit property in the anonymous type. (Just make sure that you do not load any Fruit objects that need to be filtered into the context, as described in Use projections and the repository to fake filtered impatient downloads .)

Answer 3

I just decided that I would add another solution, spending some time trying to add DDD principles for coding the first models. After searching for some time, I found a solution like the one below that works for me.

 public class FruitFarmContext : DbContext { public DbSet<Farm> Farms { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Farm>().HasMany(Farm.FruitsExpression).WithMany(); } } public class Farm { public int Id { get; set; } protected virtual ICollection<Fruit> Fruits { get; set; } public static Expression<Func<Farm, ICollection<Fruit>>> FruitsExpression = x => x.Fruits; public IEnumerable<Fruit> FilteredFruits { get { //Apply any filter you want here on the fruits collection return Fruits.Where(x => true); } } } public class Fruit { public int Id { get; set; } } 

The idea is that farm fruit picking is not directly available, but is exposed through a property that pre-filters it. The tradeoff here is the static expression, which is required in order to be able to address the fruit picking when setting up the match. I started using this approach for a number of projects where I want to control access to child collections of objects.