NHibernate 3.0+ rounding a decimal data to 5 decimal places

Note: Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

Problem: One of our team member was trying to to insert data into a SQL Server database from an application that is using NHibernate ver. 3.1. The format for a decimal datatype value is being forced to decimal(16,5) always, regardless of the SCALE defined for the column (ex: DECIMAL_VALUE1 decimal(16,6) in the SQL Server table. This results in decmial values being truncated before being written to the SQL Server database.

Here is the sample sql profiler trace:

exec sp_executesql N’INSERT INTO TEST_TABLE( DECIMAL_VALUE1, DECIMAL_VALUE2, LONG_VALUE1) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY()’ , N’@p0 decimal(16,5),@p1 decimal(16,5),@p2 bigint’, @p0 = 9.94434, @p1 = 9.94434, @p2 = 5583

Hence, the gotcha is that NHibernate by default sets the SCALE to “5”, if we haven’t told any SCALE in entity mapping class for the particular property.

The reason behind: [NH-1594] – When setting property in hbm type=”Decimal(precision, scale)” – “DECIMAL(19,5)” is always generated Ref: https://github.com/leemhenson/nhibernate/blob/master/nhibernate/releasenotes.txt

Solution: Enforcing scale to “6” in the fluent mapping or .hbm like below, it works great.

Map(x => x.DECIMAL_VALUE1).Scale(6);

<property name=”DECIMAL_VALUE1″ scale=”6″ />

Happy coding!