Support filtered Include

[0xdeafcafe]

We keep this issue to track specifying filters inline when you specify eager loading in a query. However there are many scenarios that can be satisfied with global query filters:

Please learn about global query filters

We are seeing that a large portion of the scenarios customers want this feature for can already be better addressed using global query filters. Please try to understand that feature before you add your vote to this issue.

We are keeping this issue open only to represent the ability to specify filters on Include on a per-query basis, which we understand can be convenient on some cases.

Original issue:

Doing a .Where() inside a .Include() is allowed (by the syntax, it fails in execution). However, if you then follow that up with a .ThenInclude(), like so:

.Include(t => t.Ratings.Where(r => !r.IsDeleted))
.ThenInclude(r => r.User)

You get the following error:

'IEnumerable' does not contain a definition for 'User' and no extension method 'User' accepting a first argument of type 'IEnumerable' could be found (are you missing a using directive or an assembly reference?)

Is this something you're aware of and going to allow in a future version, or am I just doing something wrong and It's already supported?

[popcatalin81]

Historically this was not supported by EF, However this would be a nice feature to have. We had to implement something similar ourselves in the our code base using Linq tree rewriting. It hope the EF team considers this.

[0xdeafcafe]

Yeah. I noticed discussion of this in the .Include() design meeting docs. It looked as if it was road-mapped and going to be implemented. Although that could all just be a dreadful fantasy and I'm completely wrong.

[rowanmiller]

Moving to back log as a new feature (we do want to support filtered include but it is a lower priority than some other features that were available in EF6).

[rowanmiller]

Opened new issue for better exception message #1883

[mikes-gh]

Don't underestimate the value of filtered include. It was a top request in ef 6.
I came across this requirement on my first ef 7 project.
I'd take that over lazy loading any day.

[rendmath]

Any non-trivial development using the Entity Framework will encounter this limitation.

It also has consequences on other technologies likes the OData protocol, because there is really no point to adding filtered $expands to the protocol if EF does not support them.

For those who are not (yet) familiar with the internals of EF Core, could you point us to the parts of the Entity Framework that would be impacted in order support this ?

[joshmouch]

I too would love this feature.

[gdoron]

@rowanmiller so what is the current way of using left join with a where clause \ on clause?
I currently query for the entire relationship and filter on the client.
Is there a better way of doing it?

var story = await _context.Stories
    .Include(x => x.Paragraphs)
    .Include(x => x.User)
    .Include(x => x.Tips)
    .Include(x => x.Images)
    .Include(x => x.Comments)
    .ThenInclude(x => x.User)
    .SingleOrDefaultAsync(x => x.Id == storyId);
if (story == null)
    return null;

story.Comments = story.Comments.Where(x => !x.IsDeleted).ToList();
story.Images = story.Images.Where(x => !x.IsDeleted).ToList();
story.Paragraphs = story.Paragraphs.Where(x => !x.IsDeleted).ToList();
story.Tips = story.Tips.Where(x => !x.IsDeleted).ToList();
return story;

It's a mess... and bad performance-wise.

[lucacestola]

Will Filtered Loading be supported at configuration level, in order to have one to many navigation property filtered upstream? Can this feature be someway useful to avoid bad configurations with circular reference like this?

[gdoron]

@lucacestola I'm not sure why you think it's related to conditional querying of a navigation property.

[lucacestola]

@gdoron because it could be also applyed to one to many relationships and would be implicit.

Actually, with EF 6, I was not yet able to find a good solution for the relation type like the one at the posted link, without using a circular FK and, at the meantime, have the possibility to use an expression like this: parent.MainChild.ChildProperty.

I know that this kind of relations depends on a very custom logic so there is no simple way to address such a need, and I was hoping that, configuring the way relationships are loaded could almost partially address the issue.

[Bartmax]

I just have the exactly same need as @gdoron (and not surprising, with IsDeleted fields as well)
is there any way to left join w/ where at db level?

[armartinez]

I'm also in the @gdoron scenario, but I return a collection instead of a single record so I'm doing a foreach on the collection and something like
story.Comments = story.Comments.Where(x => !x.IsDeleted).ToList();
on each record.
I've tried this http://stackoverflow.com/questions/4720573/linq-to-entities-how-to-filter-on-child-entities/4724297#4724297 but it doesn't seem to work on EF Core. Is there a better way to do this?

[arielcloud]

You can query stories without including comments, then query separately comments and Explicit loading them: https://docs.efproject.net/en/latest/querying/related-data.html

