SQL Server

Working with decimal precision in .NET with MSSQL Server and Entity Framework

Careful with your precision and scale

Credit: Wikimedia

It seems like an elementary thing but dealing with numbers beyond whole integers in software is tricky. Differences between rounding in code vs rounding in database storage can catch you off guard if you don't pay attention. Throw in a layer of abstraction like the .NET Entity Framework and it can be easy to make a mistake. If you're using a code first approach to developing with Entity Framework, it's even more likely you'll make a mistake with decimal precision. 

The problem, especially with code first, is that the default precision and scale values are usually not accurate enough for most situations. When you define a column in MS SQL server as either decimal or numeric (these are both options but do the same thing) you need to define a fixed precision and scale value for that column. The precision is the total number of digits, including decimals, that you want to store. The scale is the number of digits out of the precision that you want to use for decimal places. That distinction that the precision includes the decimal places is important as people tend to forget that frequently. 


  MyDecimalColumn decimal(5,2)

The column MyDecimalColumn is defined with a data type of decimal, a precision of 5, and a scale of 2. This means that the column will store a maximum of 5 digits, 2 of which will always be after the decimal point - e.g. 103.27

When you are using the code first approach to development you would define your model in code and give your property a type of decimal.

public decimal myDecimalProperty {get; set;}

When you apply this code model to the database to create the table the default precision and scale will be used which are decimal(18,2) and you may not realize that. Then, as you're going along coding your application, you end up with a value in your myDecimalProperty of something like 678.0274 that you want to persist to the database. When you save that value your precision will be lost and instead of storing the full decimal, the rounded decimal will be stored instead, 678.03. 

Unfortunately, using code first, there is no out of the box way to specify decimal precision on your property using decorators. You instead need to define precision in your model builder, which is fine, but it moves the definition away from the property where it's harder to notice. An alternate solution that we use on our code first projects is to define a custom property decorator that can be used to define the precision of decimal types. Then you register the precision filter once in your model builder and it's good to go. Here's what that class looks like:

And here's how to use it.

Once you know to watch out for it you'll start all of your projects with the precision of decimal types in mind, but I'd guess that the default precision has caught everybody at one time or another. 

ITWorld DealPost: The best in tech deals and discounts.