5

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:

SellPrice specify a scale of 19,4

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

SellPrice shows lots of decimals

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.

Pierre-Alain Vigeant
  • 22,635
  • 8
  • 65
  • 101
  • Does this post help? http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c423a005-6a42-4f8d-aab6-e13fe3bdffba – mbeckish Feb 19 '13 at 20:30

1 Answers1

3

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

Community
  • 1
  • 1
DiskJunky
  • 4,750
  • 3
  • 37
  • 66
  • 2
    I tried to set the fields to money instead. It does not work. I also tried to do another Entity Model just to see how they would map and it does display a Precision of 19 and a scale of 4, but the Sql Profiler still shows Decimal(19,2). It must be somewhere else, but I cannot find where. – Pierre-Alain Vigeant Mar 08 '13 at 15:17
  • 1
    @Pierre-AlainVigeant, are you explicitly setting the Scale and Precision or are these set automatically for you when you try to update the EF diagram/DB? On another note, are you creating the database first or the model? – DiskJunky Mar 08 '13 at 18:44