25

Here is the problem: I need to return a collection of objects with filtered nested collections. E.g: there is a store with orders and I need to return a collection with stores that includes nested collections with orders but without orders from customers that are marked as deleted.

Here is what I try to do. But still no luck. Any suggestions are appreciated :)

public List<StoreEntity> GetStores(Func<Store, bool> storeFilter, Predicate<OrderEntity> orderFileter)
{
    IQueryable<StoreEntity> storeEntities = Context.Stores
        .Include(o => o.Order)
        .Include(cu => cu.Orders.Select(c => c.Customer))
        .Where(storeFilter)
        //.Where(rcu=>rcu.Orders.Select(cu=>cu.Customer.Deleted==false)) //just test this doesn't work
        .AsQueryable();

    List<StoreEntity> storeEntities = storeEntities.ToList();

    //storeEntities.ForEach(s => s.Orders.ToList().RemoveAll(c=>c.Customer.Deleted==true)); // doesn't work

    foreach (StoreEntity storeEntity in storeEntities)
    {
        storeEntity.Orders.ToList().RemoveAll(r=>r.Customer.Deleted==true);
    }

    return storeEntities;
}

The problem is that the filter is not applied. Customers that have deleted flag set as true stay in the collection.

Melissa
  • 463
  • 5
  • 18
Naz
  • 5,104
  • 8
  • 39
  • 63
  • And what is the problem? Doesn't it compile? Does it throw a runtime exception? Does it run but return the wrong data? – Daniel Hilgarth Aug 16 '11 at 14:19
  • 2
    I ended up using this nuget package: `Z.EntityFramework.Plus.QueryIncludeFilter.EF6` Documentation here: https://github.com/zzzprojects/EntityFramework-Plus/wiki/EF-Query-IncludeFilter-%7C-Entity-Framework-Include-Related-Entities-using-Where-Filter – Bron Davies Dec 16 '16 at 15:47
  • @BronDavies This Nuget package should be the accepted answer, not a comment! – Avrohom Yisroel Jan 16 '18 at 14:58

3 Answers3

35

You can't do that directly in a "neat" way, but you have a few options.
First of all, you can explicitly load the child collection after you've fetched the stores. See the Applying filters when explicitly loading related entities section.

If you don't want to make extra trips to the database, you will have to construct your own query and project the parent collection and the filtered child collections onto another object manually. See the following questions for examples:
Linq To Entities - how to filter on child entities
LINQ Query - how sort and filter on eager fetch

Edit

By the way, your first .Where(rcu=>rcu.Orders.Select(cu=>cu.Customer.Deleted==false)) attempt doesn't work since this way you are applying a filter to your parent collection (stores) rather than the nested collection (e.g. all the stores that don't have deleted customers).
Logically, the code filtering the nested collection should be placed in the Include method. Currently, Include only supports a Select statement, but personally I think it's time for the EF team to implement something like:

.Include(cu => cu.Orders.Select(c => c.Customers.Where(cust => !cust.IsDeleted)));
Community
  • 1
  • 1
Yakimych
  • 17,612
  • 7
  • 52
  • 69
  • 1
    Yeah, I agree. (I know that thing doesn't work, but still wanted to try it out, who knows maybe that feature was hidden and I would get pleasantly surprised), and yes, this kind of Include would be nice to have. I decided to go with custom projection... don't really like that method because I had to assign a lot of dumb values like ( ID= r.ID, Name= r.Name ... and so on). But at least it works :) Thx – Naz Aug 16 '11 at 19:36
  • 7
    I agree the EF team should implement filtered Include. Vote for it [here](https://entityframework.codeplex.com/workitem/47)! – Chris Sep 27 '13 at 07:33
  • What if I have a generic method called `GetWithInclude()` `public IQueryable GetWithInclude(params Expression>[] includeProperties) { return includeProperties.Aggregate>, IQueryable>(DbSet, (current, includeProperty) => current.Include(includeProperty)); }` Yet, I don't have a collection and I need filter from a projection. In other words, I have a property that needs to equal something. – Vyache Jan 31 '18 at 23:16
3

The problem with the code you currently have is this line:

storeEntity.Orders.ToList().RemoveAll(r=>r.Customer.Deleted==true);

storeEntity.Orders.ToList() returns a new List<OrderEntity> with the contents of storeEntity.Orders. From this new list, you remove all deleted customers. However, this list isn't used anywhere after that.

However, even if it would do what you want to, this would also remove those customers from the database, because your StoreEntity objects are still connected to the data context!

You really want to use a filter as you first tried in the commented Where. Please see Yakimych's answer for help on that.

Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
-7

Old topic, but I ran into a rather similar problem. I've searched a lot, and the MSDN link provided by Yakimych finally hinted me to a solution : explicitely disable lazy loading, and then do queries to filter the navigation properties. The result will then be "attached" to the main query, which would give something like that :

Context.Configuration.LazyLoadingEnabled = false;

var filteredOrders = Context.Orders.Where(x => x.Customer.Delete == false);

IQueryable<StoreEntity> storeEntities = Context.Stores
.Include(o => o.Order)
.Include(cu => cu.Orders.Select(c => c.Customer))
.Where(storeFilter)
.AsQueryable();
darkchico
  • 647
  • 7
  • 7