Including entity with a QueryFilter results in root entity not being loaded

[langebo]

Related to #11691

We are having multiple entities with the same query filter in the form of

bool IsArchived

Our desired behavior for these entities is, that once they are archived, they are not displayed to the user anymore, when queried as the root entity.

The reason we chose not to delete these entities is, that once they are related to other root entities, they should still be visible (regardless of them being archived or not), so that aggregates that were created before the nested entity was archived, remain unchanged.

I have prepared an example to showcase the (from our perspective) unwanted behavior.

Steps to reproduce

As an example we have these 2 classes.

public class Unit
{
    public Guid Id { get; set; }
    public string Title { get; set; }

    public bool IsArchived { get; set; }
}

public class Measurement
{
    public Guid Id { get; set; }
    public double Value { get; set; }

    public Guid UnitId { get; set; }
    public Unit Unit { get; set; }

    public bool IsArchived { get; set; }
}

DbContext configuration example

override protected void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Measurement>(e =>
    {
        e.HasOne(m => m.Unit)
            .WithMany()
            .HasForeignKey(m => m.UnitId)
            .OnDelete(DeleteBehavior.NoAction);

        e.HasQueryFilter(m => !m.IsArchived);
    });

    modelBuilder.Entity<Unit>(e => e.HasQueryFilter(u => !u.IsArchived));
}

And this is the "test case" for our example. We create two units and four measurements and assign units to these measurements. Afterwards we change one unit to be archived. Then we query all measurements.

public async Task Prepare()
{
    await using var db = new Context();

    var meters = new Unit { Id = Guid.NewGuid(), Title = "meters" };
    var inches = new Unit { Id = Guid.NewGuid(), Title = "inches" };
    await db.Units.AddRangeAsync(meters, inches);

    await db.Measurements.AddRangeAsync(
        new Measurement { Value = 0.4D, Unit = meters },
        new Measurement { Value = 1.2D, Unit = meters },
        new Measurement { Value = 3.4D, Unit = inches },
        new Measurement { Value = 2.1D, Unit = inches }
    );

    await db.SaveChangesAsync();
}

public async Task Act()
{
    await using var db = new Context();
    var meters = await db.Units.FirstOrDefaultAsync(u => u.Title == "meters");
    meters.IsArchived = true;
    await db.SaveChangesAsync();
}

public async Task Check()
{
    await using var db = new Context();

    var measures = await db.Measurements
        .Include(m => m.Unit)
        .ToListAsync();

    var result = JsonSerializer.Serialize(measures);
    Console.WriteLine(result);
}

await test.Prepare();
await test.Act();
await test.Check();

Querying the measurements now, only yields the 2 entries, that are not related to the archived unit, even though the measurements that are related to the archived unit themselves are not archived.

[
    {
        "Id": "e635456e-c285-4d14-973f-08d7eb5af8df",
        "Value": 3.4,
        "UnitId": "a9bec54c-5fba-4c93-abb1-a5373d08fd06",
        "Unit": {
            "Id": "a9bec54c-5fba-4c93-abb1-a5373d08fd06",
            "Title": "inches",
            "IsArchived": false
        },
        "IsArchived": false
    },
    {
        "Id": "b421fafd-1076-4f9c-9740-08d7eb5af8df",
        "Value": 2.1,
        "UnitId": "a9bec54c-5fba-4c93-abb1-a5373d08fd06",
        "Unit": {
            "Id": "a9bec54c-5fba-4c93-abb1-a5373d08fd06",
            "Title": "inches",
            "IsArchived": false
        },
        "IsArchived": false
    }
]

Which makes sense in regard to the generated sql statement, which applies the query filter to the inner join as well as to the outer/main query.

SELECT [m].[Id], [m].[IsArchived], [m].[UnitId], [m].[Value], [t].[Id], [t].[IsArchived], [t].[Title]
      FROM [Measurements] AS [m]
      INNER JOIN (
          SELECT [u].[Id], [u].[IsArchived], [u].[Title]
          FROM [Units] AS [u]
          WHERE [u].[IsArchived] <> CAST(1 AS bit)
      ) AS [t] ON [m].[UnitId] = [t].[Id]
      WHERE [m].[IsArchived] <> CAST(1 AS bit)

Ignoring query filters via

.IgnoreQueryFilters()

is not an option for us either, because it would also yield measurements that are 'legitimately' archived.

So our desired behavior would be to remain the inner join (since we want the archived nested entities to be included) but not apply the query filter to the inner select query.

The related issue #11691 describes a similar behavior, and @smitpatel mentioned that it is fixed with 3.1, but either it isn't or (more likely) we have a misunderstanding of what the desired/implemented behavior is like.

So my question is, are we doing things wrong? Is there a way to achieve our desired behavior with global query filters or do we need to manually perform this filtering on all of our calls (which would be kinda sad, because i really like this feature).

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer 3.1.3
Target framework: .NET Core 3.1 (SDK: 3.1.201)
Operating system: macOS Catalina 10.15.4 (also tested on Windows 10)
IDE: Visual Studio Code 1.44.2

[smitpatel]

Duplicate of #19801