Filtering On ThenInclude, with EntityFrameworkPlus IncludeFilter

Question

I am trying to filter three child levels down and find only child elements where PropertyMailingAddress.Status== True.

How do I convert filter three levels down and conduct nested filtering with EntityFrameworkPlus IncludeFilter? What is the most efficient way?

Class Structure is nested like this:

1. Property
2. PropertyParty
3. Party
4. PartyMailingAddress
5. PropertyMailingAddress <--- Status should equal true (Any grandchild PropertyMailingAddress nodes with Status == False, should be removed from this nested grandchild branch, keep the PropertyMailingAddress nodes which are True)

This original way is not working:

var result = await db.Property.Include(pm => pm.PropertyParty)
                    .Include(pm => pm.PropertyParty)
                    .ThenInclude(x => x.Party)
                    .ThenInclude(x => x.PartyMailingAddress)
                    .ThenInclude(x => x.PropertyMailingAddress)
                    .Where(a => a.PropertyParty.Any(x => (x.Party.PartyMailingAddress.Any(z => z.PropertyMailingAddress.Any(h => h.Status == true))))).ToListAsync();

Trying efficient way with Entity Framework Plus: Does the last line have to be relink joined again with nested wheres above, or is below fine?

var result = await db.Property.Include(pm => pm.PropertyParty)
                    .Include(pm => pm.PropertyParty)
                    .ThenInclude(x => x.Party)
                    .ThenInclude(x => x.PartyMailingAddress)
                    .ThenInclude(x => x.PropertyMailingAddress)
                    .IncludeFilter(y => y.PropertyMailingAddress.Where(z => z.Status == true)).ToListAsync();

*We will require all the nested entities, while filtering,

Currently using Net Core 2.2

Mccusker

Could you append your class definition? – Gab

Answer 1

You cannot mixte Include with IncludeFilter.

In EF Core, the IncludeFilter should automatically add all paths.

We don't have the class definition so it makes it hard to know exactly the relationship but the query should look like this:

var result = await db.Property.IncludeFilter(pm => pm.PropertyParty
                                .Select(x => x.Party)
                                .SelectMany(x => x.PartyMailingAddress)
                                .SelectMany(x => x.PropertyMailingAddress.Where(z => z.Status == true)).ToListAsync();

The filtering is done in the database. So be careful with EF Core 2.x, as their have a client side evaluation they removed in EF Core 3.x which was causing some problem.

If you need more help, just provide a runnable solution in our issue tracker: https://github.com/zzzprojects/EntityFramework-Plus/issues

Jesse

hi Jon, I will need the related entities, With the filter in the answer, your answer just contains the filter, thanks – Mccusker