LINQ .NET can't filter on .include .select .where to filter collection on deeply nested object-CodePudding

I am stuck on filtering a returning collection with deeply nested objects with the the following code

var my collection = await myRepository.GetDBSet<MyObjectType>()
                    .include(x => Table1)
                    .include(x => Table2)
                    .include(x => Table2.Select(y => SubTable1))
                    .include(x => Table2.Select(y => SubTable1.Users))
                    .include(x => Table2.Select(y => SubTable1.Users.Addressess))
                    .include(x => Table2.Select(y => SubTable1.Users.Roles))
                    .include(x => Table2.Select(y => SubTable1.Users.Departments))
                    .Where(w => w.id1 = _id && w.isActive = t);

This example query works, please ignore the .where clause

I want to filter the SubTable1.Users.Departments and not bring back any departs with deptId != 1

Here is what I tried on the .where section

.Where(w => w.id1 = _id && w.isActive = t &&
w.Table2.where(s => s.SubTable1.Users.Departments.First().DeptId != 1))

This does not work, the error is saying it cannot convert int to bool. I had to add the .First() since it would not allow the DeptId. I am trying to filter out any dept record that has the deptId but not sure how to do this on a deeply nested linq statement.

Thanks in advance for any help.

CodePudding user response：

Can you try using LINQ's Any?

From Microsoft's documentation:

Determines whether any element of a sequence exists or satisfies a condition.

In your example it shuld be something like this:

.Where(w => w.id1 = _id && w.isActive = t &&
w.Table2.where(s => s.SubTable1.Users.Departments.Any(x => x.DeptId != 1))

CodePudding user response：

(Below I assume that your question contains pseudocode and answer accordingly, since your code normally wouldn't compile. I also assume you're using EF Core.)

You can theoretically do it within the Include statement like this:

var my collection = await myRepository.GetDBSet<MyObjectType>()
                    .Include(...)
                    ...
                    .Include(x => x.Table2)
                        .ThenInclude(y => y.SubTable1)
                        .ThenInclude(z => z.Users)
                        .ThenInclude(t => t.Departments.Where(d => d.Id == 1))
                    ...
                    .Where(w => w.id1 = _id && w.isActive = t);

From my experience the support for such syntax in EF Core is a bit underwhelming - for example, it only allows one such condition per LINQ query - but this is still the simplest way to filter out the related records that you don't need and select the original, tracked entities from the database.

This feature is called Filtered Include and is available starting from EF Core 5.0 (documentation)