Improvement - Not truncating/rounding decimals for `SqlParameter` with `Scale = 0`, and throws Out of Range Exception

[julian-gargicevich]

After upgrading from 3.1 to 5 my decimal types started throwing an Out of Range Exception on save. I know there's a breaking change related, but even after setting the precision using the new API I'm still getting this error.

My previous configuration was working in 3.1:

builder.Property(e => e.BookValuePerSqmtr).HasColumnType("decimal(14,0)");

Trying to save this number 22351.969005269646026090843821 throws the out of Range exception, while it was doing the right rounding/truncating in 3.1.

builder.Property(e => e.BookValuePerSqmtr).HasPrecision(14,0);

I tried changing the model builder configuration to use the new HasPrecision method but I'm still getting the same error. Is there any way to re enable the rounding before saving?

Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'
ArgumentException: Parameter value '22351.969005269646026090843821' is out of range.

EF Core version: 5.0.7
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.3

[ajcvickers]

Note for triage: This is a consequence of how SqlClient handles decimals when the scale is set to 0. So with decimal(14,1), SqlClient will round when given a decimal with digits after the decimal point, but when the same value is used with decimal(14,0) it will throw.

@cheenamalhotra Is this expected behavior for SqlClient?

Full stack trace:

fail: 8/13/2021 14:50:58.131 CoreEventId.SaveChangesFailed[10000] (Microsoft.EntityFrameworkCore.Update)
      An exception occurred in the database while saving changes for context type 'SomeDbContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> System.ArgumentException: Parameter value '22351.969005269646026090843821' is out of range.
         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(DbContext _, Boolean acceptAllChangesOnSuccess)
         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 updating the entries. See the inner exception for details.
 ---> System.ArgumentException: Parameter value '22351.969005269646026090843821' is out of range.
   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(DbContext _, Boolean acceptAllChangesOnSuccess)
   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() in C:\local\AllTogetherNow\FiveOh\Program.cs:line 87

[cheenamalhotra]

This seems related to change from #470 released in v2.0.0.
To round instead of truncation was an intentional breaking change to fix #440.

I don't think there should be Out of Range exceptions due to that, but we can double check. cc @DavoudEshtehari

Please find context switch information to re-enable old behavior and confirm if that fixes the issue:
Enabling decimal truncation behavior

[ajcvickers]

Thanks @cheenamalhotra. Would it be better to transfer this to your repo?

[cheenamalhotra]

Sure, I'll take over.

@julian-gargicevich

Could you confirm if enabling App Context switch as described on MS Docs fixes your issue and/or if you override Microsoft.Data.SqlClient version to latest v3.0.0 or v2.1.3 by directly referencing it and rerun your app, do you still experience issues without the app context switch?

[julian-gargicevich]

Thanks for the reply @cheenamalhotra, unfortunantely I'm still getting the same error. I tried referencing both versions with/without the AppContext line and I'm still getting the same error.

Happy to provide a sample project.

[DavoudEshtehari]

I couldn't find any relation with "Switch.Microsoft.Data.SqlClient.TruncateScaledDecimal" switch. I was able to prepare a repro that shows it occurs since MDS V1.1.0 and SDS 4.8 which I've tested.
@julian-gargicevich could you verify if this demonstrates your issue, please?

using SqlConnection cn = new SqlConnection("Data Source=localhost;Database=Northwind;Integrated Security=true;");
cn.Open();
using SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT cast(0 as decimal(38, 24)) [Value] INTO #tmp;";
cmd.ExecuteNonQuery();

decimal testVal = 22351.969005269646026090843821m;
var p = new SqlParameter("@Value", null);
p.Precision = 18;
p.Scale = 0;
cmd.Parameters.Add(p);
p.Value = testVal;
cmd.CommandText = $"INSERT INTO #tmp ([Value]) VALUES(@Value)";
cmd.ExecuteNonQuery();

[julian-gargicevich]

@DavoudEshtehari Yes, that's the same Exception I'm getting.

[ajcvickers]

Just to add a bit more clarity on this issue. The change that is causing this is that EF Core is now setting the precision and scale for DbParameters when previously it did not. We made this change after the change for rounding was implemented in SqlClient to support always-encrypted. However, this now reveals that SqlClient throws for any decimal value with digits after the decimal point (e.g. 3.14) when the scale is set to zero (e.g. "decimal(18,0)") while it does not throw if the scale is anything other than zero (e.g. "decimal(18,1)".

I expect that is was "by-design" many years ago, but I would assert that it's not intuitive or helpful behavior, and that (18,0) should behave the same as (18,1)--i.e. neither of them should throw, but rather both should round. I think SqlClient could make this change without it being considered a breaking change because, generally speaking, changes that stop throwing exceptions are not considered breaking. (However, in this case it could be breaking if code is relying on this for validation, so it might be worth documenting it as a break anyway.)

We can't really do anything in EF other than stop setting precision and scale again, but doing this would re-break always -encrypted. So I'm hoping SqlClient can make the change. Hope that helps!