efcore 🚀 - Support filtered Include

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.

popcatalin81 on 16 Mar 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.

0xdeafcafe on 16 Mar 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 on 23 Mar 2015

Opened new issue for better exception message https://github.com/aspnet/EntityFramework/issues/1883

rowanmiller on 23 Mar 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.

mikes-gh on 12 Dec 2015

👍102

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 ?

rendmath on 30 Dec 2015

👍33

I too would love this feature.

joshmouch on 22 Jan 2016

👍19

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

gdoron on 25 Jul 2016

👍25 👀1

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?

lucacestola on 25 Jul 2016

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

gdoron on 25 Jul 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.

lucacestola on 25 Jul 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?

Bartmax on 25 Jul 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?

armartinez on 5 Sep 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

arielcloud on 5 Sep 2016

👍2

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.

armartinez on 5 Sep 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.

atrauzzi on 6 Oct 2016

👍21

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

gdoron on 6 Oct 2016

👍8

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.

HappyNomad on 1 Nov 2016

👍6

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

gdoron on 21 Nov 2016

👍1

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

rowanmiller on 21 Nov 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.

gdoron on 22 Nov 2016

👍24

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.

```c#
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();
```

rowanmiller on 22 Nov 2016

👍6

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

gdoron on 22 Nov 2016

👍4

cc @anpete and @divega who can provide some more context. But in general, the EF Core approach out performs the EF6.x approach pretty consistently.

rowanmiller on 22 Nov 2016

I find it very hard to believe EF Core is or will be faster because of this approach.
You might have fixed some internal slow parts that caused slow execution?

I believe several roundtrips will never be as fast as a single well written query.
That just doesn't make any sense to me.
I can ask my ex-CTO which is a SQL-Server MVP for his input if you like @rowanmiller.

gdoron on 22 Nov 2016

@gdoron We never execute n+1 queries for Include. For your collection example, we would execute two queries, one for the parents and one for all of the children of those parents.

EF6 would execute one single left outer join query, which results in the dreaded cartesian product result set - The parent data is duplicated for each child row in the results.

anpete on 22 Nov 2016

❤2

@anpete that's a relief... 👍 😄
Thanks for clarifying!

gdoron on 22 Nov 2016

This is probably obvious, but that's also what the workaround code I listed does. It's two queries, not n+1.

rowanmiller on 22 Nov 2016

In the example you provided,

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

how should I also include Blog.Images ? Do I need a 3rd query?

db.Images.Where(i => blogsIds.Contains(i.BlogId))
    .Where(i => !i.IsDeleted)
    .Load();

For your collection example, we would execute two queries, one for the parents and one for all of the children of those parents.

I don't see 1 query for parents and 1 for ALL childrens, instead I see one query PER children.

Am i missing something?

Not to mention about other type of filtering like Take or Skip ... that makes the whole deal a lot uglier and complicated with GroupBy afaik. https://github.com/aspnet/EntityFramework/issues/7033

I really think the most on point comment is:

Don't underestimate the value of filtered include.

Bartmax on 22 Nov 2016

👍3

@rowanmiller @anpete actually I'm not sure we understood each other correctly.
I wasn't talking about N+1 where N = number of rows, but where N=number of included properties.

So in case I have a query like:

context.Post
.Include(x=> x.Images)
.Include(x=> x.Paragraphs)
.Include(x=> x.As)
.Include(x=> x.Bs)
.Include(x=> x.Cs)
.Include(x=> x.Ds)
.Where(x => x.Id == 1)
.ToList()

We'll have 1 query for the post, and another 6 queries for the navigation properties, am I right?
If I am, that's bad even if it's not 6 round trips.

gdoron on 22 Nov 2016

👍2

@anpete @rowanmiller
Just had a phone call with a SQL-Server MVP, a summary of what he said:

To SQL Server it doesn't matter if you'll have a single query or 10, the redundant fields returned have 0 impact on SQL Server.
Splitting the queries is what SQL is doing internally anyway, and when people try to optimize SQL, it's usually to the worst as SQL is doing better when not forcing it do act in a specific way.
Having multiple queries have overhead on SQL.
The only thing that actually splitting queries solves is bandwidth on the network as there will be less bytes transferred over the wire, and he says it's negligible compared to having multiple queries.

EOF.

gdoron on 22 Nov 2016

@gdoron i don't think it's that black or white, and pretty sure @rowanmiller (and the ef team) understands very much the internals of SQL and ORMs. I wouldn't even dare to question that.

I think the item being in high priority should be enough, they know it's important and they will add it. But for some it's critical and for others is high priority. This thread focused on make it a more priority item, not disusing the design whatsoever.

On my scenarios, even I would love to have filtered includes and I would use it on every project. It's not critically blocking me. High priority sounds reasonable, but I don't mind if it make to critical either.
And actually, I'm more interested on the api consumption than on query performance.

Bartmax on 22 Nov 2016

pretty sure @rowanmiller (and the ef team) understands very much the internals of SQL and ORMs. I wouldn't even dare to question that.

I never did... and I apologize to the team if I was understood otherwise!
It never hurts to hear another expert though.

gdoron on 22 Nov 2016

👍2

@Bartmax

how should I also include Blog.Images ? Do I need a 3rd query?

Yes, you would need a third query. I'm definitely not saying that it's as good as native filtered loading, it does get pretty messy, it's just one possible way to tackle the problem while we do not have native support.

I don't see 1 query for parents and 1 for ALL childrens, instead I see one query PER children.

The second query starts by filtering to posts that belong to the blogs returned in the first query (.Where(p => blogsIds.Contains(p.BlogId))). It then applies additional filters over that set of posts - in a single query.

rowanmiller on 22 Nov 2016

@gdoron There are trade-offs here for sure. I would encourage you to do some measuring of your specific scenarios using both the EF6 and EF Core approaches. We would definitely be open to further improvements in this area if you uncover something compelling.

anpete on 22 Nov 2016

👍1

BTW, another aspect of this that I would love to see feedback on is what level of granularity is more interesting to prioritize:

Explicit filters at the query level as the original post describes
Implicit filters that are applied to all queries over specific types in a context (but that can possibly overridden at the query level). E.g. for the soft-delete case this would probably mean the filter can be setup up once for each type that has an IsDeleted property. I don't think we have an issue for that, but the closest is #3932

divega on 22 Nov 2016

@divega I really like your implicit filtering idea! I use something similar with InsertDate:

    private static void ConfigDefaultInsertDate(ModelBuilder builder)
    {
        builder.Model
            .GetEntityTypes()
            .SelectMany(e => e.GetProperties())
            .Where(x => x.Name == "InsertDate" && x.ClrType == typeof(DateTime))
            .ToList().ForEach(x =>
            {
                x.SqlServer().DefaultValueSql = "GetUtcDate()";
                x.ValueGenerated = ValueGenerated.OnAdd;
            });
    }

And it saves us a lot of time with the mapping, and your idea would save a lot of time with querying and more importantly it can us from bugs where we forgot to filter the soft deleted rows which is the top concern on Stackoverflow about soft delete.

But had I had to choose only one feature, I would have chosen the explicit as it gives us more choices.
I would rather having to write more but being able to filter all kind of stuff, like filter by the image Type property I mentioned earlier or by the the first row ordered by [Order]

e.g.:

