169

I'm trying to filter on the initial query. I have nested include leafs off a model. I'm trying to filter based on a property on one of the includes. For example:

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Include(blog => blog.Posts)
            .ThenInclude(post => post.Author)
        .ToList();
}

How can I also say .Where(w => w.post.Author == "me")?

4
  • I'm facing the same issue, does EF Core 2.xx implement this?
    – Hiep Lam
    Oct 15, 2018 at 7:24
  • There are now global query filters, but this is only helpful if they're pretty standard across all queries. You can disable them on a query-by-query basis so it can work for simpler things. learn.microsoft.com/en-us/ef/core/querying/filters Feb 13, 2019 at 19:29
  • I have solved my issue using Linq to Entities over EF core DBSet(s) Feb 22, 2019 at 14:01
  • 4
    But does that still bring back the whole table and then filter it in memory on the web server?
    – tnk479
    Aug 13, 2019 at 20:00

9 Answers 9

269

Entity Framework core 5 is the first EF version to support filtered Include.

How it works

Supported operations:

  • Where
  • OrderBy(Descending)/ThenBy(Descending)
  • Skip
  • Take

Some usage examples (from the original feature request and the github commmit) :

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.

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

or

context.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)

Another important note:

Collections included using new filter operations are considered to be loaded.

That means that if lazy loading is enabled, addressing one customer's Orders collection from the last example won't trigger a reload of the entire Orders collection.

Also, two subsequent filtered Includes in the same context will accumulate the results. For example...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...followed by...

context.Customers.Include(c => c.Orders.Where(o => o.IsDeleted))

...will result in customers with Orders collections containing all orders.

Filtered Include and relationship fixup

If other Orders are loaded into the same context, more of them may get added to a customers.Orders collection because of relationship fixup. This is inevitable because of how EF's change tracker works.

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...followed by...

context.Orders.Where(o => o.IsDeleted).Load();

...will again result in customers with Orders collections containing all orders.

The filter expression

The filter expression should contain predicates that can be used as a stand-alone predicate for the collection. An example will make this clear. Suppose we want to include orders filtered by some property of Customer:

context.Customers.Include(c => c.Orders.Where(o => o.Classification == c.Classification))

It compiles, but it'll throw a very technical runtime exception, basically telling that o.Classification == c.Classification can't be translated because c.Classification can't be found. The query has to be rewritten using a back-reference from Order to Customer:

context.Customers.Include(c => c.Orders.Where(o => o.Classification == o.Customer.Classification))

The predicate o => o.Classification == o.Customer.Classification) is "stand alone" in the sense that it can be used to filter Orders independently:

context.Orders.Where(o => o.Classification == o.Customer.Classification) // No one would try 'c.Classification' here

This restriction may change in later EF versions than the current stable version (EF core 5.0.7).

What can (not) be filtered

Since Where is an extension method on IEnumerable it's clear that only collections can be filtered. It's not possible to filter reference navigation properties. If we want to get orders and only populate their Customer property when the customer is active, we can't use Include:

