How to fix Decimal Conversion Issue in EF 7

Introduction

I was working on upgrading one of my client Web API projects from ASP.NET Core 3.1 to the latest .NET 6.0 LTS.

The Web API solution is internally connected to an SQL Server database via Entity Framework Core 3.1. So naturally when I updated my solution packages, I bumped my Entity Framework Core version to the latest 7.0.x.

Once the migration was done, the solution was built successfully without any errors. We had a brief regression testing done to check if the APIs were working fine and everything seemed OK.

So we pushed our code to production. All seemed to be going fine, until one of the users complained that a save functionality wasn’t working.

We tried to analyze what went wrong and realized that a POST API was crashing while saving an entity to the database. Root cause? Decimal value conversion.

In this article, I want to share with you one of the weirdest issues I faced when I migrated my ASP.NET Core solution to .NET 6 and my findings about it.

Entity Framework Core Decimal Conversion Issue

As soon as I understood what went wrong, the first thing I did was to simulate if the same thing was occurring in the ASP.NET Core 3.1 Web API solution. It wasn’t.

In one of my entities we had an attribute which is mapped to a table column of type NUMERIC(n,0). Now what is NUMERIC? What is n and 0?

What is Precision and Scale in SQL Server?

In SQL Server, we can store decimal values or floating point numbers along with their decimal values in the data type NUMERIC(n,x). Here n is called PRECISION and x is called SCALE.

PRECISION refers to the total number of digits present in the number, INCLUDING SCALE.
SCALE refers to the number of digits present after the decimal point.

For example, the number 43.654 has a SCALE of 3 (counting the digits 654) and PRECISION of 5 (counting 43654).

When we use Entity Framework Core to scaffold this datatype, it maps this type to decimal datatype in C#, which doesn’t have such precision or scale indication.

So for example, we can store 43.654 value in a C# variable of type decimal, but when we want to store the same in a Table we need the column to have a minimum PRECISION of 5 and SCALE of 3. So the target should be at least NUMERIC(5,3).

Out of Range Exception for decimals with Scale = 0

So where is the issue? Let’s say I have the value 43.654 in C# to be saved to an SQL Server table via Entity Framework Core. The mapped target column is of data type NUMERIC(5,0) instead of NUMERIC(5,3). This means that the column cannot store any digits after the decimal point.

In this case, the expectation is that Entity Framework Core will round off the value to the appropriate scale and store it in the column. In the above example, EF Core should round off the value 43.654 to 47 and store it in the respective column.

And this was how EF Core behaved in version 3.1.

Entity Framework Out of Range Exception with Example

To demonstrate, let’s say I have a Grades Database, with schema as below –

use [GradesDB]

create table Student
(
    StudentId int identity(1,1) primary key,
    StudentName nvarchar(100) not null,
    RollNumber int,
    CreatedDate datetime default getdate()
)
create table StudentGrade
(
    StudentGradeId int identity(1,1) primary key,
    StudentId int references Student(StudentId),
    SubjectName nvarchar(100) not null,
    Marks numeric(10,2),
    Grade numeric(10,0),
    CreatedDate datetime default getdate() 
)
GO

The column Marks can store up to 2 decimal points, while Grade doesn’t store any. When I scaffold this schema, I get two Entity classes Student and StudentGrade in EF Core 3.1 and a GradesdbContext class as below.

using System;
using System.Collections.Generic;


namespace StudentGrades3.WebAPI.Data.Entities
{
    public partial class Student
    {
        public Student()
        {
            StudentGrade = new HashSet<StudentGrade>();
        }


        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public int? RollNumber { get; set; }
        public DateTime? CreatedDate { get; set; }


        public virtual ICollection<StudentGrade> StudentGrade
        { get; set; }
    }

    public partial class StudentGrade
    {
        public int StudentGradeId { get; set; }
        public int? StudentId { get; set; }
        public string SubjectName { get; set; }
        public decimal? Marks { get; set; }
        public decimal? Grade { get; set; }
        public DateTime? CreatedDate { get; set; }


