Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support filtered Include #1833

Open
0xdeafcafe opened this issue Mar 16, 2015 · 163 comments

Comments

Projects
None yet
@0xdeafcafe
Copy link
Contributor

commented Mar 16, 2015

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

This comment has been minimized.

Copy link

commented Mar 16, 2015

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

This comment has been minimized.

Copy link
Contributor Author

commented Mar 16, 2015

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 rowanmiller added this to the Backlog milestone Mar 23, 2015

@rowanmiller

This comment has been minimized.

Copy link
Member

commented Mar 23, 2015

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 rowanmiller changed the title Can't do a Where statement inside an Include, if it's followed by another Include Support filtered Include Mar 23, 2015

@rowanmiller

This comment has been minimized.

Copy link
Member

commented Mar 23, 2015

Opened new issue for better exception message #1883

@mikes-gh

This comment has been minimized.

Copy link

commented Dec 12, 2015

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

This comment has been minimized.

Copy link

commented Dec 30, 2015

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

This comment has been minimized.

Copy link

commented Jan 21, 2016

I too would love this feature.

@gdoron

This comment has been minimized.

Copy link

commented Jul 25, 2016

@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

This comment has been minimized.

Copy link

commented Jul 25, 2016

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

This comment has been minimized.

Copy link

commented Jul 25, 2016

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

@lucacestola

This comment has been minimized.

Copy link

commented Jul 25, 2016

@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

This comment has been minimized.

Copy link

commented Jul 25, 2016

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

This comment has been minimized.

Copy link

commented Sep 5, 2016

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

This comment has been minimized.

Copy link

commented Sep 5, 2016

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

This comment has been minimized.

Copy link

commented Sep 5, 2016

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

This comment has been minimized.

Copy link

commented Oct 6, 2016

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

This comment has been minimized.

Copy link

commented Oct 6, 2016

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

@ChainReactive

This comment has been minimized.

Copy link

commented Nov 1, 2016

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

This comment has been minimized.

Copy link

commented Nov 21, 2016

@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

This comment has been minimized.

Copy link
Member

commented Nov 21, 2016

@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

This comment has been minimized.

Copy link

commented Nov 22, 2016

@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

This comment has been minimized.

Copy link
Member

commented Nov 22, 2016

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

This comment has been minimized.

Copy link

commented Nov 22, 2016

@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.

@johnkwaters

This comment has been minimized.

Copy link

commented Mar 27, 2019

I agree with the sentiment that with DbContexts as a short lived unit of work concept. these scenarios are not realistic. In addition, I think Lazy Loading is quite a devil in disguise, leading developers to write code with unpredictable performance characteristics, and I would hate to see support for bad patterns trump the need to be able to express efficient filtered subqueries. I would say if you are doing Lazy, then don't do these kinds of queries, or get undefined results if you do Or you could add something like the Global Filter suppression, WithNoFilteredInclude to appease the lazy crowd or vice versa. That would allow callers to explicitly Opt in or Out of this behavior.

@ajcvickers

This comment has been minimized.

Copy link
Member

commented Mar 28, 2019

@mguinness Just to clarify, current POR is still to release EF Core as part of the wider .NET Core releases on the same schedule.

@denious

This comment has been minimized.

Copy link

commented Mar 28, 2019

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.

Can someone please confirm that ODATA through EFCore $expand filtering is done in-memory instead of being sent to the DB because of this lacking feature?

@kakins

This comment has been minimized.

Copy link

commented Apr 1, 2019

Unless I'm just having a complete brain malfunction today, I'm still not sure any of the above has solved this issue for me. Global query filters won't work because my filter criteria is provided at runtime, not during EF model building.

As a point of clarification, I essentially want to be able to run this query using EF relationships, with at or near the same efficiency of raw SQL:

ParentTable (1) -> ChildTable (*)

SELECT ParentTable.* 
FROM ParentTable
INNER JOIN ChildTable
	ON ParentTable.ID = ChildTable.ParentID
WHERE ChildTable.SomeValue = 'SomeValue'

My model:

public class ParentTable
{
    public int ID { get; set; }
    public List<ChildTable> Children { get; set; }
}

My EF Query:

