Sum throwing exception when property has a ValueConverter with ConvertsNulls set to true
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
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:
```C#
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()
.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(entityBuilder =>
{
entityBuilder.HasKey(x => x.Id);
entityBuilder.Property(x => x.MyDecimalValue)
.HasConversion();
entityBuilder.Property(x => x.MyLongValue)
.HasConversion();
});
}
}
public class DecimalConverter : ValueConverter
{
public override bool ConvertsNulls => true;
public DecimalConverter() : base(x => x ?? 0m, x => x == 0m ? null : x) { }
}
public class LongConverter : ValueConverter
{
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.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.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.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
```
Contributor guide
Assessment
This issue has not been assessed yet.