Query Filters doesn't apply to JOIN or subqueries

[Xavier-Mobius]

Hello, and thanks for this tool :)

The Query Filter function does not apply to JOIN (or subqueries), whereas the basic Entity Core function does. This is very inconvenient in most cases (for example, for Multi-Tenancy or Soft-Delete, it is necessary for the filter to apply to all tables, not just the main table).

Example:

context.Filter<Team>(q => q.Where(x => !x.IsDeleted));
context.Filter<Member>(q => q.Where(x => !x.IsDeleted));

var query = context.Teams.SelectMany(t => t.Members);
Console.WriteLine(query.ToQueryString());

The returned SQL is :

SELECT "m"."MemberId", "m"."IsDeleted", "m"."Name", "m"."TeamId"
FROM "Teams" AS "t"
INNER JOIN "Members" AS "m" ON "t"."TeamId" = "m"."TeamId"
WHERE NOT ("t"."IsDeleted")

If I make the same query using the native query filters from the framework :

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // ...
    modelBuilder.Entity<Team>().HasQueryFilter(x => !x.IsDeleted);
    modelBuilder.Entity<Member>().HasQueryFilter(x => !x.IsDeleted);

    base.OnModelCreating(modelBuilder);
}

The result is quite different:

SELECT "t0"."MemberId", "t0"."IsDeleted", "t0"."Name", "t0"."TeamId"
FROM "Teams" AS "t"
INNER JOIN (
    SELECT "m"."MemberId", "m"."IsDeleted", "m"."Name", "m"."TeamId"
    FROM "Members" AS "m"
    WHERE NOT ("m"."IsDeleted")
) AS "t0" ON "t"."TeamId" = "t0"."TeamId"
WHERE NOT ("t"."IsDeleted")

Complete example file : https://dotnetfiddle.net/GDNRkE (You can easily see the difference between expected and observed behavior by commenting/uncommenting the first line: #define Z_ENTITYFRAMEWORK_PLUS).

• EF version: [EF Core v7.0.13]
• EF Plus version: [EF Plus Core v7.100.0.5]
• Tested with Postgres and SqlLite
• Database Provider version (NuGet): [Microsoft.EntityFrameworkCore.Sqlite 7.0.13"]

Thank you for your time

[JonathanMagnan]

Hello @Xavier-Mobius ,

Unfortunately, this is part of our limitations for EF Core: https://entityframework-plus.net/ef-core-query-filter#limitations

We succeeded in making it work in EF6 as the Query Filter was working differently, but we were not able to use the same technique for EF Core.

Best Regards,

Jon

[Xavier-Mobius]

Oh, I understand.

I think you should write it more explicitly in the limitations section of your documentation. "Include" is, in my opinion, different from "subqueries". Do you have any plans in your roadmap to work around this limitation soon?

Thank you

[JonathanMagnan]

Hello @Xavier-Mobius ,

Unfortunately, no, I don't think it will ever happen. Since EF Core already have their own global filter query, I doubt we will pass more time on this feature to try to improve it.

Best Regards,

Jon

[Xavier-Mobius]

That's sad to hear because, with such a limitation, I don't see how I can use this library for filters. :(

It seemed so promising. This limitation aside, your library offers much more advanced and convenient global filter management than the framework's native one.

Anyway, thanks for your time, have a good day.