// What do i do here?  
// I essentially only want to get ParentTable records where ChildTable records exist, 
// containing a specified 'SomeValue'.
// This seems to generate a SELECT statement for each row in the ChildTable.
_context
    .ParentTable
    .Where(parent => parent.Children.Any(child => child.SomeValue == "SomeValue");

Which is what made me consider a "filterable" include, which is not currently supported.

@maurei

This comment has been minimized.

Copy link

commented Apr 3, 2019

Would love to see this implemented in the near future!

@dominikfoldi

This comment has been minimized.

Copy link

commented Apr 12, 2019

We really need local filters in our projects. Without it EF is not really production ready, I cannot understand that how such a feature is not implemented already. So please prioritize this to the top.

@TiagoCunhaF

This comment has been minimized.

Copy link

commented Apr 18, 2019

I'am have exact some problem, filter on children table..

@dblood2

This comment has been minimized.

Copy link

commented Apr 22, 2019

Throwing my vote in for this as well.

@OndrejValenta

This comment has been minimized.

Copy link

commented Apr 23, 2019

Jesus Christ man, I've just found out that EF Core does not know how to do this even after four years of this issue. That is just lame. It's a basic feature. How simple would our database have to be for us not need this functionality in EF Core? I wonder what is the recommended workaround for the SQL query @kakins wrote in his comment.

LLBLGen knew how to do this back in 2008. How can it be so difficult to do when they were able to do it even back then?

Multi-entity filters

@stevieTheCoder

This comment has been minimized.

Copy link

commented Apr 25, 2019

Throwing my vote in on this too...

@popcatalin81

This comment has been minimized.

Copy link

commented Apr 25, 2019

Instead of so many people throwing votes ... How about throwing some PRs with contributions to this?

@mguinness

This comment has been minimized.

Copy link

commented Apr 25, 2019

@popcatalin81 I understand there are some design decisions that need to be made by the team before work can commence, see #1833 (comment) for more detail.

@popcatalin81

This comment has been minimized.

Copy link

commented Apr 25, 2019

@popcatalin81 I understand there are some design decisions that need to be made by the team before work can commence, see #1833 (comment) for more detail.

This is not blocking for the actual functionality. It's just a decision on how to set a flag. Ignore that flag for the time being and add the code for filtered included for the Load.

@rafalschmidt97

This comment has been minimized.

Copy link

commented Apr 26, 2019

Would be cool to have that in EF Core:
https://entityframework-plus.net/query-include-filter

For now I see several solutions:

  1. a) SQL - There is a ExecuteReaderAsync method and https://docs.microsoft.com/en-us/ef/core/querying/raw-sql. I would also consider using Dapper only for retrieving data.
    b) NOSQL - @hades200082 good catch. Dunno 😅 I haven't worked with mongo+ef.
    Currently, the 2.1 EF Core roadmap doesn't show any new providers for NoSQL DBs. (https://docs.microsoft.com/en-us/ef/core/what-is-new/roadmap)
    You need probably a different provider for connection probably. Like this: https://docs.microsoft.com/en-us/dotnet/standard/microservices-architecture/microservice-ddd-cqrs-patterns/nosql-database-persistence-infrastructure#retrieve-the-database-and-the-collection

  2. Selecting everything and filtering (slow but in most cases it will be enough for a proof of concept)

  3. I haven't tested that but there are two valuable articles which might help:
    a) https://weblogs.asp.net/dixin/entity-framework-core-and-linq-to-entities-4-query-methods
    b) https://docs.microsoft.com/en-us/ef/core/querying/related-data

  4. Global query filter - but it is useful mainly to reduce deleted/disabled records - https://docs.microsoft.com/en-gb/ef/core/querying/filters

Any other ways?

