Query produced by global filters leads to wrong result with optional navigation

[msmolka]

File a bug

The EF generates incorrect query, which leads to wrong results. Even if parent record is removed, child record is returned.

Include your code

Please look at attached repo. EFBugRepo.zip

Global filters

modelBuilder.Entity<Customer>().HasQueryFilter(u => u.DeletionDate == null);
modelBuilder.Entity<Role>().HasQueryFilter(c => c.CustomerId == null || (c.CustomerId != null && c.Customer.DeletionDate == null));

Generated query

SELECT [r].[RoleId], [r].[RoleCustomerId], [r].[RoleName]
FROM [Roles] AS [r]
LEFT JOIN (
    SELECT [c].[CustomerId], [c].[CustomerDeletionDate]
    FROM [Customers] AS [c]
    WHERE [c].[CustomerDeletionDate] IS NULL
) AS [t] ON [r].[RoleCustomerId] = [t].[CustomerId]
WHERE ([r].[RoleCustomerId] IS NULL) OR ([t].[CustomerDeletionDate] IS NULL)
ORDER BY [r].[RoleName]

If customer is deleted it still returns role, because left join returns null. and then null is checked.

Include provider and version information

EF Core version: Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: (e.g. .NET 6.0) Operating system: IDE: (e.g. Visual Studio 2022)