[armartinez]

Looks interesting, but I need to filter the first query to only return a record if there are any results in the specific navigation property, so I need to have the include or change the result of the first query when the explicit loading doesn't return anything which I think it's worse.

[atrauzzi]

Yikes, don't know why this is sitting idle in a backlog. Should be considered for any upcoming version as per what the comments from @mikes-gh and @rendmath imply. Easy to overlook this one.

[gdoron]

@atrauzzi well, this feature was and still is idle for years in EF, so I'm afraid ... 😢
Not sure why it's not prioritized.

[HappyNomad]

Yes, this feature request has been around a while. I found it posted on UserVoice since 2010. It's crucial for allowing me to properly load my complex data model, so I hope it's soon given higher priority.

[gdoron]

@rowanmiller @divega Can you please share with us when if ever this is going to be implemented?
We designed our data structure in a way that is best practice regarding DB design, but heavily rely on this feature.

Consider this simplified scheme:

public class Post
{
    public int Id { get; set; }
    public string Text { get; set; }
    public List<PostImage> Images { get; set; }
}

public class PostImage
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public Post Post { get; set; }
    public int PostId { get; set; }
    public string Url { get; set; }
    public string CdnUrl { get; set; }
    public ImageType Type { get; set; }
}

public enum ImageType
{
    Normal = 0,
    Primary = 1,
    Header = 2,
    Profile = 3
}

Now lets say I want to query 10 posts for my blog homepage with their single Primary image.
Currently, there is no way of doing it.
I will have to query for 10 posts with ALL of their images (even the deleted ones!) and only on the client filter out the useless data.

As our application is getting more sophisticated and gets more traffic, this is becoming a real and significant pain and we need a solution for this.

Is it going to have the same luck as the feature request on EF which was and still is idle for 6 years?

We really need an answer, there are solutions like denormalize our data model but that's rarely a good idea.

Thanks!

[rowanmiller]

@gdoron we do want to do this, but it's not at the top of the list. Our Roadmap will give you an idea of the things that are going to be worked on next. You will see that this is listed under "High Priority", but just not the "Critical O/RM" list.

[gdoron]

@rowanmiller I'm sure everyone has different points of view and needs but here are my two cents:
Most of the things that are missing have somewhat reasonable workarounds.

e.g.
Lazy load - simply Include upfront all your data.
View mapping- Manually create a migration.
SP Mapping - Use some technique as with View.
etc.

But Filtered Include has 0 reasonable workarounds.
The only workaround is writing raw SQL but in many cases you need it for almost all of your queries, so that's not an option or else why use an ORM to begin with.

So reiterating what @mikes-gh wrote months ago:

Don't underestimate the value of filtered include. It was a top request in ef 6.
I came across this requirement on my first ef 7 project.
I'd take that over lazy loading any day.

I had already 3 projects on EF Core, and it was a requirement and a pain in ALL of them.

[rowanmiller]

Just to be clear, the items on the roadmap are the very top of the 100s of feature requests we have sitting on the backlog. So it's inclusion on the list means that it is one of our top priorities. The split between "Critical" and "High Priority" is always subjective. The current split is based on our imperfect aggregation of the feedback from all our customers.

It's not as clean as true filtered loading support, but you can do something like this to do filtered loading. It will run two queries, but that is what EF Core would do under the covers anyway, when you load a collection.

var blogs = db.Blogs
    .Where(b => b.Rating> 3)
    .ToList();

var blogIds = blogs.Select(b => b.BlogId).ToList();

db.Posts.Where(p => blogsIds.Contains(p.BlogId))
    .Where(p => p.Rating > 3)
    .Load();

[gdoron]

@rowanmiller

It will run two queries, but that is what EF Core would do under the covers anyway, when you load a collection.

I thought it's a temporary limitation that you're working on fixing.
Are you telling me it is by design?
Because it's not just two queries, it's two round trips to the DB.

Anyway, it might be acceptable for one collection loading, but when you have 10 included entities (we already have 6-7 in some places,), that means 11 round trips or querying the DB with 10 connections concurrently.

I might got you wrong, but if I didn't... Houston we have a problem.

[maumar]

Fixed in 21b9a35

Example:

customers.Include(c => c.Orders.Where(o => o.Name != "Foo").OrderByDescending(o => o.Id).Take(3))

Supported operations: Where, OrderBy/ThenBy, Skip, Take.