@salaros yeah. You are right. It was rude. [I've removed this useless comment]

@hades200082

This comment has been minimized.

Copy link

commented Apr 26, 2019

@rafalschmidt97 It's worth remembering the EF is not just for SQL Server. How would your options work with Mongo or MySQL or Postgres?

@salaros

This comment has been minimized.

Copy link

commented Apr 26, 2019

@rafalschmidt97 stop flooding this issue with your no-sense comments. Npgsql EF Core provider and MongoDB provider both work well, because they were created by highly skilled devs, unlike your friends, so there are no "weird sql queries" there.
Additionally there ARE non-MSSQL providers for EF Core created by Microsoft, e.g. https://docs.microsoft.com/en-us/ef/core/providers/sqlite/

@andriysavin

This comment has been minimized.

Copy link

commented Apr 28, 2019

@salaros could you point me to the MongoDB provider, I didn't know it exists!

@MoazAlkharfan

This comment has been minimized.

Copy link

commented Apr 30, 2019

So for people who really need this I have a simple solution but you'll have to project your query using "Select()" on every query.

public class Entity
{
    public int Id { get; set; }

    public virtual IQueryable<Entity> Relations { get; set; }

    [NotMapped]
    public List<Entity> RelationsLoaded { get; set; }
}

And you would use it like this:

Expression<Func<Entity, bool>> filter = r => r.Id < 500;

List<Entity> allEntities = DbContext.Entities
    .Select(e => new Entity
    {
        Id = e.Id,
        RelationsLoaded = Relations
            .Where(filter)
            .ToList()
    })
    .ToList();

It would also be nice to cast into IQueryable.

Assuming:
1- "NotMappedAttribute" and "RelationsLoaded" are removed.
2- "Relations" becomes virtual ICollection

Expression<Func<Entity, bool>> filter = r => r.Id < 500;

List<Entity> allEntities = DbContext.Entities
    .Select(e => new Entity
    {
        Id = e.Id,
        Relations = (Relations as IQueryable<Entity>)
                .Where(filter)
                .ToList()
    })
    .ToList();

// OR similar to OPs' suggestion

allEntities = DbContext.Entities
    .Include(e => (e.Relations as IQueryable<Entity>).Where(filter))
    .ToList();
@smitpatel

This comment has been minimized.

Copy link
Contributor

commented Apr 30, 2019

(Relations as IQueryable<Entity>) => Relations.AsQueryable()

@hades200082

This comment has been minimized.

Copy link

commented Apr 30, 2019

Wouldn't this trigger more store queries than desired though?

@kvijayan-aliera

This comment has been minimized.

Copy link

commented May 4, 2019

Global query filters cannot contain references to navigation properties in .Include and .ThenInclude. That's bad news for EF core developers. Would be a nice feature to have.

@garfbradaz

This comment has been minimized.

Copy link

commented Jun 21, 2019

Is this on the roadmap @divega ? Or a Spec so the community can assist?

@divega

This comment has been minimized.

Copy link
Member

commented Jun 21, 2019

@garfbradaz this is in the Backlog milestone, meaning that is not assigned to any specific release, but still active, so it is something we would like to do in the future (more information about how we use milestones to track work at https://docs.microsoft.com/ef/core/what-is-new/roadmap).

In general contributions are welcome on all issues, but this particular issue requires some design work (which can also be contributed) before it can be implemented.

To set the right expectations, we are completely booked for EF Core 3.0 so I don't think there are any chances for something like this to make it into the release. Even if perfect design and implementations were contributed, evaluating them would require deviating significant resources to that. Also if you want to start a discussion about the design now with the goal of adding the feature in a future release, that is fine, but we may not be as responsive as we would like to be.

@wisepotato

This comment has been minimized.

Copy link

commented Jun 24, 2019

@divega any documents on how to start this design process? Its something I really need and am willing to spend some time on. But I find it hard for a "professional" product such as this. Do you have guidelines/plans to follow?

@divega

This comment has been minimized.

Copy link
Member

commented Jun 24, 2019

@wisepotato we don't have written guidance on how to start the design process specifically. The basics from https://github.com/aspnet/AspNetCore/blob/master/CONTRIBUTING.md of course apply.

We generally only write "one page" descriptions of the functionality we want to enable with enough detail to make the useful to gather feedback. We can add more details if the feedback requests it, and iterate several times.

I would recommend you start trying to sum up the scenarios discussed in this thread (yup, that alone can be daunting given there are 160 comments in this issue) and identify goals (the problems you are trying to solve) and non-goals (the problems you consider are out of scope), then describe the proposed solution. The latter should include examples of how the API can be used and the actual signature for any method you want to add. I find it useful to create at least a mock extension method version of the API that I can compile and use for the samples. The you can look around for corner cases in which the API may not work as expected.

@yannickvidal

This comment has been minimized.

Copy link

commented Jul 8, 2019

Not a solution, but a problem related to this discussion

I was trying to find a way to add a filter condition on a join and came across this post.
All I was trying to do is something like SELECT * FROM books LEFT JOIN user_books ON user_books.book_id = book.id AND user_books.name = "nice"

The following code does return what I want, however it should not:

using (var context = new ContextController())
            {
                var booksList = context.Books
                    .ToList();

                var somethingElse = context.UserBooks
                    .Include(ub => ub.Book)
                    .Where(ub => ub.Name == "nice")
                    .ToList();  
            }

If you debug the project, you will see that after the first ToList() the list of books will be loaded inside booksList, with no relationship whatsoever because I did not specify ToInclude, which is fine.

But after doing the second reverse query, that relationship (the one with user_book.Name = "nice, and only this one) will be added loaded to bookList, even though that variable and query have got nothing to do with that initial bookList variable.

@wisepotato

This comment has been minimized.

Copy link

commented Jul 9, 2019

@yannickvidal this is great, but it can get hairy with the dbcontex around this. So it's tricky to get this right, cbecause you dont want to load stuff that shouldnt be in the end result. but yeah nice find

@TheMisir

This comment has been minimized.

Copy link

commented Jul 13, 2019

😟 It's 2019, and this 4 years old issue has not fixed yet.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.