Ternary false-expression does not take ternary condition into account, resulting in slow execution on SQL Server

[mycroes]

Bug description

EF Core 8, only, but pending outcome of #36291.

When the ternary condition contains a == null check, the else branch doesn't eliminate null values in the else expression, resulting in queries that will scan more data than required when executed against SQL Server.

As part of a query template to optimize performance for a specific query I have the following C# predicate, which translates and works as expected:

r => Template.SupplyOrderId != null
    ? r.SourcePallet.SupplyOrderId == Template.SupplyOrderId
    : r.SourcePallet.Id == Template.ActivePalletId

This predicate translates to the where clause of the following SQL subquery:

SELECT [w].[NetWeight], 1 AS [Key]
FROM [WasteRegistrations] AS [w]
INNER JOIN [SourcePallet] AS [s3] ON [w].[SourcePalletId] = [s3].[Id]
WHERE CASE
    WHEN [s].[SupplyOrderId] IS NOT NULL THEN CASE
        WHEN [s3].[SupplyOrderId] = [s].[SupplyOrderId] AND [s3].[SupplyOrderId] IS NOT NULL THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
    ELSE CASE
        WHEN [s3].[Id] = [p3].[ActivePalletId] AND [p3].[ActivePalletId] IS NOT NULL THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
END = CAST(1 AS bit)

Here s.SupplyOrderId is of type int?, just as well as p3.ActivePalletId is of type int?.

If we however invert the ternary conditions and swap the expressions, we get the following expression:

r => Template.SupplyOrderId == null
    ? r.SourcePallet.Id == Template.ActivePalletId
    : r.SourcePallet.SupplyOrderId == Template.SupplyOrderId.Value);

This predicate translates to the where clause of the following SQL subquery:

SELECT [w].[NetWeight], 1 AS [Key]
FROM [WasteRegistrations] AS [w]
INNER JOIN [SourcePallet] AS [s3] ON [w].[SourcePalletId] = [s3].[Id]
WHERE CASE
    WHEN [s].[SupplyOrderId] IS NULL THEN CASE
        WHEN [s3].[Id] = [p3].[ActivePalletId] AND [p3].[ActivePalletId] IS NOT NULL THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
    ELSE CASE
        WHEN ([s3].[SupplyOrderId] = [s].[SupplyOrderId] AND [s3].[SupplyOrderId] IS NOT NULL AND [s].[SupplyOrderId] IS NOT NULL) OR ([s3].[SupplyOrderId] IS NULL AND [s].[SupplyOrderId] IS NULL) THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
END = CAST(1 AS bit)

