Data precision — Entity Framework
After solving a problem recently regarding monetary information on SQL Server using Entity Framework, I decided to write this quick article to give some tips on Data Precision using Entity Framework Core.
As with any other ORM (Object-Relation Mapping), Entity Framework obeys certain default configurations that we can customize if we want, such as identity for SQL Server, nullable, data precision, name conventions, etc.
Obviously, C# data types and Database column types are not always 100% compatible for SQL Server, Postgres, MySql, and other databases. Furthermore, the Entity Framework configuration may affect the mapping between C# data types and database columns as well as the maximum values and format might be different or not supported between the two sides of the integration: application and database.
Said that I’d like to propose a scenario where we have the following configuration for the test:
- SQL Server installed locally
- Console Application using .NET 5 version
- Entity Framework 5
- Code First for the Entity Framework approach
- Db context with a single entity called Payment, which contains a decimal property to store monetary values
Note: this post does not have the purpose of demonstrating how to configure Entity Framework from scratch for the database first approach. So, it will be focused on data precision only. To learn with more details on Entity Framework, please see in the end of the article for the official documentation.
This is how the Payment class looks like:
After configuring the System Context class, connectionstring to a local SQL Server database and running the add-migration command, this is how the migration file looks like:
With the migrations applied, I’ve created a simple method in the Console Application to insert payment test data, as seen in the following code:
After running this code only once, I got the following result on the database (Payments table) for the ValueDollar column:
The actual value set on the Payment object in C# was “5.4647”, not “5.46”. The number on the database should have 4 decimal places. For accountability and financial calculations involving huge amounts of money, the precision in terms of decimal values is extremely important because it may affect the final result. The same happens for academic scientific experiments that require high precision. How, can we fix that on Entity Framework?
On the OnModelCreating class you can specify the precision for certain types, including decimal, as seen in the following image:
In this case, I specified the precision of the ValueDollar property with four decimal places. After creating a new migration on Entity Framework and applying the changes to the database, I’ve run the application again and that’s the result:
The SQL Server is respecting the precision I want through Entity Framework and added extra zeros to the existing data in order to keep the same precision for all the records.
The same process can be applied to other types in C#, such as DateTime, which may be required to calculate nanoseconds in complex time measures.
Thank you for reading this quick article until the end. If you have any questions or comments, please leave them here. I’ll be glad to chat about it.
Extra information:
I’m glad to announce that I have my first book published. It is a deep dive hands-on through the most common Design Patterns used in .NET applications. The book contains hundreds of code samples and explanations based on real-world scenarios. It also has many examples of Object-Oriented Programming, SOLID principles, and all the path to get yourself familiar with .NET 5 and C#. Check it out:
Twitter: https://twitter.com/alemalavasi
Linkedin: https://www.linkedin.com/in/alexandremalavasi/
Youtube: https://www.youtube.com/channel/UC-KFGgYiot1eA8QFqIgLmqA
Facebook: https://www.facebook.com/alexandre.malavasi.dev
Thank you.