Only one filter allowed per navigation, so for cases where the same navigation needs to be included multiple times (e.g. multiple ThenInclude on the same navigation) apply the filter only once, or apply exactly the same filter for that navigation.

Example:

customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders).ThenInclude(o => o.Customer)

or

customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.Customer)

Feature will ship in the next preview, but should be available in our daily builds shortly for anyone interested in taking an early look. If you find some problems please file new issue as this thread is already very long.

[Trolldemorted]

Is there a chance that we get this feature in a stable release before winter? Virtually all my dotnet projects would benefit from this, but I am reluctant to ship releases that depend on preview versions.

[themisir]

Is there a chance that we get this feature in a stable release before winter? Virtually all my dotnet projects would benefit from this, but I am reluctant to ship releases that depend on preview versions.

I'm currently using something like that.

var productsWithEnglishTranslations = await _context.Products
  .Select(p => new Product
  {
    Id = p.Id,
    Name = p.Name,
    Price = p.Price,
    Translations = p.Translations.Where(pt => pt.Language == 'en'),
  })
  .ToListAsync();

[roji]

@Trolldemorted filtered includes will only be released with 5.0.0 - we only released bug fixes in patch release (i.e. for 3.1.x) to reduce the risk of introducing breakage.

[kosalsean]

Is there a chance that we get this feature in a stable release before winter? Virtually all my dotnet projects would benefit from this, but I am reluctant to ship releases that depend on preview versions.

I'm currently using something like that.

var productsWithEnglishTranslations = await _context.Products
  .Select(p => new Product
  {
    Id = p.Id,
    Name = p.Name,
    Price = p.Price,
    Translations = p.Translations.Where(pt => pt.Language == 'en'),
  })
  .ToListAsync();

Thanks

[appsupport-gc]

I don't see why this isn't working then!

      var yearEntity = dc.YearEntities.Include(ye => ye.EntitySections.OrderBy(es => es.SectionOrder))
            .ThenInclude(es => es.StoredEntries.OrderBy(se => se.EntryOrder))
            .Where(e => e.EntityGuid == EntityGuid)
            .FirstOrDefault();

It gives me the error:

"Expression of type 'System.Linq.IOrderedQueryable1[YBEdit.Shared.Models.EntitySection]' cannot be used for return type 'System.Linq.IOrderedEnumerable1[YBEdit.Shared.Models.EntitySection]'"

Did this feature not make it beyond preview? I don't see anyone else suggesting using this method either.

Thanks,

Jonathan

[cgountanis]

@appsupport-gc what if you remove the orderbys and maybe just try one where on an include and then the other can you narrow it down to anything?

[appsupport-gc]

Oh I thought I saw suggested that orderbys on sub includes were now supported. I was hoping that was the case. It looks like I misread above and misunderstood the source linking here... Too bad. Some way to include OrderBys with ThenIncludes would be great! I guess my only option is manually stepping through each level of object? :(
Jonathan

[appsupport-gc]

The example above suggests this should work:

"customers.Include(c => c.Orders.Where(o => o.Name != "Foo").OrderByDescending(o => o.Id).Take(3))"

That should be equivalent to dc.YearEntities.Include(ye => ye.EntitySections.OrderBy(es => es.SectionOrder)) which doesn't work for me.

[mguinness]

Check the documentation for Filtered include and if it isn't working as documented then create a New issue with more information.

[Mike-E-angelo]

Gosh what an awesome feature. Works like a champ. 💪 Thank you so much for putting this together and all your efforts out there. 👍

[CollinAlpert]

This is really cool! How can I dynamically build a predicate for this? If I compile the System.Linq expression (since .Where() expects a Func<T, bool>) I get the following exception:
Expression of type 'System.Func`2[MyType,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[MyType,System.Boolean]]'

[smitpatel]

@CollinAlpert Queryable.Where expects a Expression<Func<T, bool>> parameter. The enumerable version expects one without Expression (just func). Your exception message say you are passing func rather than expression somewhere. The solution your question would depend on how you are constructing the expression tree. But that is not related filtered include or EF Core in any form. This kind of questions are more suitable for online question-answer forums like stackoverflow.

[CollinAlpert]

@smitpatel Thanks for your reply. The navigation property used in my .Include() is an ICollection so I cannot pass an Expression into the .Where method, since it is the enumerable's method, not Queryable. How do I make my navigation property use the Queryable.Where method when filtering inside .Include()?

[itx-digital]

Hello,
Is there any chance you will implement this feature in EF Core 3 ?
Thank you.