dotnet / dotnet/efcore

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

Open
#30,233 1 comment 0 reactions 0 assignees View on GitHub
area-type-mapping customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.