How to store decimal.MaxValue or decimal.MinValue?

[yanpitangui]

Question

Is there any way to store decimal.MaxValue or decimal.MinValue in a decimal(p, s) column?
I tried inserting directly into the database, eg: -POWER(2.0,96), +POWER(2.0,96) and it worked.
Can it be a problem with EfCore?

Code sample

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

main();

void main()
{
    using var context = new SomeDbContext();
    context.Database.EnsureCreated();
    context.Add(new Entity { Owned = new Owned()});
    context.SaveChanges();
    
}

public class Entity
{
    public int Id { get; set; }
    public Owned Owned { get; set; } = null!;
}

public class Owned
{
    public int Id { get; set; }

    public int EntityId { get; set; }

    public Entity Entity { get; set; } = null!;

    public decimal LowerLimit { get; set; } = decimal.MinValue;
    public decimal UpperLimit { get; set; } = decimal.MaxValue;
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=ef-decimal;")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Entity>()
            .OwnsOne(x => x.Owned, b =>
            {
                b.Property(x => x.LowerLimit)
                    .HasColumnType("decimal(38,4)");

                b.Property(x => x.UpperLimit)
                    .HasColumnType("decimal(38,4)");
            });

    }

    public DbSet<Owned> Owned { get; set; }
    public DbSet<Entity> Entities { get; set; }
}

Stack traces

An exception occurred in the database while saving changes for context type 'SomeDbContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> System.OverflowException: Conversion overflows.
         at System.Data.SqlTypes.SqlDecimal.ToDecimal()
         at System.Data.SqlTypes.SqlDecimal.get_Value()
         at Microsoft.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
         at Microsoft.Data.SqlClient.TdsParser.TDSExecuteRPCAddParameter(TdsParserStateObject stateObj, SqlParameter param, MetaType mt, Byte options)
         at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)

         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__104_0(DbContext _, ValueTuple`2 t)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> System.OverflowException: Conversion overflows.
   at System.Data.SqlTypes.SqlDecimal.ToDecimal()
   at System.Data.SqlTypes.SqlDecimal.get_Value()
   at Microsoft.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
   at Microsoft.Data.SqlClient.TdsParser.TDSExecuteRPCAddParameter(TdsParserStateObject stateObj, SqlParameter param, MetaType mt, Byte options)
   at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__104_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at Program.<<Main>$>g__main|0_0() in C:\src\stuff\ef-decimal\EfDecimal\EfDecimal\Program.cs:line 12
   at Program.<Main>$(String[] args) in C:\src\stuff\ef-decimal\EfDecimal\EfDecimal\Program.cs:line 5

Include provider and version information

EF Core version: 6.0.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0.200
Operating system: Windows 11
IDE: Rider 2022.1.1

[roji]

You will need to increase the precision of the SQL Server decimal column. EF Core by default creates a column with type decimal(18,2) - try decimal(38,0) (see docs).

[yanpitangui]

Yeah, in my sample I was using decimal(38,4), but it seems that i need to use 0 as scale for it to work.
I thought that i could make it work with it set as 2 or something.

[roji]

I'd recommend reading up on the SQL Server docs on decimal (as this isn't related to EF Core).

[yanpitangui]

I tried getting the generated query from entity and I ran it directly on SSMS and it worked. Are you sure this is a sql server thing and not a ef core conversion failing?

Generated query:

fail: 16/06/2022 13:13:32.051 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)
      Failed executing DbCommand (15ms) [Parameters=[@p0='1', @p1='0', @p2='-79228162514264337593543950335' (Precision = 38) (Scale = 4), @p3='79228162514264337593543950335' (Precision = 38) (Scale = 4)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Owned] ([EntityId], [Id], [LowerLimit], [UpperLimit])
      VALUES (@p0, @p1, @p2, @p3);

The query I ran:

declare @p0 int = 0, @p1 int =3, @p2 decimal(38,4)='-79228162514264337593543950335', @p3 decimal(38,4)='79228162514264337593543950335'

SET IDENTITY_INSERT Entities ON
insert into Entities(id)
values(0);

 SET NOCOUNT ON;
INSERT INTO [Owned] ([EntityId], [Id], [LowerLimit], [UpperLimit])
VALUES (@p0, @p1, @p2, @p3);

And when I select from the owned table:

EntityId    Id          LowerLimit                              UpperLimit
----------- ----------- --------------------------------------- ---------------------------------------
0           3           -79228162514264337593543950335.0000     79228162514264337593543950335.0000

[roji]

I took another look, and I can indeed see this working outside of EF Core: decimal.MaxValue can be inserted into a decimal(38,4) column with SqlClient, but an exception is thrown when it's done via EF Core. The reason is that EF Core sets the precision and scale on SqlParameter; doing that via raw ADO.NET triggers the same exception:

// Works
using var cmd1 = new SqlCommand("INSERT INTO [Blogs] ([Decimal]) VALUES (@p)", conn);
cmd1.Parameters.Add(new SqlParameter("p", decimal.MaxValue));
cmd1.ExecuteNonQuery();

// Throws
using var cmd2 = new SqlCommand("INSERT INTO [Blogs] ([Decimal]) VALUES (@p)", conn);
cmd2.Parameters.Add(new SqlParameter("p", decimal.MaxValue) { Precision = 38, Scale = 4 });
cmd2.ExecuteNonQuery();

IIRC it's important to set the precision/scale on the parameter, otherwise various issues can occur (e.g. with Always Encrypted); however, I agree it's odd that this causes the above failure. This may mean that there's an issue with System.Data.SqlTypes.SqlDecimal, which is where the exception originates. You may want to report this on github.com/dotnet/sqlclient - I'm not sure if this is intended or not.

/cc @ajcvickers

[roji]

Reported in SqlClient as dotnet/SqlClient#1655.

A workaround here is to use a command interceptor to go over the parameters and unset the Precision/Scale.

[yanpitangui]

Thank you @roji!

[IT-CASADO]

Why is this ticket closed. The mentioned workaround by command interceptor could not be the final solution:

IIRC it's important to set the precision/scale on the parameter, otherwise various issues can occur (e.g. with Always Encrypted); however, I agree it's odd that this causes the above failure

I cannot believe that two Microsoft Teams (EF Core and SQL Server) cannot solve this for 19 months.