LinQ to query an entity including children with conditions

Question

What is wrong in this query? It's about 2 entities that can be logically deleted by inserting a delete date. So I must be sure I get a single Entity with is collection of SubEntities. All with DateDelete not null.

    return DbContext.Entity
         .AsNoTracking()
         .Include(y => y.SubEntities.Select(sb => sb.DateDelete == null))
         .Single(y => y.Name == entityName && y.DateDelete == null);

On runtime I get an exception

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

I also tried this with same error

    return DbContext.Entity
         .AsNoTracking()
         .Include(y => y.SubEntities.Where(sb => sb.DateDelete == null))
         .Single(y => y.Name == entityName && y.DateDelete == null);

You're using `Select` with something that is effectively a condition... did you mean `Select(sb => sb.DateDelete)`? It's unclear to me what you're trying to do... — Jon Skeet, Nov 24 '16 at 11:38
Are you using the `Include` to try to filter the child entities? That's not how it works I'm afraid. — DavidG, Nov 24 '16 at 11:41
What Am I doing different from http://stackoverflow.com/a/17597480/196526? It seems working for this user. How can I filter child entities then? — Bastien Vandamme, Nov 24 '16 at 11:42
Just use `.Include(y => y.SubEntities)` if you want to include the SubEntities. If you only want to have SubEntities that have DateDelete not equal to null, you can filter for that in a where statement. Currently your mixing up filtering and including. — Chris Schmitz, Nov 24 '16 at 11:44
I understand your point Dr. Coconut. But... the Where clause apply on Entity, not on SubEntity. — Bastien Vandamme, Nov 24 '16 at 11:47
There's plenty of example of how to filter child collections, for example http://stackoverflow.com/questions/16798796/ef-include-with-where-clause — DavidG, Nov 24 '16 at 11:47

score 0 · Accepted Answer · edited May 23 '17 at 12:24

After my own investigation I found it is impossible to filter on .Include() but there are workarounds by using .Select() method or the filter library.

Workaround 1

return DbContext.Entity
     .AsNoTracking()
     .Where(e => e.DateDelete == null)
     .Select(e => new
         {
             e.Id,
             e.Property,
             e.DateDelete,
             SubEntities = e.SubEntities.Where(se => se.DateDelete == null)
         })
     .ToList();

Workaround 2

The solution given by Gert Arnold here with EntityFramework.DynamicFilters. This workaround has some limitation. Read the documentation.

LinQ to query an entity including children with conditions

1 Answers1

Workaround 1

Workaround 2