How to retrieve nested entities using C# MVC with conditions?

Question

Let's say I have a three-level hierarchy of entities like this Continent -> Country -> City.

I'm currently using code-first to generate the database.

I have a List of Continent entities, each continent entity having a List of Countries, each country having having a list of Cities.

All entites have a Deleted boolean property.

Using dbContext, how can I retrieve all non-deleted items from the database in this same structure, ie, in the end I'll just have a List of continents (higher level).

Since all entities are related, when I do

var allContinents = context.Continents.ToList();

I get a list of all entities I have, but I don't know how to do a good-looking LINQ statement to filter by my Deleted property.

What I am doing right know is bring everything into memory and remove the deleted items, but I don't want to bring useless data.

Any help is appreciated. Thanks

Are you looking for var allContinents = context.Continents.Where(x => x.Deleted != true).ToList() ? — Ryan Intravia, May 27 '16 at 18:47
@RyanIntravia it sounds like he wants the child entities to be filtered as well. — DVK, May 27 '16 at 18:50
Possible duplicate of [How to filter nested collection Entity Framework objects?](http://stackoverflow.com/questions/7079378/how-to-filter-nested-collection-entity-framework-objects) — DVK, May 27 '16 at 19:07

Jonathan Magnan · Accepted Answer · 2018-08-22T22:31:50.260

Solution #1

Disclaimer: I'm the owner of the project Entity Framework Plus

EF+ Query IncludeFilter feature allows filtering related entities.

context.Continents.Where(x => !x.IsDeleted)
       .IncludeFilter(x => x.Countries.Where(y => !y.IsDeleted))
       .IncludeFilter(x => x.Countries.Where(y => !y.IsDeleted)
                            .SelectMany(y => y.Cities).Where(y => !y.IsDeleted))
       .ToList();

Wiki: EF+ Query IncludeFilter

Solution #2

Another technique is to use projection (which is what my library do under the hood)

context.Continents.Where(x => !x.IsDeleted)
        .Select(x => new {Continents = x,
                          Countries = x.Countries.Where(y => !y.IsDeleted),
                          Cities = x.Countries.Where(y => !y.IsDeleted)
                                    .SelectMany(y => y.Cities).Where(y => !y.IsDeleted)
                })
        .ToList()
        .Select(x => x.Continents) // select only continents
        .ToList();

DVK · Answer 2 · 2016-05-27T19:34:08.133

0

Entity Framework doesn't have an elegant way to filter out the selection of child entities included with the parent entity, however EF 6 does provide the ability to intercept database commands so you can filter soft-deleted entities without actually modifying your query.

https://msdn.microsoft.com/en-us/data/dn469464.aspx

Starting with Entity Framework 6, anytime Entity Framework sends a command to the database this command can be intercepted by application code. This is most commonly used for logging SQL, but can also be used to modify or abort the command.

There are some other options, but none are very elegant or practical in most cases.

edited May 27 '16 at 19:34

answered May 27 '16 at 18:49

DVK

2,726
1
17
20

Thanks for the response. I'm trying to use WHERE but I don't know how to filter out the children entities like City in the same statement. Any ideas? edit: btw repeating is not a huge issue because the app is using repository so all queries will be concentrated in one class – pca1987 May 27 '16 at 18:56

How to retrieve nested entities using C# MVC with conditions?

2 Answers2