context.Posts.Where(x=> x.Id == 1)
    .Include(x => x.Images.Where(i =>!i.IsDeleted && i.Type == ImageType.Primary)
    .Include(x => x.Paragraphs.Where(p =>!p.IsDeleted).OrderBy(p => p.Order).First())
    .ToList()

Again, I really really like the implicit idea, but it doesn't let me do this.

Edit:
Worth mentioning, Join in SQL based on first row isn't a trivial task, so I'm not sure it was a good example... filter by type is a better one.

gdoron on 23 Nov 2016

👍3

I know there are a lot opposing opinions on this topic, so I want to add mine.

To understand where I'm coming from, a little bit of background: I've been doing SQL Server and EF training and consultancy for around 10 years (among others) and I've been involved in performance critical projects (like processing millions of rows a minute, tens of thousand of transactions a second, thousands of users concurrency, and lots more), also I've been using EF since EF 1 beta and all subsequent versions ever since, in major enterprise and commercial consumer applications. I even have a EF6 course "The pitfalls of EF and extracting the most performance out of it" (The course started during EF 4.1 time frame) that was very popular.

In EF6 an lower one of the things I've seen completely destroy the performance of application (over and over again) is Dreaded Cartesian join effect of Include when including multiple related collections

In a specific case where a list of products (thousands) was loaded and included Product Facets (thousands again) and included Categories and category facets (thousands), and Geographic locations , and location facets, etc (a graph of tens of thousands of items total) the Cartesian join produced by the include generated a result set of over 40 million rows that was processed, and execution time was almost a half an hour for that query only. A simple change of loading all the data with one query per level made the data load in a couple of seconds (under 3s).

In this particular case 20 or so queries with includes only for related entities (N:1) ad NOT related collections (1:N, M:N) were allot faster than a giant one (11K lines query and 40M+ result set) by 3 orders of magnitude..

Over the years, I've rewritten so many EF Linq queries and split them into multiple queries as performance work that I've lost count (and always with considerable effects).

Finally some years ago I've built a Load Graph API on top of EF6 that could be used to do a load a graph with one query per level (basically what EF Core does now), withouth the dreaded Cartesian join created by including collections and it would look something like:

var customers = GraphLoader.WithRoot<Customers>(dbContext)
           .Where(c => c.Country == "US")
        .LoadRelated(
            c => c.Orders.Where(o => o.ShipViaShipper.CompanyName == "United Package"),
            orders => orders.Include(o => o.Employee)
        )
        .LoadRelated(
            c => c.CustomerCustomerDemos
        ).Load();

Because this was graph loading API it exposed it's own Linq methods, disallowing projections.

The best thing is that this API can be implemented very easily in a few hundred lines of code:

public static class GraphLoader
{
    public static Graph<T> WithRoot<T>(DbContext context)
        where T : class
    {
        return new Graph<T>(context.Set<T>());
    }

    public static Graph<T> LoadRelated<T, TU>(this Graph<T> graph, Expression<Func<T, TU>> related)
        where T : class
        where TU : class
    {
        graph.additionalQueries.Add(graph.root.Select(related));
        return graph;
    }

    public static Graph<T> Include<T, TU>(this Graph<T> graph, Expression<Func<T, TU>> related)
        where T : class
        where TU : class
    {
        graph.root.Include(related);
        return graph;
    }

    public static Graph<T> LoadRelated<T, TU, TV>(this Graph<T> graph, Expression<Func<T, IEnumerable<TU>>> related, Func<Graph<TU>, Graph<TV>> loadRelated)
        where T : class
        where TU : class
        where TV : class
    {
        var newQuery = graph.root.SelectMany(related);
        var newGraph = new Graph<TU>(newQuery);
        loadRelated(newGraph);
        graph.additionalGraphs.Add(newGraph);
        return graph;
    }

    public static Graph<T> Where<T>(this Graph<T> graph, Expression<Func<T, bool>> filter)
        where T : class
    {
        graph.root = graph.root.Where(filter);
        return graph;
    }

    public class Graph<T> : Graph
    where T : class
    {
        internal IQueryable<T> root;
        public Graph(IQueryable<T> source)
        {
            this.root = source;
        }

        public virtual List<T> Load()
        {
            LoadAdditional();
            return root.ToList();
        }

        public override void LoadRoot()
        {
            root.Load();
        }
    }

    public class Graph
    {
        internal List<IQueryable> additionalQueries = new List<IQueryable>();
        internal List<Graph> additionalGraphs = new List<Graph>();

        public virtual void LoadRoot()
        {

        }

        public void LoadAdditional()
        {
            foreach (var additionalQuery in additionalQueries)
            {
                additionalQuery.Load();
            }
            foreach (var graph in additionalGraphs)
            {
                graph.LoadRoot();
                graph.LoadAdditional();
            }
        }
    }
}

This is an incomplete version, but if someone shows interest I could make it a library.

popcatalin81 on 24 Nov 2016

👍15 ❤6

@popcatalin81 great feedback, my concern is the API to do the filtered include. Performance wise, I expect the EF team to come up with what's best. 1 superbig query or many small ones, i really don't care, that's exactly why I use an ORM, to abstract that from code. And IF there's a performance implication, I can dig deeper.

Bartmax on 24 Nov 2016

👍2

@popcatalin81 GREAT feedback indeed, and thanks for sharing.

Curious to know, when you wrote "slow execution", was it on the DB side or on the network side or on the IIS server side?

Two notes though:

The scenario you described isn't the usual usage of an ORM (at least from my XP), querying for so much data.
While on EF 6 you could change the execution relatively easy by splitting the query into multiple ones when it was needed (millions of records as you had), in EF Core, it's impossible to do the opposite, returning all the data in a single big query.

Since you clearly have much more XP and knowledge than me, do you you agree that on less returned rows (more common) queries EF 6 approach was faster?

Thanks again!

gdoron on 24 Nov 2016

👍2

@gdoron, the slow execution was also on the DB side due to "table spools" (buffering data in tempdb) and on the network side (gigabytes of data transferred for the giant query)

Note: One scenario where table spools are created is when not using Read Commited Snaposhot and the number of read locks that needs to be taken for the data set size far exceeds the max, which was this case.

popcatalin81 on 24 Nov 2016

@popcatalin81 yeah, that makes sense with huge queries.
What about "normal" much smaller queries?

Anyway, I wish EF Core could provide an API where the developer can send a boolean flag that tells EF which strategy to use.

Something like:

public List<T> ToList(bool splitQuery = false)

Currently I have no way of combing the queries into one query unless I manually craft a SQL query and execute it with FromSql which is unrealistic.

gdoron on 24 Nov 2016

👍1

@gdoron

Currently I have no way of combing the queries into one query unless I manually craft a SQL query and execute it with FromSql which is unrealistic.

You can always use do something like this:
```c#
from c in context.Customers
join o in context.Orders on c.CustomerID equals o.CustomerID into orders
select new { c, orders };

which produces this:
```sql
SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country],
       [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region], [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Customers] AS [c]
LEFT JOIN [Orders] AS [o] ON [c].[CustomerID] = [o].[CustomerID]
ORDER BY [c].[CustomerID]

Of course, this requires using a tracking query so that we get fix-up. We can make it work for no-tracking with a small hack:
```c#
var q = (from c in context.Customers
join o in context.Orders on c.CustomerID equals o.CustomerID into orders
select new { c, _ = SetOrders(c, orders) }).AsNoTracking();

private static object SetOrders(Customer c, IEnumerable orders) => c.Orders = orders.ToList();
```

anpete on 25 Nov 2016

👍1

Just to make it clear, that also for IEnumerables (1:N) so as for N:1 Includes a possibility to filter (extend ON clause on T-SQL level via Linq) is a must.

hidegh on 25 Nov 2016

Chiming in as an ordinary C# dev who has had the exact same issues as @popcatalin81 and even made use of a custom API similar to his GraphLoader . Great to see that others understand this.

jnm2 on 28 Nov 2016

👍1

Another dev who is also holding out hope for filtered includes in a future release.

I came across DynamicFilters, but unfortunately it doesn't support EF Core :(

