Entity Framework Core Include with Filter

Question

I am trying to bring a list of objects that have a list of child objects as well from the database.

Here is an example

 public class User 
 {
    public int Id { get; set; }
    public ICollection<Child> Childs { get; set; }
 }

 public class Child 
 {
    public int Id { get; set; }
    public string UserId { get; set; }
    public User User { get; set; }
 }

The problem here is that I cannot find a way to bring a list of Users and filter the Childs with a condition as well at the same time.

I tried something like this:

users = _context.Users.Where(e => e.Childs.Any(ec => ec.Id > 1))

But using this example if the condition is not met it will not bring the User back and I want all the users even if they do not have Childs or the condition is not met.

Also found this project: https://github.com/zzzprojects/EntityFramework-Plus but looks like it does not support EF Core for what I want to do.

Does anyone have any suggestions?

Answer 1

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

Our Library doesn't support Query Filter yet for .NET Core due to the N+1 queries issue.

Under our hood for EF6, our library was only doing a simple projection.

Something similar to this using your information:

var users = _Context.Users.Select(x => new {
                Users = x,
                Childs = x.Childs.Any(ec => ec.Id > 1)
            })
            .ToList()
            .Select(x => x.Users)
            .ToList();

However, for EF Core, the same projection makes a database roundtrip to get a child for every user (N+1 queries)

You can try the following projection and see if you get the same error.

I believe until the EF Core team fixes it, to my knowledge, there is not way to filter child entities.

SELECT [x].[Id], [x].[ColumnInt]
FROM [Lefts] AS [x]
go
exec sp_executesql N'SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Rights] AS [ec1]
        WHERE ([ec1].[Id] > 1) AND (@_outer_Id1 = [ec1].[LeftId]))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END',N'@_outer_Id1 int',@_outer_Id1=1
go
exec sp_reset_connection
go
exec sp_executesql N'SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Rights] AS [ec1]
        WHERE ([ec1].[Id] > 1) AND (@_outer_Id1 = [ec1].[LeftId]))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END',N'@_outer_Id1 int',@_outer_Id1=2
go
exec sp_reset_connection
go
exec sp_executesql N'SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Rights] AS [ec1]
        WHERE ([ec1].[Id] > 1) AND (@_outer_Id1 = [ec1].[LeftId]))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END',N'@_outer_Id1 int',@_outer_Id1=3
go
exec sp_reset_connection
go
exec sp_executesql N'SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Rights] AS [ec1]
        WHERE ([ec1].[Id] > 1) AND (@_outer_Id1 = [ec1].[LeftId]))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END',N'@_outer_Id1 int',@_outer_Id1=4
go
exec sp_reset_connection
go
exec sp_executesql N'SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Rights] AS [ec1]
        WHERE ([ec1].[Id] > 1) AND (@_outer_Id1 = [ec1].[LeftId]))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END',N'@_outer_Id1 int',@_outer_Id1=5
go

ANSWER Sub-Question:

With the new release of .NET core 2.0 does this problem got fixed ?

Unfortunately, Entity Framework is still not able to handle cast correctly with the V2.x

By example, this LINQ that use the Cast method doesn't work:

var ids = ctx.MyTables
    .Cast<IMyTable>()
    .Cast<MyTable>()
    .Where(x => x.SomeKey.Equals(keyId))
    .Select(x => x.MyFieldIntegerIWant)
    .ToList();

EDIT: Status Update #1

Unfortunately, the EF Core team has still not fixed the N+1 queries issue with projection.

Looking at the latest status for the v3.0, I do not believe they plan to fix this kind of issue soon either: https://github.com/aspnet/EntityFrameworkCore/issues/10001#issuecomment-456581915

EDIT: Status Update #2

The IncludeFilter feature is now supported in the latest version of EF Plus (EF Core 2.x and EFCore 3.x)