Problem mapping decimal to numeric(9, 0) in EF Core 5.0

[RobertSmits]

File a bug

Context

We use EF Core database first, so our model is reverse engineerd using dotnet ef dbcontext scaffold.
This maps our numeric fields of type numeric(9, 0) to C# decimals.
This has always worked in EF Core until EF Core 5.

Problem

When trying to create or update an entity where the value is the max length of 9, the action fails.
I have found that adding or leaving out the decimal place has influence on this behaviour.

For example:

Value	Working
999000169.0	No
999000169	Yes
89000169.0	Yes

This makes me believe only the precision of the decimal gets checked without checking the decimals.
In my 3 examples only the first one has a precision of 10 where the other ones both have a precision of 9.

I believe this is a bug in EF Core 5 as the last example proves that the actual presence of the .0 has no effect
on whether the value can be inserted. Ignoring the .0 our value 999000169.0 also ends with a precision of 9
and should be able to get inserted.

Further, this has worked in EF Core up to 3.1 and I can not find anything in the EF Core 5 changelog
regarding breaking changes around this topic.

Example

In my example I have the same small project using EF Core 3.1 to show the working version
and 5.0 to show the exception.

Stacktrace

----- Exception -----
An error occurred while updating the entries. See the inner exception for details.
   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 InsertTest.Program.Main(String[] args) in D:\Sources\playground\InsertTest\InsertTest\Program.cs:line 23

----- Inner Exception -----
Parameter value '999000169.0' 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)

Provider and version information

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

[ajcvickers]

Notes for triage: In 5.0, we started setting the precision on parameters for decimal type mappings. This causes SqlClient to throw.

Query from 3.1:

      Executing DbCommand [Parameters=[@p0='425524ed-b113-4dfa-ae43-6606d4240556', @p1='999000169.0' (Nullable = true)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Test] ([Id], [Value])
      VALUES (@p0, @p1);

Query from 5.0:

      Executing DbCommand [Parameters=[@p0='425524ed-b113-4dfa-ae43-6606d4240556', @p1='999000169.0' (Nullable = true) (Precision = 9)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Test] ([Id], [Value])
      VALUES (@p0, @p1);

[ajcvickers]

Notes from triage: Always encrypted means that we can no longer avoid setting parameter facets in cases like this. Unfortunately, this causes SqlClient to throw in some cases where it previously would not. We will document this as a breaking change in EF Core 5.0.

@cheenamalhotra @David-Engel Do you have any thoughts about whether or not the underlying SqlClient behavior is correct? Specifically, should it be throwing for the "999000169.0" value?

[cheenamalhotra]

cc @karinazhou

[karinazhou]

Hi,

According to SQL Server docs, the precision

This number includes both the left and the right sides of the decimal point.

"999000169.0" will fail as expected for Precision = 9 .

Thanks,

[ajcvickers]

Moving to the docs repo to document this as a breaking change.

[cheenamalhotra]

Based on Karina's comment above, it looks like precision set here is incorrect. SqlClient cannot change it, and server will fail.
EF Core needs to account for digits after decimal too, even if it's 0.