Cannot write decimal.MaxValue if precision/scale are set on SqlParameter

[roji]

It seems like it's possible to write decimal.Max into a decimal(38, 4) column, as long as Precision and Scale aren't set on the parameter. The moment they are set, an exception is thrown. In EF Core, we always set the Precision/Scale because it's necessary for Always Encrypted to function correctly.

Exception:

Unhandled exception. 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, SqlCommand command)
   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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 24
   at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 24
   at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 24
   at Program.<Main>(String[] args)

Full repro:

await using var conn = new SqlConnection("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false");
await conn.OpenAsync();

await using var command = new SqlCommand(@"
DROP TABLE IF EXISTS data;
CREATE TABLE data (foo decimal(38,4))", conn);
await command.ExecuteNonQueryAsync();

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

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

Originally reported in dotnet/efcore#28240

[DavoudEshtehari]

Thanks @roji, We'll check and will get back to you.

[artur-yakupov]

It seems that some logic is incorrect in sqlbuffer or somewhere else. There were some issues years ago about precision and scale and some fixes have been made.
Things that i have read about: decimal(38,20) to .net decimal , decimal(38,18) to net and one more wich i cant find now.

Seems some fixes helped, since i can save 2342342,35467895678945678956 now into (38,20), but i accidentally got new bug.
i am using sql server and ef core 7.0.11. My database decimal type is decimal(38,20) and i am getting overflow exception when trying to save 9999999999999 (13 nines, notice that 12 nines is ok) for example.

i have quick look at sqlbuffer but could not find bug yet

[DavoudEshtehari]

For the record, when the scale is going to change SqlDecimal.AdjustScale is called. The return value is a SqlDecimal and an attempt to retrieve data from the Value property throws the overflow exception. This exception comes from System.Data.SqlTypes.SqlDecimal and I think the fix should be proposed from the related library.

[IT-CASADO]

I also run into this issue.

dotnet/efcore#33109

Why is this only Low Priority. How can we workaround this?

A possible mentioned workaround here is not a viable solution. See the comment by @roji here.

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.

[shidcordero]

It's been a while and haven't seen much update on this. We are also having issue storing decimal value with 28 precision on a decimal(38,10). Our code was heavy on EF and it will hard adding non-EF workaround. Any updates on this?

[DavoudEshtehari]

@shidcordero, @IT-CASADO this issue is related to underlying APIs and it would be better addressed in the related API, as I mentioned here.

@shidcordero If you have another issue, feel free to open a new issue and file your repro for further investigation.

[DennisTycho]

@shidcordero, @IT-CASADO this issue is related to underlying APIs and it would be better addressed in the related API, as I mentioned here.

@shidcordero If you have another issue, feel free to open a new issue and file your repro for further investigation.

What exactly do you mean by "related to underlying APIs" ? Are you saying that it cannot be fixed in SqlClient? and needs to be fixed elsewhere? what needs to be done to inform the relevant stakeholders so this can be fixed. I see this as a rather big issue.

[ekhtiari]

we still have this issue

[HUkiah]

结合 ChatGPT 和 DeepSeek，大概明白了这个问题。核心点是 SqlClient 尝试调整小数位导致中间结果溢出（Conversion overflow）。.NET 的 decimal 类型的上限大约是 7.9228 × 10^28，AdjustDecimalScale 处理 Scale 时，value 会乘以 10^scale ，大于 .NET 的 decimal 类型的上限。如果调整 scale = 0 上述抛异常的代码可以会正常工作。我没有看具体的源码，测试了几个边界情况符合上面的描述，另外不改变 scale 的情况下，可能需要自定义ValueConverter去处理