After recently updating my database from the old model-first approach that relied on EDMX files to code first, I discovered a rather nasty little problem with regard to precision. In my work environment, I am using the T-SQL money data type for any data column that actually relates to money. I understand there is a healthy debate about whether you should be using the money data type at all, but many of the databases I’m managing are over 10 years old and have been built with the money data type from the beginning (so I’m going to stick with it).
Now, the .Net CLR doesn’t have a “Money” type and instead relies on the decimal datatype to represent money. Therefore, if you setup a mapping in EntityFramework as such:
What can happen is you can lose precision when persisting to the database. Here is the example that eventually caught my attention:
- We had an object where the money column had 4 places of precision (for example, $10.0025).
- When instantiating this item as a POCO, the price would correctly be set to $10.0025.
- However, when saving the object to the database, the price would save as $10.00. To make sure the value was being sent to EF correctly, I ran the debugger and checked the value right before the call to persist was made. The debugger showed the price value as a decimal data type with value $10.0025.
The important piece that was missing in the above code was this restriction during mapping (see highlighted line):
Now, the values persist correctly.
In order to quickly identify all of the tables in a database that have the type of money, you can run the following SQL code against your database:
Then, you just need to add the constraints to your entity mappings.