Dangerous decimal fractional digit truncation when changing from EF core 3 to EF 5 on MS-SQL Server

[bennil]

Today I faced a dangerous production problem after upgrading form .net 3.1 (Microsoft.EntityFrameworkCore.SqlServer 3.1.8) to .net 5 (Microsoft.EntityFrameworkCore.SqlServer 5.0.1).

Table decimal values are truncated to only two fractional digits!

I could reproduce the changed behavior in a short sample project:
.net 5 output:
Expected 5.1234567; Received Value 1: 5.1200000
Expected 5.1234567891; Received Value 2: 5.1200000000
Expected 5.12345; Received Value 3: 5.12000

With .net core 3.1 the decimal fractional digits are not truncated and work as expected.

Sample reproduce project

1. Download and extract the attached zip file
   EfCore5InvalidDecimalPrecisionTest.zip
2. Execute the datebase SQL create script under
   EfCorePrecisionTest.Common/CreateDecimalTestDb.sql
3. Adjust the connection string in EfCorePrecisionTest.Common/TestConfig.DbConnectionString to your DB environment
4. Run the "EfCore5InvalidDecimalPrecisionTest" console app project
5. Run the "EfCore3DecimalPrecisionTest" console app project

Affected Database Table Column Types

[DecimalValue1] [decimal](10, 7) NULL,
[DecimalValue2] [decimal](18, 10) NULL,
[DecimalValue3] [decimal](18, 5) NULL,

Version Info

Tested with Microsoft SQL Server Express (64-bit) 14.0.3048.4 on Ubuntu (16.04) (Docker)

[ajcvickers]

This is a breaking change that we be documenting shortly. See dotnet/EntityFramework.Docs#2923.

[bennil]

But if this is not a bug it would be a major breaking change. It will produce much harm in production environments without throwing an error.
Why is the new default only 2 fractional digits ragardless the DB type?

[ajcvickers]

@bennil This is a breaking change. The default has not changed--it has always been 18, 2. However, if your database column is set up with something different from the default, and you don't tell EF this in the mappings, then EF would previously send the full value and let the database sort it out. Now the default is respected on the EF and SqlClient sides as well, so if you have your model configured differently from the database you will experience this breaking change. You can get back to the old behavior by making sure the types are fully specified in the EF model. (Note that in previous versions there was already a warning when using a decimal column without an explicit type mapping.)

[bennil]

@ajcvickers Thanks for the explanation. In my case I don't use the ef tooling to gernerate my database. So I was not aware of that warning.
If you only see EF as a data access level tool and your actual database schema is the master, then this can be problematic.

[ajcvickers]

@bennil The warning is generated in runtime logs. It is not related to the EF tooling.

[bennil]

OK never paid attention to these logs :-(

But I still see it as default change for all who did not initially create their database with EF tooling.