The obvious flaw in this query is OR ([s3].[SupplyOrderId] IS NULL AND [s].[SupplyOrderId] IS NULL, because that's already excluded by this branch being the ELSE CASE of WHEN [s].[SupplyOrderId] IS NULL. Unfortunately SQL Server isn't able to optimize this out of the query, resulting in very inefficient data access. SourcePallet gets a Clustered Index Scan while there's more approriate indexes to use, this in turn causes a Clustered Index Scan against the WasteRegistration table (which in my case is a lot larger) instead of a more optimal Clustered Index Seek. Of course the outcome is specific to my data structures, nonetheless the OR part can be eliminated resolving this specific issue,

Your code

See above

Stack traces

Verbose output

EF Core version

8.0.11

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 8.0

Operating system

Windows 11

IDE

Visual Studio 2022 17.4

[ranma42]

I went forward and implemented a reproducer for your expression

Code

#:package Microsoft.EntityFrameworkCore.Sqlite@9.0.6

using System.Data;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

Console.WriteLine("Happy query");
var query = db.WasteRegistrations
    .Where(r => r.Template.SupplyOrderId != null
        ? r.SourcePallet.SupplyOrderId == r.Template.SupplyOrderId
        : r.SourcePallet.Id == r.Template.ActivePalletId);

Console.WriteLine(query.ToQueryString());
Console.WriteLine("==================");
Console.WriteLine("Sad query");
var query2 = db.WasteRegistrations
    .Where(r => r.Template.SupplyOrderId == null
        ? r.SourcePallet.Id == r.Template.ActivePalletId
        : r.SourcePallet.SupplyOrderId == r.Template.SupplyOrderId);

Console.WriteLine(query2.ToQueryString());

public class BloggingContext : DbContext
{
    public DbSet<WasteRegistration> WasteRegistrations { get; set; }
    public DbSet<SourcePallet> SourcePallets { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
            .UseSqlite();
}

public class WasteRegistration
{
    public int Id { get; set; }

    public int SourcePalletId { get; set; }
    public required SourcePallet SourcePallet { get; set; }

    public int TemplateId { get; set; }
    public required SourcePallet Template { get; set; }
}

public class SourcePallet
{
    public int Id { get; set; }

    public int? SupplyOrderId { get; set; }
    public int? ActivePalletId { get; set; }
}

I used Sqlite to keep the issue of the BIT vs boolean expressions separate.

For the first query the EFCore 8 vs 9 diff is:

 SELECT "w"."Id", "w"."SourcePalletId", "w"."TemplateId"
 FROM "WasteRegistrations" AS "w"
 INNER JOIN "SourcePallets" AS "s" ON "w"."TemplateId" = "s"."Id"
 INNER JOIN "SourcePallets" AS "s0" ON "w"."SourcePalletId" = "s0"."Id"
 WHERE CASE
-    WHEN "s"."SupplyOrderId" IS NOT NULL THEN "s0"."SupplyOrderId" = "s"."SupplyOrderId" AND "s0"."SupplyOrderId" IS NOT NULL
-    ELSE "s0"."Id" = "s"."ActivePalletId" AND "s"."ActivePalletId" IS NOT NULL
+    WHEN "s"."SupplyOrderId" IS NOT NULL THEN "s0"."SupplyOrderId" = "s"."SupplyOrderId"
+    ELSE "s0"."Id" = "s"."ActivePalletId"
 END

The resulting query looks just fine (both IS NOT NULL checks have been removed as they are not required in this case).

For the second query the EFCore 8 vs 9 diff is:

 SELECT "w"."Id", "w"."SourcePalletId", "w"."TemplateId"
 FROM "WasteRegistrations" AS "w"
 INNER JOIN "SourcePallets" AS "s" ON "w"."TemplateId" = "s"."Id"
 INNER JOIN "SourcePallets" AS "s0" ON "w"."SourcePalletId" = "s0"."Id"
 WHERE CASE
-    WHEN "s"."SupplyOrderId" IS NULL THEN "s0"."Id" = "s"."ActivePalletId" AND "s"."ActivePalletId" IS NOT NULL
-    ELSE ("s0"."SupplyOrderId" = "s"."SupplyOrderId" AND "s0"."SupplyOrderId" IS NOT NULL AND "s"."SupplyOrderId" IS NOT NULL) OR ("s0"."SupplyOrderId" IS NULL AND "s"."SupplyOrderId" IS NULL)
+    WHEN "s"."SupplyOrderId" IS NULL THEN "s0"."Id" = "s"."ActivePalletId"
+    ELSE "s0"."SupplyOrderId" = "s"."SupplyOrderId" OR ("s0"."SupplyOrderId" IS NULL AND "s"."SupplyOrderId" IS NULL)
 END

In this case the query ha slightly improved (as the expression received predicate-specific optimizations), but is still sub-optimal because the information from the WHEN clause is only propagated to the associated THEN, while it should be possible to also propagate it to the following clauses/results.

The fully optimized query would obviously be:

SELECT "w"."Id", "w"."SourcePalletId", "w"."TemplateId"
FROM "WasteRegistrations" AS "w"
INNER JOIN "SourcePallets" AS "s" ON "w"."TemplateId" = "s"."Id"
INNER JOIN "SourcePallets" AS "s0" ON "w"."SourcePalletId" = "s0"."Id"
WHERE CASE
    WHEN "s"."SupplyOrderId" IS NULL THEN "s0"."Id" = "s"."ActivePalletId"
    ELSE "s0"."SupplyOrderId" = "s"."SupplyOrderId"
END

[mycroes]

Good work, thanks for brining this back to a small example. I devoted a couple of days on the mentioned query optimizations, about half a day of that preparing the GitHub issues, so I'm happy you've taken this on to produce a small reproduction (and smart choice for using Sqlite to highlight the problem). I did it during work, so I get paid for those hours, but sometimes it's hard to justify.