Custom ValueConverter generates correct SQL in expression eval but incorrect TSQL

[jpenniman]

I have a scenario where I have a non-nullable column in my table but a my POCO model is nullable. (I know, not ideal. It's a brownfield and I'm trying to avoid a projection). I created a converter as follows:

class NullDecimalConverter : ValueConverter<decimal?, double>
{
    public NullDecimalConverter() : base(
        model => model == null ? 0 : (double)model,
        dbValue => dbValue == 0 ? null : (decimal)dbValue,
        convertsNulls: true
    )
    { }
}

Then mapped as follows:

model.Property(p => p.Quantity).HasColumnName("quantity").HasConversion(new NullDecimalConverter());

When use the following Linq query...

var po = db.ProposedOrders.Where(p => p.Quantity == null).ToList();

It generates the correct SQL at the start of the process, but the incorrect SQL to execute:

The select statement compiled here is correct...

dbug: 2/27/2024 15:35:42.293 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression: 
      'queryContext => new SingleQueryingEnumerable<ProposedOrder>(
          (RelationalQueryContext)queryContext, 
          RelationalCommandCache.QueryExpression(
              Projection Mapping:
                  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: ProposedOrder.Id (Id) Required PK AfterSave:Throw, 0], [Property: ProposedOrder.ComplianceStatus (SeverityLevel?), 1], [Property: ProposedOrder.Quantity (decimal?), 2] }
              SELECT p.order_id, p.compliance_check, p.quantity
              FROM proposed_orders AS p
              WHERE p.quantity == 0.0E0),
          null,
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, ProposedOrder>,
          NullEnumsInEF.ReadDbContext,
          False,
          False,
          True
      )'

But then this is what it finally executed...

info: 2/27/2024 15:35:42.582 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (31ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [p].[order_id], [p].[compliance_check], [p].[quantity]
      FROM [proposed_orders] AS [p]
      WHERE [p].[quantity] IS NULL

Ultimately, we'll probably end up solving it at the database level so no converters are necessary, but it struck me as odd that EF got it right on the first pass but didn't actually use that SQL.

Include provider and version information

EF Core version: 8.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 8.0 Operating system: Windows 11 IDE: Visual Studio 2022 17.9)

[ajcvickers]

Note for team triage: related to #26209 and #26210.