        public virtual Student Student { get; set; }
    }
}


namespace StudentGrades3.WebAPI.Data
{
    public partial class GradesdbContext : DbContext
    {
        public GradesdbContext()
        {
        }


        public GradesdbContext(DbContextOptions<GradesdbContext> options)
            : base(options)
        {
        }


        public virtual DbSet<Student> Student { get; set; }
        public virtual DbSet<StudentGrade> StudentGrade { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Student>(entity =>
            {
                entity.Property(e => e.CreatedDate)
                    .HasColumnType("datetime")
                    .HasDefaultValueSql("(getdate())");


                entity.Property(e => e.StudentName)
                    .IsRequired()
                    .HasMaxLength(100);
            });


            modelBuilder.Entity<StudentGrade>(entity =>
            {
                entity.Property(e => e.CreatedDate)
                    .HasColumnType("datetime")
                    .HasDefaultValueSql("(getdate())");


                entity.Property(e => e.Grade).HasColumnType("numeric(10, 0)");


                entity.Property(e => e.Marks).HasColumnType("numeric(10, 2)");


                entity.Property(e => e.SubjectName)
                    .IsRequired()
                    .HasMaxLength(100);


                entity.HasOne(d => d.Student)
                    .WithMany(p => p.StudentGrade)
                    .HasForeignKey(d => d.StudentId)
                    .HasConstraintName("FK__StudentGr__Stude__276EDEB3");
            });


            OnModelCreatingPartial(modelBuilder);
        }


        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

I injected the DbContext class into my controller class to make things simpler, and created a POST method to add Student Grades.

namespace StudentGrades3.WebAPI.Controllers
{


    [ApiController]
    [Route("api/[controller]")]
    public class StudentGradesController : ControllerBase
    {
        private readonly GradesdbContext _db;


        public StudentGradesController(GradesdbContext db)
        {
            _db = db;
        }


        [HttpPost, Route("{studentId}/add")]
        public async Task<int[]> AddGradesAsync(
            int studentId,
            [FromBody] IEnumerable<StudentGradeDTO> grades)
        {
            var entities = grades.Select(x => new StudentGrade
            {
                StudentId = studentId,
                Grade = x.Grade,
                Marks = x.Marks,
                SubjectName = x.SubjectName
            });


            _db.StudentGrade.AddRange(entities);
            await _db.SaveChangesAsync();


            return entities.Select(x => x.StudentGradeId).ToArray();
        }


        [HttpGet, Route("{studentId}")]
        public IEnumerable<StudentGrade> GetGrades(int studentId)
        {
            return _db.StudentGrade.Where(x => x.StudentId == studentId).ToList();
        }


    }
}

This is an ASP.NET Core 3.1 project, using Entity Framework Core version 3.1.1 package.

When I ran this Web API and tried POSTing data to the endpoint, I got no exception, and the Grade data got inserted just fine.

curl -X 'POST' \
  'http://localhost:5230/api/StudentGrades/3/add' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "subjectName": "Computer Science",
    "marks": 97.453,
    "grade": 1.4864829359
  }
]'

// Response is 200

curl -X 'GET' \
  'http://localhost:5230/api/StudentGrades/3' \
  -H 'accept: text/plain'

[
  {
    "studentGradeId": 17,
    "studentId": 3,
    "subjectName": "Computer Science",
    "marks": 97.45,
    "grade": 1,
    "createdDate": "2023-05-16T21:03:10.683",
    "student": null
  }
]

Notice that the Grade attribute was automatically rounded off to the nearest whole number (in this case, 1.4864829359 rounded off to 1).

The point here I’m trying to make is that the precision of the value I tried to insert (1.4864829359) is 11, while the target precision is 10. EF Core automatically rounded off the value and inserted it for me.

But when I upgraded this project to .NET 6 with Entity Framework Core 7.0.5 and ran migration again, I saw no visible difference in the DbContext class except that the Entities now come in plural by default.