I also found EntityFramework-Plus, but again no support for EF Core :(

Another hope was for command interceptors where I could manipulate the SQL before it went out for execution, but that's not available yet and discussed in issue https://github.com/aspnet/EntityFramework/issues/626.

Although the Roadmap includes the following under CRUD heading:

Filtered loading allows a subset of related entities to be loaded.
Simple command interception provides an easy way to read/write commands before/after they are sent to the database.

There's no guidance on whether they'll be included in EF Core 1.2 release. If the EF team has higher priorities is there a possibility they can somehow lend support to one of the projects mentioned above?

mguinness on 30 Nov 2016

@mguinness command interception is an another concept. filtering on includes can be "replaced" if you are willing to give up comfort by using automapper on the original iqueryable, thus you can create joins (mixing linq query withg lambda and using Where to manipulate what is joined), but the result will be an anonymous type (or you can create a custom type for it) - anyhow, you are loosing flexibility without filterable Includes.

hidegh on 30 Nov 2016

@hidegh I understand command interception is another concept, but it allows for workarounds when you don't have filtered include. I see three 3 needs for filtered includes, but I'm sure there are others:

Multi-tenant database (inc. row level security)
Security filtering at either user or role level
Soft deletes where tables have boolean flag field

Using command interception you could implement these by altering the SQL before it gets executed (not pretty but effective). Leaving SELECT untouched would mean no impact to EF processing, rather you would modify the JOIN constraint:

LEFT JOIN Post AS p ON b.BlogId = p.BlogId
would change to
LEFT JOIN Post AS p ON b.BlogId = p.BlogId AND 0 = p.Deleted

Maybe I'm missing something obvious, but I don't see how you can do this when .Include() is used.

mguinness on 30 Nov 2016

👍1

OK, so I ran into this today, and also need this feature. Is there a latest on when this might be included in EF?

johnkwaters on 4 Apr 2017

👍3

+1 for Where condition on Include!! Similar to MGuinness, i need to be able to perform security filtering at the user level; I have a RiskRegister object with FK link to ApplicationUser, & want to be able to return all RiskRegisters for logged in user.

michaelpev on 8 May 2017

This should do the trick (any additional where clausule can be added to the joins/Where), but... Need to check out: http://www.thinqlinq.com/Post.aspx/Title/Left-Outer-Joins-in-LINQ-with-Entity-Framework - since depending how the model is set up and how you mix lambda and linq - the resulting query might do a bit of surprise (like in some cases: matching 2 table rows where ID is null)...

            var q =
                from od in dbContext.Set<Document>()
                from p in dbContext.Set<Packet>().Where(i => i.PacketId == od.Packet.PacketId)
                from c in dbContext.Set<Company>().Where(i => i.CompanyId == p.Company.CompanyId)
                select new DocumentMetadataDto()
                {

hidegh on 8 May 2017

Includes can now be filtered at the model-level (see b1379b10)

anpete on 8 May 2017

👍5 ❤4 🎉4

@anpete can you give us more insight? I don't see the Include(x=> x.Where(y => y.Something)) combination (or by any other syntax that supposed to address this issue) on that PR (at least not on tests). What model-level means in this context ?

_off topic:_ the Select(x => x is Kiwi) is beautiful 👍

Bartmax on 8 May 2017

Model-level means you specify the filters on the model (usually in OnModelCreating). E.g.
c# modelBuilder.Entity<Customer>().HasFilter(c => !c.IsDeleted); modelBuilder.Entity<Order>().HasFilter(o => o.CustomerId == this._tenantId);
So, this is not ad-hoc Include filtering, but does enable things like soft-delete and multi-tenancy etc.

anpete on 8 May 2017

as long as one would be able to override that filtering on per query (aka showing soft-deleted records on admin dashboard) it's fine for me 👍 thanks!

Bartmax on 8 May 2017

https://github.com/aspnet/EntityFramework/commit/b1379b10fba013bcb70b60250e39eae8e895a6c3#diff-05dcdf754d61d5e939e36c51beab1374R99

anpete on 8 May 2017

🎉3

This looks like a big step forward. Thank you!
Do I understand correctly this is only for EFCore?

urig on 8 May 2017

Yep, Core only.

anpete on 8 May 2017

Wow a great improvement @anpete kudos!
I didn't think it will be done any time soon.

Do you have an estimation when filler includes will be available not just in the model level?

gdoron on 8 May 2017

Nice!
Any chance you would support interfaces as well as types? All of our multitenant entities have an ITenant, all of our soft deletables have an ISoftDelete...

johnkwaters on 8 May 2017

@gdoron Nothing at the moment, sorry.
@johnkwaters Do you mean modelBuilder.Entity<ITenant>()? If so, we don't, but it should be very easy to write a helper method that loops over all entities in the model and applies to filter to those that implement the interface.

anpete on 8 May 2017

Could you sketch the pseudo code for that? Have tried it in the past and not had much luck expressing it!

John Waters, MVP

CTO4Hire LLC

c: 831.295.3218

http://www.cto4hire.net www.cto4hire.net

From: Andrew Peters [mailto:[email protected]]
Sent: Monday, May 8, 2017 1:47 PM
To: aspnet/EntityFramework EntityFramework@noreply.github.com
Cc: John Waters john.waters@cto4hire.net; Mention mention@noreply.github.com
Subject: Re: [aspnet/EntityFramework] Support filtered Include (#1833)

@gdoron https://github.com/gdoron Nothing at the moment, sorry.
@johnkwaters https://github.com/johnkwaters Do you mean modelBuilder.Entity()? If so, we don't, but it should be very easy to write a helper method that loops over all entities in the model and applies to filter to those that implement the interface?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/aspnet/EntityFramework/issues/1833#issuecomment-299986017 , or mute the thread https://github.com/notifications/unsubscribe-auth/ADQ5rWYWhbEYo0sJ0k0PaDYz6rHBMVjQks5r339MgaJpZM4DvDZS . https://github.com/notifications/beacon/ADQ5rSTEtpzkBLsP5yjMf_zhdvCK3uDBks5r339MgaJpZM4DvDZS.gif

johnkwaters on 9 May 2017

@johnkwaters It is not as easy as it could be but this works:
```c#
public interface IFilter
{
string CustomerID { get; set; }
}

foreach (var entityType
in modelBuilder.Model.GetEntityTypes()
.Where(et => typeof(IFilter).IsAssignableFrom(et.ClrType)))
{
var entityParameter = Expression.Parameter(entityType.ClrType, "e");

var filter
    = Expression.Lambda(
        Expression.Equal(
            Expression.Property(entityParameter, "CustomerID"),
    Expression.Constant("ALFKI")),
         entityParameter);

modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);

}
```

anpete on 9 May 2017

👍2

Thanks a ton!

John Waters, MVP

CTO4Hire LLC

c: 831.295.3218

http://www.cto4hire.net www.cto4hire.net

From: Andrew Peters [mailto:[email protected]]
Sent: Monday, May 8, 2017 3:23 PM
To: aspnet/EntityFramework EntityFramework@noreply.github.com
Cc: John Waters john.waters@cto4hire.net; Mention mention@noreply.github.com
Subject: Re: [aspnet/EntityFramework] Support filtered Include (#1833)

@johnkwaters https://github.com/johnkwaters It is not as easy as it could be but this works:

public interface IFilter
{
string CustomerID { get; set; }
}

foreach (var entityType
in modelBuilder.Model.GetEntityTypes()
.Where(et => typeof(IFilter).IsAssignableFrom(et.ClrType)))
{
var entityParameter = Expression.Parameter(entityType.ClrType, "e");

var filter
    = Expression.Lambda(
        Expression.Equal(
            Expression.Property(entityParameter, "CustomerID"),
           Expression.Constant("ALFKI")),
         entityParameter);

modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);

}

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/aspnet/EntityFramework/issues/1833#issuecomment-300007854 , or mute the thread https://github.com/notifications/unsubscribe-auth/ADQ5rbHzavO3y1Iaorsbdf69Fe-L17QBks5r35XegaJpZM4DvDZS . https://github.com/notifications/beacon/ADQ5rf6Z80sFrl6ImIJWCtLoeZoPRhEsks5r35XegaJpZM4DvDZS.gif

johnkwaters on 9 May 2017

Here's a little helper method.

Usage:

```c#
modelBuilder.AddQueryFilterToAll(_ => _.CustomerID == "ALFKI");

Definition:

```c#
public static class TemporaryUtils
{
    public static void AddQueryFilterToAll<T>(this ModelBuilder modelBuilder, Expression<Func<T, bool>> predicate) where T : class
    {
        if (modelBuilder == null) throw new ArgumentNullException(nameof(modelBuilder));
        if (predicate == null) throw new ArgumentNullException(nameof(predicate));

        foreach (var entityType
            in modelBuilder.Model.GetEntityTypes()
                .Where(et => typeof(T).IsAssignableFrom(et.ClrType)))
        {
            var entityParameter = Expression.Parameter(entityType.ClrType);

            modelBuilder.Entity(entityType.ClrType).HasQueryFilter(
                Expression.Lambda(
                    new ExpressionReplacer(predicate.Parameters[0], entityParameter).Visit(predicate.Body),
                    entityParameter));
        }
    }

    private sealed class ExpressionReplacer : ExpressionVisitor
    {
        private readonly Expression find;
        private readonly Expression replace;

        public ExpressionReplacer(Expression find, Expression replace)
        {
            this.find = find;
            this.replace = replace;
        }

        public override Expression Visit(Expression node)
        {
            if (node == find) return replace;
            return base.Visit(node);
        }
    }
}

jnm2 on 9 May 2017

@anpete I'm using EF Core 1.1.0. How do I get access to HasFilter()?

im1dermike on 9 May 2017

@im1dermike Since the HasQueryFilter code was only merged 8 days ago, I tested against the nightlies (https://dotnet.myget.org/feed/aspnetcore-dev/package/nuget/Microsoft.EntityFrameworkCore.SqlServer).

jnm2 on 9 May 2017

Nice!

John Waters, MVP

CTO4Hire LLC

c: 831.295.3218

http://www.cto4hire.net www.cto4hire.net

From: Joseph Musser [mailto:[email protected]]
Sent: Monday, May 8, 2017 4:21 PM
To: aspnet/EntityFramework EntityFramework@noreply.github.com
Cc: John Waters john.waters@cto4hire.net; Mention mention@noreply.github.com
Subject: Re: [aspnet/EntityFramework] Support filtered Include (#1833)

Here's a little helper method.

Usage:

modelBuilder.AddQueryFilterToAll(_ => _.CustomerID == "ALFKI");

Definition:

public static class TemporaryUtils
{
public static void AddQueryFilterToAll(this ModelBuilder modelBuilder, Expression> predicate) where T : class
{
if (modelBuilder == null) throw new ArgumentNullException(nameof(modelBuilder));
if (predicate == null) throw new ArgumentNullException(nameof(predicate));

    foreach (var entityType
        in modelBuilder.Model.GetEntityTypes()
                .Where(et => typeof(T).IsAssignableFrom(et.ClrType)))
    {
        var entityParameter = Expression.Parameter(entityType.ClrType);

        modelBuilder.Entity(entityType.ClrType).HasQueryFilter(
            Expression.Lambda(
                new ExpressionReplacer(predicate.Parameters[0], entityParameter).Visit(predicate.Body),
                entityParameter));
    }
}

private sealed class ExpressionReplacer : ExpressionVisitor
{
    private readonly Expression find;
    private readonly Expression replace;

    public ExpressionReplacer(Expression find, Expression replace)
    {
        this.find = find;
        this.replace = replace;
    }

    public override Expression Visit(Expression node)
    {
        if (node == find) return replace;
        return base.Visit(node);
    }
}

}

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/aspnet/EntityFramework/issues/1833#issuecomment-300017675 , or mute the thread https://github.com/notifications/unsubscribe-auth/ADQ5rXayjIiu4r1sF1TadyLqfeggo2itks5r36NTgaJpZM4DvDZS . https://github.com/notifications/beacon/ADQ5rXn70LQZW98lklC_UNhKkcOaAGoRks5r36NTgaJpZM4DvDZS.gif

johnkwaters on 9 May 2017

Where is "ad-hoc" Include filtering on the roadmap now? I read here under High priority features:

Filtered loading allows a subset of related entities to be loaded. _This has been addressed by global query filters in EF Core 2.0 Preview 1_

Global query filters refer to the model-level filtering mentioned above does it? I can understand that its addition has dampened enthusiasm for this feature but there are still use cases for Include filtering that don't make sense on a model level. I'm just after clarification here - is this still high priority?

ajbeaven on 8 Jun 2017

😕12

I have now encountered a .Include() that crashes a C# console app when the amount of data that an include brings with it exceeds a certain threshold. I don't know the exact level of data but this illustrates why a filtered include would be beneficial. The main issue for me was the Unhandled exception when iterating the in-memory list of the objects was never the same. I suppose one could argue that this could be an overall .ToList() memory issue but without the ability to filter on the Include I was forced to alter data in a down system situation last night to overcome the limitation. This would be a huge win for those of us who find Include very helpful yet prone to data overload.

dodegaard on 4 Nov 2017

@dodegaard In case it helps, have you had a chance to look at the new query filters feature supported by EF Core 2.0? Would that help in your scenario?

divega on 4 Nov 2017

@ajbeaven As you very well understand, the team considers that the priority of this feature is less because the overlap with query filters. It is very hard to asses what the current priority should be. One way you may be able to help is by articulating the scenarios in which you think this feature is still necessary but query filters can’t help.

divega on 4 Nov 2017

@divega There has been a handful of times where I've needed to load a subset of data for one query, but not for another. Here are a couple of examples:

var parts = _context.Parts
    .Include(c => c.Performances.Where(p => p.PerformerId == _currentUser.UserId));

var plays = _context.Plays
    .Include(c => c.Parts.Where(p => p.CanAudition));

Neither of these includes make sense on global level filter.

I'm also using AutoMapper's .ProjectTo to easily map EF datasets directly to ViewModels without loading superfluous data. Without filtered includes allowing me to load the exact dataset, I need to do some additional filtering after the mapper had done its work, which somewhat defeats the purpose.

ajbeaven on 5 Nov 2017

👍4

@ajbeaven Projections aren't done using 'Include' and includes are not used during projections.
(https://github.com/AutoMapper/AutoMapper/wiki/Queryable-Extensions)

popcatalin81 on 6 Nov 2017

@popcatalin81 Yikes, thanks for putting me straight there! I was confused between one mapping that used ProjectTo and another that used the Mapper.Map (the latter did require Includes to be specified).

That aside, the examples above are still valid use-cases as far as I'm concerned.

ajbeaven on 6 Nov 2017

👍2

I absolutely need this and can't imagine any serious medium to large-scale, data-centered application not needing it. I'm shocked it has sat in the backlogs for coming up on a decade.

xr280xr on 28 Nov 2017

👍10

I also need this feature and encountered it on my first project with EF. Like others say, can't see many projects that won't need this feature...

niccha on 8 Dec 2017

👍1

The workaround looks ugly. .Include(..).Where(..) is a readable, understandable syntax. Please Include this feature fast.

jhvanderven on 13 Dec 2017

😕2 👍2

1+, this is a must-have feature (I think... this is a very basic feature. All of my projects need this).

Example:
var users = _context.Users.Include(u => u.Permissions.Where(p => p.PermissionType == 1));

mhosman on 31 Jan 2018

👍12 👎1

Hey @rowanmiller @ajcvickers this is something that can be done? You have any estimated time for this? Thanks!!!

mhosman on 1 Feb 2018

@mhosman This issue is in the Backlog milestone. This means that it is not going to happen for the 2.1 release. We will re-assess the backlog following the 2.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

ajcvickers on 1 Feb 2018

Hey @ajcvickers thank you very much for your response. The only approach here is to make a raw SQL query? Maybe there is some example to mix a full lambda query with include and add a FromSQL to filter the Include? Or maybe make an include and then filter the results manually? (this last option is not performant).

mhosman on 2 Feb 2018

@mhosman Global query filters (HasQueryFilter API in OnModelCreating) are one way to do this.

anpete on 2 Feb 2018

👎1

Hey @anpete I know that, but I need to make a filter based on a specific value that changes all the time.

mhosman on 2 Feb 2018

👍1

@mhosman IIRC you can use member properties of the DbContext to do that already with the filtering API we have now. You just need to set the value, before executing the query. There are no concurrency issues as EF isn't thread-safe, so you can only do one query at a time

TsengSR on 2 Feb 2018

@anpete @TsengSR Could you please provide some basic workaround example based on this example that I'm trying to apply (using lambda)??

var users = _context.Users.Include(u => u.Permissions.Where(p => p.PermissionType == 1));

Thank you very much!

mhosman on 2 Feb 2018

@mhosman

Add a _permissionType field to your context and initialize the value from a ctor argument.
Setup a filter on the Permission ET like:
modelBuilder.Entity<Permission>().HasQueryFilter(p => p.PermissionType == _permissionType);
Now any query for Permission will be filtered by the current value of _permissionType.

anpete on 2 Feb 2018

👍1

@anpete Do you mean to initialize the value in the PermissionType Class? (sorry my confusion but I can't see where to initialize that specific value for that specific query in the controller).

mhosman on 2 Feb 2018

The normal way is to have the value initialized when creating the context:

var context = new MyContext(permissionType: 1);

In this case, the value is "set" for the lifetime of that specific context instance. Other instances can have difference values.

You should also be able to add a PermissionType property to your context.

var context = new MyContext();
context.PermissionType = 1;
var q = context.Users.Include(u => u.Permissions).ToList();

This is less common but should allow you to change the value on the fly.

anpete on 2 Feb 2018

👍2

Great @anpete! Thank you very much for your help!

mhosman on 2 Feb 2018

@anpete Just one last question. How to access _permissionType attribute from a Self-contained type configuration file?

mhosman on 2 Feb 2018

@smitpatel Passing in the context to the entity configuration works, right?

anpete on 2 Feb 2018

No. See issue #10301

smitpatel on 2 Feb 2018

Yes, passing the context to a file with this example code:

class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
  public void Configure(EntityTypeBuilder<Customer> builder)
  {
     builder.HasKey(c => c.AlternateKey);
     builder.Property(c => c.Name).HasMaxLength(200);
   }
}

mhosman on 2 Feb 2018

Thanks @smitpatel

@mhosman For now you will need to define the filters in OnModelCreating directly

anpete on 2 Feb 2018

👍1

How about:

c# .Include(f => f.Thumbnails .Where(t => t.ThumbnailSize < maxThumbSize) .OrderBy(t => t.Rank) .Take(3)) .ThenInclude(...)

weitzhandler on 7 Mar 2018

👍17 😄2

Just want to throw a +1 onto this, would be excellent to have this feature I commonly hit this limitation.

stap123 on 13 Mar 2018

👍20

Also adding a +1 onto this feature. The ability to filter on include on a per query basis would be an excellent addition to EF Core

n123r on 21 Apr 2018

👍10

It's been a couple years now! Work on this feature please!!!!! Query on Includes. I'm going to Ignite this year, have it done by then or else. Or else what? Or else i'll just have to keep waiting.

conterio on 24 Apr 2018

😄29 👍25 ❤2

I also upvote this feature, and yes I know about global query filters and it doesn't solve any of my use cases.

orobert91 on 25 May 2018

👍17

+1 to this feature. Global filters doesn't solve my problems, i need to set condition on per-query basis

SonicGD on 14 Jun 2018

👍19 ❤7

Can we lock new comments on this issue? I'm keen to hear progress on this but don't want to be spammed by inane +1 comments.

If you wish to throw support behind this issue, thumbs up the OP and subscribe to notifications. The fourth comment here says that this will happen at some point, so either be patient or create a pull request.

ajbeaven on 21 Jun 2018

👍6 😄4

to OP, my current workaround is to use LINQ join

HairyPorker on 22 Jun 2018

I use the following workaround:

var entity = context.Table.First();
context.Entry(entity)
                        .Collection(parent => parent.myChildTable)
                        .Query()
                        .Where(child => child.amount > 100)
                        .ToList();

The collection of myChildTable inside the parent entity then gets populated with the filtered entities.

jmatter on 27 Jun 2018

👍1

@jmatter How do you use this approach, when you want to retrieve list of items instead of just the first one, and also, when the child table also has children? So, like:

class A { List ListOfB; bool IsDeleted; int JustANumber; }
class B { List ListOfC; bool IsDeleted; int SomethingElse; }
class C { ... }

What I would normally do is this:

public IEnumerable GetFilteredListOfA(int parameter)
{
IEnumerable result = MyDbContext.ClassA.Where(a => !a.IsDeleted && JustANumber >= parameter).Include(a => a.ListOfB.Where(b => !b.IsDeleted && SomethingElse >= parameter).Include(b => b.ListOFC....);

Right now I need to use the .Select and create new typed instances, but the code is quite long and I had to break my domain driven approach as e.g. the Id field now cannot be set to private, else all my items will have the Id of 0 instead of the real one. So, with Select it looks like this:

IEnumerable result = MyDbContext.ClassA.Where(a => !a.IsDeleted && JustANumber >= parameter)
.Select(a => new ClassA()
{
Id = a.Id,
ListOfB = a.ListOfB.Where(...).Select(b => new ClassB() { Id = b.Id, ListOFC = ...
}

KuzDeveloper on 12 Jul 2018

Consider the scenario where instead of:
query.Include(p => p.Property)
we have:
query.Include("Xxx.Yyy.Zzz")
only the chained property names.

How are we going to add the Where, OrderBy, Take and Skip filters?

We can try to create some Expression for our property name:

static Expression<Func<T, object>> ToLambda<T>(string propertyName)
{
    var parameter = Expression.Parameter(typeof(T));
    var property = Expression.Property(parameter, propertyName);
    var propAsObject = Expression.Convert(property, typeof(object));

    return Expression.Lambda<Func<T, object>>(propAsObject, parameter);
}

or even something like for nested properties:
var body = propertyName.Split('.').Aggregate<string, Expression>(param, Expression.PropertyOrField);

But this seems an obscure path to me.

YandyZaldivar on 25 Jul 2018

https://docs.microsoft.com/en-us/ef/core/querying/related-data

Tip
Entity Framework Core will automatically fix-up navigation properties to any other entities that were previously loaded into the context instance. So even if you don't explicitly include the data for a navigation property, the property may still be populated if some or all of the related entities were previously loaded.

What if we want to include only what we filter, and if we filter nothing for a specific query, include nothing?

Take into account that data obtained is potentially for being serialized over the network (to client apps), so it is not a good idea end up sending what we don't need.

YandyZaldivar on 25 Jul 2018

I do not understand why 'global query filters' should help me in case of filtering a navigation collection. The query depends from case to case.
I tried so much ways to filter included collections because i never thougth that this functionality not exists.
The EntityFramework Core is realy a cool thing!

Der-Kraken on 19 Sep 2018

I did not say it is easy to achieve this, but in its current state EF Core is not production ready. We used it for one of our microservices and it is causing a lot of performance issues to load too many unnecessary records when we already have 6-7 different filters available. Other than this, it would be an excellent tool.

KuzDeveloper on 19 Sep 2018

👍1

Anybody in favor of filtered includes should realize that it requires some dilemmas to be solved:

Should a partially loaded collection be marked as loaded?

Now that lazy loading is possible in EF core it's relevant to know whether a collection is loaded or not. A collection navigation property is marked as loaded when -

It is Included
It is loaded lazily
The Load statement (not relevant for now)

A collection is not marked as loaded when -

It is populated (either partially or fully) by relationship fixup

With lazy loading, when we have one entity, entity1, and we access entity.Children, EF will query the children from the database when the collection is not marked as loaded.

So what to do with a partially Include-ed collection? When it's not marked as loaded its content will be overwritten when lazy loading is triggered. Most developers will not expect that: they filtered the Include with a purpose.
However, when it's marked as loaded lazy loading will never populate the entire collection and developers who count on _that_ to happen will be puzzled. Also, when additional child entities get attached to the context otherwise, it will be impossible to tell which were part of the loaded collection.

What should the behavior be with repeated `Include` statements?

Suppose we have this statement:

var entities1 = context.Entities.Include(e => e.Children.Where(c => c.Active));

Later on in the same scope, this:

var entities2 = context.Entities.Include(e => e.Children.Where(c => !c.Active));

What do we expect an Entity's Children collection to contain now? The first statement attached the active children to the context, the second statement attached the inactive children. Lazy loading aside, relationship fixup commands that each Entity contains _all_ of its children, so entities1 and entities2 aren't essentially different any more, although a developer might expect them to be.

Or should the last Include statement be leading? But then, in view of relationship fixup, what to do with the attached active children? Detach them? OK, but what if they have changes? We can't simply ditch these changes, can we? So keep the change tracker aware of these changes (somehow) while still disposing the entity objects? I'm afraid that's would be a major departure of the current architecture.

I can't speak for the EF team, but I think they are aware of these dilemma's, which may explain their wariness of implementing this feature.

GertArnold on 23 Sep 2018

👍5

@GertArnold I am not sure I understand the dilemma.
In your sample,
The first statement should return all the entities including the children in which active == true;
The second statement should return all the entities including the children in which active == false;

At least this is what my use case most of the time, that EF unable to support filtering on the Include / children level; of course we could always workaround with Select or other awesome solutions that mentioned in this thread :)

HairyPorker on 2 Oct 2018

@HairyPorker

The second statement should return all the entities including the children in which active == false;

But it doesn't, that's my point. Sure, the SQL query will return only inactive children. But right after the statement _all_ children are tracked, so all Children collections of all entities will be populated fully if relationship fixup keeps working the way it does now. Keep in mind that entities1 and entities2 contain the same parent instances, because the statements run in the same scope, i.e. using one and the same context instance.

GertArnold on 2 Oct 2018

@GertArnold even that being the case, the ability to create such filtered query is a great plus for many scenarios. Internal performance improvements could achieved later with the corresponding clarification, one example is the recent introduction of GroupBy translation in 2.1.

YandyZaldivar on 2 Oct 2018

@GertArnold That is an interesting point of view and I think that could be a problem in a long living context instance (not a technical problem but more an organizational).

But I understand the DataContext as an UnitOfWork. An UnitOfWork is created for one task. After that it get's disposed. During one task I do not believe that the scenario you describe is relevant.

To response directly to the situation you described: I would expect that the last query wins (so it replaces the previous ones).

Der-Kraken on 4 Oct 2018

👍1

Currently I have can't filter the translations of children elements, so a complex result like this

return await _db.Products
                .Include(p => p.Documents)
                    .ThenInclude(a => a.Translations)
                .Include(p => p.ProductCategories)
                    .ThenInclude(pc => pc.Category)
                        .ThenInclude(c => c.Translations)
                .Include(p => p.ProductProperties)
                    .ThenInclude(pp => pp.Property)
                        .ThenInclude(c => c.Translations)
                .ToArrayAsync();

would look like this

// It's just en example. don't use similar code in real apps
var lang = context.Request.Headers["Accept-Language"];
return await _db.Products
                .Include(p => p.Documents)
                    .ThenInclude(a => a.Translations, t => lang.Equals(t.Language))
                .Include(p => p.ProductCategories)
                    .ThenInclude(pc => pc.Category)
                        .ThenInclude(c => c.Translations, t => lang.Equals(t.Language))
                .Include(p => p.ProductProperties)
                    .ThenInclude(pp => pp.Property)
                        .ThenInclude(prop => prop.Translations, t => lang.Equals(t.Language))
                .ToArrayAsync();

salaros on 16 Nov 2018

👍4 👎2

The following is strictly high-level - I have no idea how feasible it would be in reality.

Would it be better to allow transient and/or scoped query-filters that work in a similar way to the global ones?

i.e. within the scope of a single DbContext instance, filters can be defined.

Since most use-cases use Scoped DbContext injection - limiting the life of the DbContext to a single request in terms of MVC/WebAPI - this could be a workable compromise with developers keeping in mind @GertArnold's post above.

For transient filters, you could allow developers to do something like this...

var transientFilterContext = DbContext.GetTransientFilterContext();

then apply query filters to the transient context which will be a copy of the current state of DbContext at the point the above was called - but a separate instance - allowing a query to be made and completed with filters applied and the transient context would be garbage-collected after the method in which it was created is done.

Transient contexts could pass entities they retrieve back up to the parent context - allowing fixup to still work in the parent context without affecting the results of transient filtered queries.

hades200082 on 12 Dec 2018

👍1

I have a scenario where I need to query and filter on a entity _EntityA_ navigation property (which is a collection of _EntityB_), and I cannot query _EntityB_ directly because I need to paginate the results of _EntityA_. Basically I display a table of _EntityA_ with filtered sub-items _EntityB_

I think it is a common scenario, so is the team planning to implement this feature in a future release ?

adrien-constant on 30 Jan 2019

👍2 ❤1

@adrien-constant I agree, I've personally been in similar situations.
A classic scenario would be translated content. For example I have a product entity with property Documents, Accessories etc, all of those are available or not in certain languages. So by using conditional Include we could actually use the Accept-Language or whatever to dynamically filter English-only Documents, Accessories etc

Using projection in the scenario I described is a nightmare!

salaros on 1 Feb 2019

@salaros In your scenario are you not able to use Global Query Filters? As long as you retrieve Accept-Language in the context constructor and not in OnModelCreating directly (due to caching) it should work even with navigation properties.

In Announcing EF Core 2.0 Preview 1 under Global query filters:

Filters are applied automatically when queries retrieve data of specific types directly as well as through navigation properties, e.g. using the Include() method.

mguinness on 1 Feb 2019

@salaros In your scenario are you not able to use Global Query Filters? As long as you retrieve Accept-Language in the context constructor and not in OnModelCreating directly (due to caching) it should work even with navigation properties.

I'm not sure I got your question / answer? Are you stating I can define filters outside the OnModelCreating? Requests to the same endpoint can be sent with different values of 'Accept-Language' header (you can easily reproduce it with Postman, Insomnia etc) and depending on its value I would have to query database differently.
Since I use DB context pooling for performance reasons I cannot rely on the fact that the DbContext instance I'm using had the correct language(s) injected upon that particular instance creation, using IHttpContextAccessor or whatever you are suggesting to use.

IMO it would be much safer rely on conditional Include which will translate to a JOIN with multiple conditions: e.g. .Include(p => p.Documents, d => dynamicLangs.Contains(d.Language)) would translate to something like SELECT * FROM products LEFT JOIN documents ON products.id = documents.productid AND documents.language IN ('en', 'es')

I know it might lead to some sacrifices, such as it would make Product.Documents property always lazy-loaded if accessed directly or whatever, but I think we could deal with such drawback.

salaros on 3 Feb 2019

@salaros Yes as stated in Limitations you can't use Global Query Filters in that manner if you intend to use DbContext Pooling (which you hadn't previously mentioned).

Avoid using DbContext Pooling if you maintain your own state (for example, private fields) in your derived DbContext class that should not be shared across requests.

Also worth reading What's the performance benefit of pooling DbContexts to see the nuanced performance gain. I'm not saying that you don't see a benefit, but for others reading this to understand the tradeoff.

mguinness on 3 Feb 2019

Is a solution to just make global filters be .. local filters? Allowing them to be turned on and off on the go?

wisepotato on 26 Feb 2019

👍2

Is a solution to just make global filters be .. local filters? Allowing them to be turned on and off on the go?

You can call me whatever you want, just make the scenario I described above possible 😄

salaros on 26 Feb 2019

I am now forced to execute the query in full and then for loop over every sub item to filter what i need. I mean, its... suboptimal.

wisepotato on 26 Feb 2019

👍2 😄1

This is a VERY BASIC and SUPER must-have feature.

mhosman on 27 Feb 2019

👍18 ❤4

This has been a good 4 years since this was suggested. Is there any way to get this going? What are the roadblocks?

wisepotato on 1 Mar 2019

@wisepotato The only "roadblocks" are lack of resources and a large number of high priority feature requests.

ajcvickers on 1 Mar 2019

👍1

Fair enough, might make a pull request if i ever find 50 free hours ;)

wisepotato on 2 Mar 2019

👎1

Personally I'm waiting for this feature about 3 years!!!

papmodern on 2 Mar 2019

👍9

This really is just a fundamental feature IMO - every time I'm touching EF, I'm sort of landing here again :)

hardcodet on 19 Mar 2019

👍8

I don't understand why I should use EF Core while it lacks a simple and fundamental feature like this. Honestly, I'm new to .NET and started to use EF Core in a project but I didn't think that it is so incomplete. You guys need to take Eloquent as an example to yourselves. EF Core is unusable to me as it is now.

division-by-zero on 23 Mar 2019

👍4

Has it ever been considerated to sponsor developers to develop highly demanded features that the core team do not have time to implement ?
I work on pretty large projects for companies that would pay to have this kind of features :)

adrien-constant on 26 Mar 2019

I'm waiting for this feature since EF was born. I really don't understand why this is not a top priority!

giarico on 26 Mar 2019

😕1

While it seems unlikely this will be included in EF Core 3.0, the good news is that the release schedule for EF Core will no longer be in-sync with the .NET Core release schedule which presumably means that the team will have more flexibility doing incremental releases.

Whilst this feature isn't labeled "High Priority" I would be interested to hear from the team the level of effort required for this feature, i.e. has there been a design discussion and if so were there any perceived implementation issues like work required in each provider.

mguinness on 27 Mar 2019

@mguiness - See https://github.com/aspnet/EntityFrameworkCore/issues/1833#issuecomment-423820442
That post describes the major design decisions to be made for filtered include. The actual work is less complicated than making those decisions. Likely it will not require work in providers. The core query should be able to create an expression tree which would work similar to normal include but with extra filter condition. There would be some more additional code required to understand and parse the Where clause.

smitpatel on 27 Mar 2019

👍3

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.

johnkwaters on 27 Mar 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.

ajcvickers on 28 Mar 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?

denious on 28 Mar 2019

👀2

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:
```c#
public class ParentTable
{
public int ID { get; set; }
public List Children { get; set; }
}

My EF Query:
```c#
// 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.

kakins on 1 Apr 2019

Would love to see this implemented in the near future!

maurei on 3 Apr 2019

👍20 🎉5 😕2

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.

dominikfoldi on 12 Apr 2019

👍7 🚀4 😕3

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

TiagoCunhaF on 18 Apr 2019

👍3 😕2

Throwing my vote in for this as well.

dblood2 on 22 Apr 2019

😕2

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

OndrejValenta on 23 Apr 2019

👍7 ❤4 👀2

Throwing my vote in on this too...

stevieTheCoder on 25 Apr 2019

😕2

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

popcatalin81 on 25 Apr 2019

👍5 😕1

@popcatalin81 I understand there are some design decisions that need to be made by the team before work can commence, see https://github.com/aspnet/EntityFrameworkCore/issues/1833#issuecomment-423820442 for more detail.

mguinness on 25 Apr 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.

popcatalin81 on 25 Apr 2019

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

For now I see several solutions:

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

Selecting everything and filtering (slow but in most cases it will be enough for a proof of concept)
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
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]

rafalschmidt97 on 26 Apr 2019

😕2 👍1

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

hades200082 on 26 Apr 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/

salaros on 26 Apr 2019

👍4

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

andriysavin on 28 Apr 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();

MoazAlkharfan on 30 Apr 2019

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

smitpatel on 30 Apr 2019

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

hades200082 on 30 Apr 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.

kvijayan-aliera on 4 May 2019

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

garfbradaz on 21 Jun 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.

divega on 22 Jun 2019

👀3 👍3

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

wisepotato on 24 Jun 2019

👍6

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

divega on 25 Jun 2019

👍4

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.

yannickvidal on 8 Jul 2019

😕1

@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

wisepotato on 9 Jul 2019

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

TheMisir on 14 Jul 2019

😕18 👍4

Kindly can Entity framework team advice what is the current status of this issue

MahmoudH96 on 21 Jul 2019

@MahmoudH96 This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

ajcvickers on 21 Jul 2019

😕7

@MahmoudH96 can you please include a link to the backlog or uservoice site so the community can vote for backlog items?

OzBob on 22 Jul 2019

@ajcvickers This issue and thread has been around for 4 years and is still actively commented on after all that time ... that suggests that it is a highly sought after feature yet the EF team have made zero progress on providing it in 4 years.

Is there somewhere else that the community can vote on this issue to raise its priority? If not, then can you explain how its seemingly low priority was arrived at?

hades200082 on 22 Jul 2019

👍8

Update:

See https://data.uservoice.com/forums/72025--closed-entity-framework-core-feature-suggestions

It would seem that the community has already voted it to the #2 most popular feature request on there... yet you're closing the UserVoice down at the end of this month.

How much more does the community have to do to show that this should be a priority feature?

hades200082 on 22 Jul 2019

👍10 😕7 👀3

@hades200082 @OzBob The way to vote for this issue is to +1 👍 it at the top of this page. UserVoice has not been used for some time, as is stated on that page.

We have a small team working on EF--currently for the 3.0 release, we have four engineers working on EF Core, and one working on EF6. Given the size of the team, we are not able to work on all the things that need working on, even some of the most requested features. We have a document that describes the planning process in more detail. This issue is currently the third most voted issue on the EF repo, and this will be taken into account when planning post 3.0 work.

We have and will continue to communicate these numbers up the management chain. It is then a strategic decision from Microsoft management whether to increase resources working on EF, as opposed to using those resources for other projects that may potentially have bigger immediate ROI or strategic impact. Currently the feedback we have received on this is that the current level of resourcing is sufficient, and the relatively slow progress on EF that results is acceptable from a strategic perspective.

On the EF team, we will continue to advocate for more resources on EF, and we will continue to do our best to move EF Core forward to the best of our ability.

ajcvickers on 22 Jul 2019

👍22 ❤9 😕5 👀1

Thank you for your honest words. I am surprised that only 4 Microsoft engineers working currently on EntityFrameworkCore. Of course I understand that 4 people never can work on that many open issues and features.

I had the impression that EFCore is one of the most important features for Asp.Net Core because in many tutorials for ASP you learn how to use EFCore. So I'm a bit of shocked that MS internally says that EFCore is not high priority. 4 people are nothing for such a big company.

Because I begin missing some basic features in Asp.Net Core too (like translation of DataAnnotations see: https://github.com/dotnet/corefx/issues/25128) I get the feeling that the whole Dot.Net Core ecosystem has no high priority. I fear that MS would say in a year that it will no longer be developed.

By the way. @wisepotato has addressed it before. I think many community members would like to contribute to Dot.Net Core (me too). But where to start? You tried to explain it a bit but I still don’t know what is to do.
Of course you have only 4 people for work. But if you establish an easy and funny way for new community members to contribute I think there must be much more resources which will help.

Der-Kraken on 2 Aug 2019

👍1

@Der-Kraken I you would like to contribute (as I do) a good place to start is with the Good First Issues

The build guide is helpful and of course you should be very familiar with the Contributing guide

Start with something simple just to understand the workflow.

ErikEJ on 2 Aug 2019

Thank you for your honest words. I am surprised that only 4 Microsoft engineers working currently on EntityFrameworkCore. Of course I understand that 4 people never can work on that many open issues and features.

I had the impression that EFCore is one of the most important features for Asp.Net Core because in many tutorials for ASP you learn how to use EFCore. So I'm a bit of shocked that MS internally says that EFCore is not high priority. 4 people are nothing for such a big company.

I think you are intentionally misinterpreting this, to provoke a more favorable answer for your standpoint? I can't remember anywhere anyone from Microsoft stating, that EF Core isn't high priority. The only things stated here is that this feature isn't high priority and there are higher priority features to be done first, mostly because most of the issues people attempt to solve with this feature can be done (cleaner) in other ways (read the whole issue, there have been named).

What you and most of the other people here failed to provide are use cases which can't be done with the current state and APIs.

Because I begin missing some basic features in Asp.Net Core too (like translation of DataAnnotations see: dotnet/corefx#25128) I get the feeling that the whole Dot.Net Core ecosystem has no high priority. I fear that MS would say in a year that it will no longer be developed.

Well, while its correct that there is no built-in localization of DataAnnotations assembly, you can localize it yourself, so not a deal breaker. Something like this should actually work.

TsengSR on 2 Aug 2019

👎5

@Der-Kraken With regard to your questions on contributing, you make some very good points. Right now we are very focused on shipping 3.0, but once that starts to wrap up I will be spending some amount of time on documentation and repo management. Two things that I hope to work on and will help here are:

Finding more candidates for the good-first-issue label
Writing some blog posts (or official docs) like I wrote a long time ago for EF6: https://blog.oneunicorn.com/2012/07/19/so-you-want-to-contribute-to-ef-part-1-introduction/

@Der-Kraken @TsengSR I'm not going to comment on specific priorities, but I can assure you that .NET Core as a whole is very much a high priority for Microsoft, and is continuing to gain momentum both internally and externally.

ajcvickers on 2 Aug 2019

👍9

@TsengSR you are wrong, many people gave use cases easy to reproduce in this thread, maybe you didn't read all of the posts.

I gave a few months ago a basic use case I had for a project :

I have a scenario where I need to query and filter on a entity EntityA navigation property (which is a collection of EntityB), and I cannot query EntityB directly because I need to paginate the results of EntityA. Basically I display a table of EntityA with filtered sub-items EntityB

If you think creating a view is cleaner, yeah, maybe you think it's useless to be able to filter an include (that is to say a SQL join). That's something very easy to write with SQL code and impossible with the current version of EF Core API.

adrien-constant on 22 Aug 2019

👍4

Could @jonathanmagnan or @waqasm78 could help?
Could an EF merge request be created from the EFplus(EF+) IncludeFilter or the IncludeOptimized features?

OzBob on 2 Sep 2019

Oh man i'd love to see this soon. So basic and useful. <3 Thanks for your work, can't wait for this feature to be added.

Fieel on 27 Sep 2019

👍3 😕1 👎1

I guess I'm suffering the same problem now. I was always trying to avoid this kind of filters but sometime you can't.

In my example I'm getting always the LaneClosureEvent, LaneClosureClosedRoad ICollection with Count = 0 at the Where level but there is data later on the Select level.

Any suggestions??

QUERY:
return await _context.LaneClosure
.Where(lc => _MatchLaneClosure_(lc, searchCriteria))
.Include(lc => lc.Requestor)
.Include(lc => lc.LaneClosureEvent)
.ThenInclude(lce => ((LaneClosureEvent)lce).LaneClosureEventGeo)
.Include(lc => lc.LaneClosureEvent)
.ThenInclude(lce => ((LaneClosureEvent)lce).LaneClosureEventDirectionType)
.ThenInclude(lcedt => ((LaneClosureEventDirectionType)lcedt).DirectionType)
.Include(lc => lc.LaneClosureEvent)
.ThenInclude(lce => ((LaneClosureEvent)lce).LaneClosureEventLocationType)
.ThenInclude(lcelt => ((LaneClosureEventLocationType)lcelt).LocationType)
.Include(lc => lc.LaneClosureClosedRoad)
.ThenInclude(lccr => ((LaneClosureClosedRoad)lccr).Agency)
.Include(lc => lc.LaneClosureClosedRoad)
.ThenInclude(lccr => ((LaneClosureClosedRoad)lccr).County)
.Include(lc => lc.LaneClosureClosedRoad)
.ThenInclude(lccr => ((LaneClosureClosedRoad)lccr).City)
.Include(lc => lc.LaneClosureClosedRoad)
.ThenInclude(lccr => ((LaneClosureClosedRoad)lccr).Road)
.Select(lc => _mapper.GetModel(lc)).ToListAsync();

private bool _MatchLaneClosure_(LaneClosure laneClosure, LaneClosureSearchCriteriaDto filters)
{
return laneClosure.StatusId == 3 &&
(
filters.ClosureNumber == laneClosure.Number ||
(
(!filters.ClosureNumber.HasValue || laneClosure.Number.ToString().Contains(filters.ClosureNumber.Value.ToString())) &&
(!filters.BeginingDate.HasValue || laneClosure.BeginingDate >= filters.BeginingDate.Value) &&
(!filters.FinishingDate.HasValue || laneClosure.FinishingDate <= filters.FinishingDate.Value) &&
(!filters.Vic.HasValue || laneClosure.Vic == filters.Vic.Value) &&
(filters.AgencyIds == null || filters.AgencyIds.Length == 0 || laneClosure.LaneClosureClosedRoad.Any(closedRoad => filters.AgencyIds.Any(agencyId => agencyId == closedRoad.AgencyId))) &&
(filters.CountyIds == null || filters.CountyIds.Length == 0 || laneClosure.LaneClosureClosedRoad.Any(closedRoad => filters.CountyIds.Any(countyId => countyId == closedRoad.CountyId))) &&
(filters.CityIds == null || filters.CityIds.Length == 0 || laneClosure.LaneClosureClosedRoad.Any(closedRoad => filters.CityIds.Any(cityId => cityId == closedRoad.CityId))) &&
(filters.RoadIds == null || filters.RoadIds.Length == 0 || laneClosure.LaneClosureClosedRoad.Any(closedRoad => filters.RoadIds.Any(roadId => roadId == closedRoad.RoadId)))
)
);
}

amedriveroperez1984 on 19 Oct 2019

Not sure if it was mentioned but EF Plus can be used as a workaround for now.

alexb5dh on 31 Oct 2019

I was lately looking a bit closer on how this is realized in the ASP.net OData framework as OData supports filtered includes with an API syntax like /users?$expand=Trips($filter=Budget gt 1000). After some further tests I realized that the main infrastructure of EFCore seems to already support it. Maybe some EFCore members can shed some light on the implications of this system but theoretically I think it should be possible to already today build a syntax on top of this behavior:

When you explicitly make selects to new objects you can apply any filter to the navigation properties as you like there. As of today this requires you to manually express all the properties of an entity but this could be automated:

using var context = new UsersDbContext();
var usersWithFilteredTrips = 
    await context.Users.Where(u=>u.IsActive)
                       .Select(u=> new User 
                        { 
                            // normal properties
                            Id = u.Id, 
                            Username = u.Username, 
                            IsActive = u.IsActive, 

                            // navigation properties
                            Trips = u.Trips.Where(t => t.Budget > 1000).ToList()
                        }).ToArrayAsync()

Initially I had the fear that EF might load the whole tables into memory and do the filtering there, but actually it generated a JOIN with sub-select. Something comparable to:

  SELECT [u].[Id], [u].[Username], [t].[Id], [t].[UserId], [t].[Budget], [t].[Destination], [t].[Time]
      FROM [Users] AS [u]
      LEFT JOIN (
          SELECT [t].[Id], [t].[UserId], [t].[Budget], [t].[Destination], [t].[Time]
          FROM [Trips] AS [t]
          WHERE [s].[Budget] > 1000
      ) AS [t] ON [u].[Id] = [t].[UserId]
      WHERE ([u].[IsActive] = 1)
      ORDER BY [u].[Id], [t].[Id]

A .Include currently results in a direct join as you would expect:

SELECT [u].[Id], [u].[Username], [t].[Id], [t].[UserId], [t].[Budget], [t].[Destination], [t].[Time]
FROM [Users] AS [u]
LEFT JOIN [Trips] AS [t] ON [u].[Id] = [t].[UserId]
WHERE ([u].[IsActive] = 1)
ORDER BY [u].[Id], [t].[Id]

Of course the JOIN with additional ON clauses might be nice and the database servers are better capable to optimize and hence might be faster, but the subselect gives you really the full power on selecting what you want and I guess we do not have to fear bugs in a new system.

Danielku15 on 12 Nov 2019

👍5 😕4 👀2

@Danielku15 we all advocate for a conditional Include, because your code example is hard to maintain. Just imagine adding User.Nickname or User.PreferredLanguage properties. You'd have to refactor all the projections to add those properties.

salaros on 13 Nov 2019

👍2 😄1

@alexb5dh yes it has been mentioned once or thrice.
The zzz/EF+ guys progressed into EFCore 3 preview 9 in Sep 2019 with a working IncludeFilter. https://github.com/zzzprojects/EntityFramework-Plus/issues/515
@smitpatel and the team have definitely noticed the number of upvotes this issue has received.

OzBob on 18 Nov 2019

👍1

@salaros I think you misunderstood me. With my comment I meant that the foundation of EF core somehow already supports filtered includes and the related SQL generation also seems correct. I also still strongly vote for a .Include() a-like syntax to activate a filtered include with a more developer friendly approach. The projections I highlighted as "manual" possibility "simply" needs to be automated in a special .IncludeFiltered() system and you can already have a feature like requested in this issue. Having it in the main library should definitly be the goal.

Danielku15 on 18 Nov 2019

👍6 ❤1

@Danielku15 Sure, OData does support filtered include. Filtering Include isn't the problem. The big difference is that OData is stateless, while EF core isn't. That raises a couple of issues that are hard to deal with. Please see https://github.com/aspnet/EntityFrameworkCore/issues/1833#issuecomment-423820442

GertArnold on 21 Nov 2019

👍1

@GertArnold there is .AsNoTracking() for that. IMO there are already plenty of conventions, so adding yet another one is not a big deal. Everybody would know that using Include(prop, filter) on a DbSet equals calling AsNoTracking + Include with some additional join conditions applied.

salaros on 21 Nov 2019

👍2 😕1

Fixed in 21b9a35db594f7a383e855f922babbe54b3d38c5

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.

maumar on 24 Mar 2020

👍56 🎉42 ❤35 🚀14

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.

Trolldemorted on 27 May 2020

👍1

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

TheMisir on 27 May 2020

👍1

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

roji on 27 May 2020

🎉5 👎2

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

kosalsean on 19 Oct 2020

🚀1 😕1

Efcore: Support filtered Include

Please learn about global query filters

Original issue:

Most helpful comment

All 189 comments

Should a partially loaded collection be marked as loaded?

What should the behavior be with repeated `Include` statements?

Related issues

Efcore: Support filtered Include

Please learn about global query filters

Original issue:

Most helpful comment

All 189 comments

Should a partially loaded collection be marked as loaded?

What should the behavior be with repeated Include statements?

Related issues

What should the behavior be with repeated `Include` statements?