Sum throwing exception when property has a ValueConverter with ConvertsNulls set to true

[ZvonimirMatic]

EF Core version: 7.0.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer, Microsoft.EntityFrameworkCore.Sqlite, Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 7.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.4.2

I have a database where I can only use numeric type for numbers. Also, instead of NULL values, the default value is always 0. In my C# code I need to consider those 0 values as null, and that is why I created a converter in the code below.

The problem is that EF Core cant translate Sum method when I try to sum up properties that have the converter associated and when that converter has the property ConvertsNulls to true. I also tried the same thing with long instead of decimal. The results vary depending on the provider:

	SQL Server	Sqlite	PostgreSQL
decimal	Exception	Not supported	Exception
long	Exception	Exception	Success

The exception I'm getting (stack trace below): System.InvalidCastException: 'Unable to cast object of type 'System.Int32' to type 'System.Nullable`1[System.Int64]'.'.

I have also tried removing ConvertsNulls override (the default is false). In that case the exception is not thrown, but I need it because for the rest of the code to work correctly. I believe the actual problem is closely related to this property.

Complete code:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

public class Program
{
    public static void Main(string[] args)
    {
        var context = new MyContext();

        var query = context
            .Set<MyEntity>()
            .GroupBy(x => x.Id)
            .Select(x => new
            {
                Id = x.Key,
                SumDecimal = x.Sum(x => x.MyDecimalValue),
                SumLong = x.Sum(x => x.MyLongValue),
            });

        var queryString = query.ToQueryString();
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public decimal? MyDecimalValue { get; set; }
    public long? MyLongValue { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.UseSqlServer("");
    }

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

        modelBuilder.Entity<MyEntity>(entityBuilder =>
        {
            entityBuilder.HasKey(x => x.Id);

            entityBuilder.Property(x => x.MyDecimalValue)
                .HasConversion<DecimalConverter>();

            entityBuilder.Property(x => x.MyLongValue)
                .HasConversion<LongConverter>();
        });
    }
}

public class DecimalConverter : ValueConverter<decimal?, decimal>
{
    public override bool ConvertsNulls => true;

    public DecimalConverter() : base(x => x ?? 0m, x => x == 0m ? null : x) { }
}

public class LongConverter : ValueConverter<long?, long>
{
    public override bool ConvertsNulls => true;

    public LongConverter() : base(x => x ?? 0L, x => x == 0L ? null : x) { }
}

Stack trace:

   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.<>c__DisplayClass4_0`2.<SanitizeConverter>b__0(Object v)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.GenerateSqlLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSqlConstant(SqlConstantExpression sqlConstantExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.<VisitSqlFunction>b__23_0(SqlExpression e)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitProjection(ProjectionExpression projectionExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.<VisitSelect>b__20_0(ProjectionExpression e)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateRootCommand(Expression queryExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(Expression queryExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.CreateDbCommand()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.ToQueryString()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   at Program.Main(String[] args) in C:\testing\DecimalSumTest\DecimalSumTest\Program.cs:line 20

[Danielku15]

I face a similar problem with trying to add custom support of TimeSpan sums on DB level (via custom TimeSpanSum() extension method and IAggregateMethodCallTranslatorPlugin)

I have a ValueConverter which stores seconds for the TimeSpan and then I want to query sums I run a similar exception as mentioned above:

System.InvalidCastException: Invalid cast from 'System.Int32' to 'System.TimeSpan'.
   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at System.Int32.System.IConvertible.ToType(Type type, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.Sanitize[T](Object value)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.<>c__DisplayClass4_0`2.<SanitizeConverter>b__1(Object v)

I think the problem is located here:

https://github.com/dotnet/efcore/blob/v7.0.9/src/EFCore.Relational/Query/SqlNullabilityProcessor.cs#L1078-L1087

On all SUM functions a COALESCE with 0 is added and there it reuses the sqlFunctionExpression.TypeMapping. In this TypeMapping the Converter is set to the custom ValueConverter. And there cast from a 0 integer to the storage type happens which is wrong. IMO in line 1085 we need to respect the model type and construct a default value which can be sent through the ValueConverter for translation to the storage.

While ultimately it would be awesome to have built-in support of SUMs for any custom mapped type (as long it is numeric in the DB) I think as a first step this problem could be fixed?

Possible change:

+            object defaultValue = sqlFunctionExpression.TypeMapping == null ? 0 : sqlFunctionExpression.TypeMapping.ClrType.GetDefaultValue();
            return _sqlExpressionFactory.Coalesce(
                sqlFunctionExpression.Update(instance, arguments),
-                _sqlExpressionFactory.Constant(0, sqlFunctionExpression.TypeMapping),
+                _sqlExpressionFactory.Constant(defaultValue, sqlFunctionExpression.TypeMapping),
                sqlFunctionExpression.TypeMapping);