context.Orders.Include(o => o.Customer.Where( ... // obviously doesn't compile

Filtered Include vs filtering the query

Filtered Include has given rise to some confusion on how it affects filtering a query as a whole. The rule of the thumb is: it doesn't.

The statement...

context.Customers.Include(c => c.Orders.Where(o => !o.IsDeleted))

...returns all customers from the context, not only the ones with undeleted orders. The filter in the Include doesn't affect the number of items returned by the main query.

On the other hand, the statement...

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders)

...only returns customers having at least one undeleted order, but having all of their orders in the Orders collections. The filter on the main query doesn't affect the orders per customer returned by Include.

To get customers with undeleted orders and only loading their undeleted orders, both filters are required:

context.Customers
    .Where(c => c.Orders.Any(o => !o.IsDeleted))
    .Include(c => c.Orders.Where(o => !o.IsDeleted))

Filtered Include and projections

Another area of confusion is how filtered Include and projections (select new { ... }) are related. The simple rule is: projections ignore Includes, filtered or not. A query like...

context.Customers
    .Include(c => c.Orders)
    .Select(c => new { c.Name, c.RegistrationDate })

...will generate SQL without a join to Orders. As for EF, it's the same as...

context.Customers
    .Select(c => new { c.Name, c.RegistrationDate })

It gets confusing when the Include is filtered, but Orders are also used in the projection:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        c.Name, 
        c.RegistrationDate,
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

One might expect that OrderDates only contains dates from undeleted orders, but they contain the dates from all Orders. Again, the projection completely ignores the Include. Projection and Include are separate worlds.

How strictly they lead their own lives is amusingly demonstrated by this query:

context.Customers
    .Include(c => c.Orders.Where(o => !o.IsDeleted))
    .Select(c => new 
    { 
        Customer = c, 
        OrderDates = c.Orders.Select(o => o.DateSent)
    })

Now pause for a moment and predict the outcome...

The not so simple rule is: projections don't always ignore Include. When there is an entity in the projection to which the Include can be applied, it is applied. That means that Customer in the projection contains its undeleted Orders, whereas OrderDates still contains all dates. Did you get it right?

3
  • Great answer, but this last part got me. You say projections ignore Include, but where is this documented, and how can I get round it. I like the filter include, but to reduce the amount of data in the produced SQL, I use a projection to only return what I need. I need the child collection to be filtered, so do I need to filter a second time?
    – Peter Kerr
    Nov 15, 2021 at 19:16
  • 1
    @PeterKerr In that case you can filter in the projection, like new { root.Property1, Children = root.ChildCollection.Where(...).Select(c => new { c.ChildProperty1, ... }). There used to be a paragraph on ignored Includes in EF core's documentation (https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager) but I can't find it any more. However, it's a logical consequence: if a projection doesn't consist of entities, where should an Include go? Nov 15, 2021 at 19:29
  • 1
    Projections ignoring the .Include is going to lead to so many hours wasted of debugging like me. Thanks to your answer I solved the problem instead of doing .Select(c => c.Orders) I did ....FirstOrDefault() followed by orders = c.Orders for which the .Include was applied to the Orders.
    – MDave
    Feb 22, 2022 at 17:38
31

Not doable.

There is an on-going discussion about this topic: https://github.com/aspnet/EntityFramework/issues/1833

I'd suggest to look around for any of the 3rd party libraries listed there, ex.: https://github.com/jbogard/EntityFramework.Filters

4
  • 3
    These are not for EF Core. With EF6 its doable with entityframework-plus.net Nov 22, 2018 at 14:30
  • Looks like that repo got migrated to EF core so debate continues at github.com/aspnet/EntityFrameworkCore/issues/1833 Feb 22, 2019 at 15:40
  • 1
    @PeterHurtony, EF Plus now supports IncludeFilter in EF Core Jul 19, 2019 at 10:34
  • 1
    Citing EF Plus as a solution just reinforces the answer's point about looking at third party libraries. EF Plus should probably be appended to the answer, though, since it's such a huge feature library that solves many problems. Sep 4, 2019 at 14:52
23

You can also reverse the search.

{
    var blogs = context.Author
    .Include(author => author.posts)
        .ThenInclude(posts => posts.blogs)
    .Where(author => author == "me")
    .Select(author => author.posts.blogs)
    .ToList();
}
1
  • 1
    but if Author is a ownedtype that there is no context.Author?
    – phiree
    Sep 13, 2021 at 9:07
7

Not sure about Include() AND ThenInclude(), but it's simple to do that with a single include:

var filteredArticles = 
    context.NewsArticles.Include(x => x.NewsArticleRevisions)
    .Where(article => article.NewsArticleRevisions
        .Any(revision => revision.Title.Contains(filter)));

Hope this helps!

1
  • 4
    Wouldnt that include every revision when one of them fits to the filter?
    – liqSTAR
    Sep 22, 2020 at 13:40
6

Although it's (still in discussion) not doable with EF Core, I've managed to do it using Linq to Entities over EF Core DbSet. In your case instead of:

var blogs = context.Blogs
        .Include(blog => blog.Posts)
            .ThenInclude(post => post.Author)
        .ToList()

.. you'll have:

await (from blog in this.DbContext.Blogs
           from bPost in blog.Posts
           from bpAuthor in bPost.Author
           where bpAuthor = "me"
           select blog)
.ToListAsync();
1
  • That is the most decent answer.
    – pantonis
    Feb 10, 2021 at 10:37
2

I used below package Use Z.EntityFramework.Plus

IncludeFilter and IncludeFilterByPath two methods are which you can use.

var list = context.Blogs.IncludeFilter(x => x.Posts.Where(y => !y.IsSoftDeleted))
                .IncludeFilter(x => x.Posts.Where(y => !y.IsSoftDeleted)
                    .SelectMany(y => y.Comments.Where(z => !z.IsSoftDeleted)))
                .ToList();

Here is the example https://dotnetfiddle.net/SK934m

Or you can do like this

GetContext(session).entity
                .Include(c => c.innerEntity)
                .Select(c => new Entity()
                {
                    Name = c.Name,
                    Logo = c.Logo,
                    InnerEntity= c.InnerEntity.Where(s => condition).ToList()
                })
6
  • Just curious, can you indicate if this library has any added value when it comes to filtered Include? Does it do better than EF's filtered Include? Mar 26, 2021 at 21:45
  • Yes, This library gives us filtered nested list instead of whole data, In this case it is good.
    – CodeByAk
    Apr 7, 2021 at 2:55
  • But that's what filtered Include also does. Apr 7, 2021 at 6:06
  • If you want to get filtered list of filtered list, suppose teacher students connection is there, then if you want to get the students who has scrored the marks in between 50-60 then above include filter can be used.
    – CodeByAk
    Apr 8, 2021 at 16:14
  • 1
    I revert to my previous comment. Apr 8, 2021 at 17:12
0

Interesting case and it worked!!

If you have table/model user(int id, int? passwordId, ICollection<PwdHist> passwordHistoryCollection) where collection is history of passwords. Could be many or none.

And PwdHistory(int id, int UserId, user User). This has a quasi relationship via attributes.

Needed to get user, with related current password record, while leaving historical records behind.


User user = _userTable
    .Include(u => u.Tenant)
    .Include(u => u.PwdHistory.Where(p => p.Id == p.PwdUser.PasswordId)) 
    .Where(u => u.UserName == userName)
    .FirstOrDefault();

Most interesting part is .Include(u => u.PwdHistory.Where(p => p.Id == p.PwdUser.PasswordId))

  • works with user and many passwords
  • works with user and no passwords
  • works with no user
2
  • All pretty vague. What is a "quasi relationship"? What's the meaning of "works"? To me, it all seems expected and documented behavior. Dec 26, 2023 at 10:24
  • @GertArnold you see there ^^, there is no true relationship in DB because it is a circular reference. One needs user id, another needs password id in reverse. But EF using models decorated with attributes makes fine work of it without hustle. As I say, this is an interesting example
    – T.S.
    Jan 2 at 14:24
-1

This task can be accomplished with two queries. For example:

var query = _context.Employees
            .Where(x =>
                x.Schedules.All(s =>
                    s.ScheduleDate.Month != DateTime.UtcNow.AddMonths(1).Month &&
                    s.ScheduleDate.Year != DateTime.UtcNow.AddMonths(1).Year) ||
                (x.Schedules.Any(s =>
                     s.ScheduleDate.Month == DateTime.UtcNow.AddMonths(1).Month &&
                     s.ScheduleDate.Year == DateTime.UtcNow.AddMonths(1).Year) &&
                 x.Schedules.Any(i => !i.ScheduleDates.Any())));

        var employees = await query.ToListAsync();

        await query.Include(x => x.Schedules)
            .ThenInclude(x => x.ScheduleDates)
            .SelectMany(x => x.Schedules)
            .Where(s => s.ScheduleDate.Month == DateTime.UtcNow.AddMonths(1).Month &&
                        s.ScheduleDate.Year == DateTime.UtcNow.AddMonths(1).Year).LoadAsync();
2
  • 1
    I think the SelectMany causes ignored Includes here. Check if ScheduleDates are really included. Schedules are, because they are in the SelectMany, not because of the Include. Oct 8, 2020 at 12:51
  • 1
    Checked out ScheduleDates. count = 11. Therefore, everything is included. If you remove .ThenInclude, then nothing is included and the count is 0
    – Acid_st
    Oct 9, 2020 at 15:11
-1

We can use by extension

public static IQueryable<TEntity> IncludeCondition<TEntity, TProperty>(this IQueryable<TEntity> query, Expression<Func<TEntity, TProperty>> predicate, bool? condition) where TEntity : class where TProperty : class
{
    return condition == true ? query.Include(predicate) : query;
}

Usage;

_context.Tables.IncludeCondition(x => x.InnerTable, true)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.