Description
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