System.InvalidOperationException: 'Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression]'

[janossuta]

Bug description

After upgrading from EF Core 8 to 10, one of our queries began throwing a System.InvalidOperationException.

This query is working in EF Core 8.

System.InvalidOperationException: 'Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression]' of type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression' encountered in 'SqlNullabilityProcessor'.'

This is a simplified version of the original query.

var factExchangeRatesMonthliesToDaily = await (from d in _context.DimDates
                                            join ere in _context.FactExchangeRatesMonthlies
                                                     on new { MonthId = (d.Year ?? 0) * 100 + (d.MonthNumber ?? 0) }
                                                 equals new { MonthId = ere.DimMonthId } into ere_1
                                            from ere in ere_1.DefaultIfEmpty()
                                            where filter.FromDate.GetYearMonthDayInt() <= d.DimDateId
                                               && filter.ToDate.GetYearMonthDayInt() >= d.DimDateId
                                               && ere.DimNumeratorCurrencyId == baseCurrency
                                               && ere.DimDenumeratorCurrencyId == newCurrency
                                            select new
                                            {
                                                d.DimDateId,
                                                ExchangeRate = ere != null ? ere.ExchangeRate : (decimal?)null,
                                                ere.DimDenumeratorCurrencyId,
                                                ere.DimNumeratorCurrencyId
                                            }).ToListAsync(); 

d.Year and d.MonthNumber fields are nullable

This is the part that fails in the new version:
on new { MonthId = (d.Year ?? 0) * 100 + (d.MonthNumber ?? 0) }

I resolved the issue by reorganizing the original query and removing the complex join condition. However, I'd like to know if this is a bug or if there's a way to fix it without modifying the query.

my repo with a runnable example

Your code

using DataLayer.Models;
using KinectOnline.Services.Extensions;
using Microsoft.EntityFrameworkCore;

var webmartContextOptions = new DbContextOptionsBuilder<WEBMartContext>()
        .UseSqlServer("Server=localhost,1433;Database=WEBMart;Integrated Security=True;TrustServerCertificate=True")
        .Options;

using var context = new WEBMartContext(webmartContextOptions);

await context.Database.EnsureCreatedAsync();

await GetVolumeAndSpotcostProductionAsync(context);

async Task GetVolumeAndSpotcostProductionAsync(WEBMartContext _context)
{
    var filter = new 
    {
        FromDate = new DateTime(2025, 1, 1),
        ToDate = new DateTime(2025, 1, 31) 
    };
  
    string currency = "NOK";
    string baseCurrency = "EUR";

    string newCurrency = currency ?? baseCurrency;


    var factExchangeRatesMonthliesToDaily = await (from d in _context.DimDates
                                            join ere in _context.FactExchangeRatesMonthlies
                                                     on new { MonthId = (d.Year ?? 0) * 100 + (d.MonthNumber ?? 0) }
                                                 equals new { MonthId = ere.DimMonthId } into ere_1
                                            from ere in ere_1.DefaultIfEmpty()
                                            where filter.FromDate.GetYearMonthDayInt() <= d.DimDateId
                                               && filter.ToDate.GetYearMonthDayInt() >= d.DimDateId
                                               && ere.DimNumeratorCurrencyId == baseCurrency
                                               && ere.DimDenumeratorCurrencyId == newCurrency
                                            select new
                                            {
                                                d.DimDateId,
                                                ExchangeRate = ere != null ? ere.ExchangeRate : (decimal?)null,
                                                ere.DimDenumeratorCurrencyId,
                                                ere.DimNumeratorCurrencyId
                                            }).ToListAsync();
}

Stack traces

Verbose output

EF Core version

10.0.0

Database provider

No response

Target framework

.NET 10.0

Operating system

No response

IDE

No response

[nithyav2017]

@janossuta
I tested the query with sample data. In SQL and EF Core 10 (.NET 10), the query either returns expected match (DimDateId: 1, ExchangeRate: 56.0000) or empty result set when the join key is null. Below is the sample result set of SQL query

DimMonthId DimNumeratorCurrencyId DimDenominatorCurrencyId ExchangeRate
NULL 1 1 23.5000
NULL 2 2 45.0000
202405 3 3 56.0000

Example LINQ:

var query =
from d in _context.DimDates
join ere in _context.FactExchangeRatesMonthlies
on ((d.Year ?? 0) * 100 + (d.MonthNumber ?? 0))
equals ere.DimMonthId
into ere_1
from ere in ere_1.DefaultIfEmpty()
where ere.DimNumeratorCurrencyId == 3
&& ere.DimDenominatorCurrencyId == 3
select new
{
d.DimDateId,
ExchangeRate = ere != null ? ere.ExchangeRate : (decimal?)null
};

I inserted NULL for 2 rows in [FactExchangeRatesMonthlies]. EF core handles nulls correctly and does not throw the SqlNullabilityProcessor exception. This suggests the bug may be schema-specific and only reproducible under certain nullability conditions(like in the original DimDate/ [FactExchangeRatesMonthlies] schema from issue. On my local machine, EF Core 10 generates valid SQL and safely returns empty results.

[janossuta]

@nithyav2017, I didn't change anything when setting up my example. I installed a fresh SQL Express instance, copied my code, and ran it. With EF Core 10, it failed. If there is a setting, I would like to know where.

[roji]

@janossuta thanks for the repro, I can see the exception happening. However, things are slightly tricky.

When cloning your repro, I can indeed see Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression]' (with EF 10), and when switching to EF 8, no exception occurs. However, when turning on logging, on EF 8 I can see the following SQL getting generated:

SELECT [d].[DimDateID] AS [DimDateId], [f].[ExchangeRate], [f].[DimDenumeratorCurrencyID] AS [DimDenumeratorCurrencyId], [f].[DimNumeratorCurrencyID] AS [DimNumeratorCurrencyId]
FROM [DimDates] AS [d]
LEFT JOIN [agg].[FactExchangeRatesMonthly] AS [f] ON 0 = 1
WHERE @__GetYearMonthDayInt_0 <= [d].[DimDateID] AND @__GetYearMonthDayInt_1 >= [d].[DimDateID] AND [f].[DimNumeratorCurrencyID] = @__baseCurrency_2 AND [f].[DimDenumeratorCurrencyID] = @__newCurrency_3

Note that the LEFT JOIN has ON 0 = 1, which is very likely not what you want. The cause here is the comparison of (d.Year ?? 0) * 100 + (d.MonthNumber ?? 0) (which evalutes to a short?) with ere.DimMonthId (which is an int?) - EF doesn't currently behave well when comparing things of different types (that's tracked by #15586). To work aroundd this, you can add an explicit cast up to int?: on (int?)(d.Year + d.MonthNumber) equals ere.DimMonthId into ere_1. Note also that you don't need the superfluous anonymous type wrapper - you can compare values directly.

In any case, I've submitted #37350 to fix the exception - after this, your code generates ON 0 = 1 as before.

[nithyav2017]

I could also reproduce the issue after cloning your repo. In my local code, I have defined all fields as int type. So, it returned empty results. The SQLNullabilityProcessor handled that constant expression since there is no type mismatch.

This is a good catch in the query (ON 0=1). Casting the whole arithmetic expression to int? fixes the translation issue: (Int?) ( (d.Year ?? 0) * 100 + (d.MonthNumber ?? 0) ) . This forces EF Core to treat both sides as the same type (int?) , avoiding the ON 0=1 translation.

Also , adding a computed column in SQL is also works.

' ALTER TABLE DimDates
ADD MonthId AS (Year * 100 + MonthNumber);'

This defines MonthId as a computed column in the database. EF Core can then map it as a regular property ( public int MonthId{ get; set; }) and use it directly in joins , also allows indexing if needed.

[janossuta]

@roji, @nithyav2017 Thanks for the suggestions. I was able to work around this issue by the time I raised the ticket. I used a different table in my join that had non-nullable fields. I wanted to know whether this was a bug or if I did something wrong. I admit I didn't check the logs for my example because our original, more complex version doesn't include the "??" null check. In our case, the fields cannot actually be null, so the query is generated correctly in .NET 8. I added the "??" null checks to ensure the fields always have a value, but it's interesting to see that the generated SQL in my example ends up as 0 = 1.

[nithyav2017]

The issue is the type mismatch when EF Core's query pipeline calls SQLNullabilityProcessor , it has to convert (byte+short) -> int and also check nullability.

In my tests, it always checks for nullability even when both model fields and columns are defined as non-nullable. The issue is resolved by explicitly casting int. In .NET8 , the same scenario throws an error when the data types are not compatible.