Entity Framework rounds at two decimal when everything is set for four decimal

Question

I am trying to understand why Entity Framework 4 is rounding to two decimals when in both the EF schema model and in the database, the precision is set to 4.

This is my schema definition for one of the decimal field:

This is my database definition

CREATE TABLE OrderItems(
....
[SellPrice] [decimal](19, 4) NOT NULL,
....

When I execute my insert query after computing the sell price of the product, I see that there is enough decimal

The MiniProfiler show my query and it displays that the value has its decimal

DECLARE ...
        @15 Decimal = '100,54347826086956521739130435',
        ...

insert [dbo].[OrderItems](..., [SellPrice], ...)
values (..., @15, ....)
select [OrderItemId]
from [dbo].[OrderItems]
where @@ROWCOUNT > 0 and [OrderItemId] = scope_identity()

But when I look through the Microsoft Sql Profiler, the SellPrice is rounded

exec sp_executesql N'insert [dbo].[OrderItems](..., [SellPrice], ...)
values (..., @15, ...)',
...,@15=100.54,...'

I have trouble finding where the value is being rounded.

Answer 1

at a guess, I'd say that your SQL datatype should be money, not decimal as decimal in c# is not the same as decimal SQL. In particular, if you look at the MSDN documentation (http://msdn.microsoft.com/en-us/library/ms187746.aspx) for the TSQL decimal type is states;

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision.

It's not explicitly stated that this is the cause of your problem but I'd take a good guess that it is. You might have more luck with the money datatype as that is most equivilant and that's also what the EF defaults to if you generate the model from an existing DB