The same code after upgrading to .NET 6 I ran and the result is as below –

curl -X 'POST' \
  'https://localhost:7157/api/StudentGrades/3/add' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "subjectName": "Computer Science",
    "marks": 97.453,
    "grade": 1.4864829359
  }
]
'

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.ArgumentException: Parameter value '1.4864829359' is out of range.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---

The above exception makes it clear that Entity Framework is no longer able to round off the value to its nearest whole number when trying to fit into a NUMERIC type with SCALE 0.

The Root Cause – Design Change in EF 5+

What changed? I tried to search for any possible explanation. Turns out that this was an expected behavior that started from EF 5. I’m referring to this github issue that was posted long ago – https://github.com/dotnet/SqlClient/issues/1214

Interestingly, as per one of the recommendations suggested in that thread, EF will no longer try to round off for numeric types with scale 0. But it does if the scale is at least 1.

Possible Solutions and the workaround I finalized

I tried changing the column data type of Grade in StudentGrade to NUMERIC(10,1) and also modified this line in DbContext file –

entity.Property(e => e.Grade).HasColumnType("numeric(10, 1)");

The result was this.

curl -X 'POST' \
  'https://localhost:7157/api/StudentGrades/3/add' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "subjectName": "Computer Science",
    "marks": 97.453,
    "grade": 1.4864829359
  }
]
'

// response was 200 OK

curl -X 'GET' \
  'https://localhost:7157/api/StudentGrades/3' \
  -H 'accept: text/plain'

[
  {
    "studentGradeId": 24,
    "studentId": 3,
    "subjectName": "Computer Science",
    "marks": 97.45,
    "grade": 1.5,
    "createdDate": "2023-05-16T21:20:19.757",
    "student": null
  }
]

Notice that the Grade now stores 1.5 instead of the expected 1, because the datatype is now NUMERIC(10,1) that allows 1 decimal place.

This didn’t work for us because it is not acceptable for an application with already existing data to have a change in its data type. What if the 0 scale was intended?

So I came with a workaround for this. The attribute that is causing the issue was Grade inside the StudentGrade entity class.

C# was passing an object that has a decimal value to SQL Server which was expecting value with no decimal places. The probable solution would be to trim off the decimal places before the data is saved to the database via EF Change Tracking.

I did this by splitting the getter and setter properties of the Grade and using a private variable internally.

namespace StudentGrades.WebAPI.Data.Entities;


public partial class StudentGrade
{
    private decimal? _grade = 0;
    public decimal? Grade
    {
        get => _grade;
        set
        {
            _grade = Decimal.Round(value.Value);
        }
    }
}


public partial class StudentGrade
{
    public int StudentGradeId { get; set; }


    public int? StudentId { get; set; }


    public string SubjectName { get; set; }


    public decimal? Marks { get; set; }


    public DateTime? CreatedDate { get; set; }


    public virtual Student Student { get; set; }
}

I separated out Grade property into another partial class of StudentGrade because when I run a scaffold in the future, this change could be lost.

When I run the application with this change, the result is below –

curl -X 'POST' \
  'https://localhost:7157/api/StudentGrades/3/add' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "subjectName": "Computer Science",
    "marks": 97.453,
    "grade": 1.4864829359
  }
]
'
// response is 200 OK

curl -X 'GET' \
  'https://localhost:7157/api/StudentGrades/3' \
  -H 'accept: text/plain'

[
  {
    "grade": 1,
    "studentGradeId": 25,
    "studentId": 3,
    "subjectName": "Computer Science",
    "marks": 97.45,
    "createdDate": "2023-05-16T21:31:46.52",
    "student": null
  }
]

Conclusion

This workaround fixed my data conversion issue and the application now works fine. But the only downside is that when I run a scaffold in the future, I will get a compilation error because EF will add another Grade property in the auto generated partial class which I need to remove manually.

But at least this works, which is a huge relief in itself.

What do you think about this issue? Do let me know in the comments below.