Sql – LINQ to SQL – Decimal datatype truncated instead of rounded

linq-to-sql

I have a DB column of decimal(6,1)
When saving a record with LINQ to SQL, it truncates the value instead of rounding.

So for example, if a parameter passed to this column in the generated LINQ to SQL query has this value…
— @p1: Input Decimal (Size = 0; Prec = 6; Scale = 1) [222.259]
the data will show up in the DB as 222.2 instead of 222.3

Then, if I change the same column in the DB to be decimal(7,2) and I don't regenerate the LINQ to SQL classes, saving a record using LINQ will still truncate…
— @p1: Input Decimal (Size = 0; Prec = 6; Scale = 1) [222.259]
the data will show up in the DB as 222.20 instead of 222.26 (or 222.30)

Does anyone know if this is the correct behavior for LINQ? Or is the SqlServer provider? It doesn't behave the same way as writing a query by hand in mgmt studio which is why I'm confused on why it is truncating instead of rounding.

The following query in mgmt studio…
UPDATE TheTable SET TheDecimalColumn = 222.259
will set the val to 222.3 when the column is decimal(6,1) and 222.26 when it is decimal(7,2)

Thanks

Best Answer

It's the sqlparameter, and it has to do this, as rounding a fraction is not standarized: there are different kind of rounding algorithms, which are used in different kind of areas, like banking uses different kind of rounding standards than others.

If you want rounding, define it yourself by rounding the value before you set the value in the entity. It's logical as you defined a single digit as scale, so .256 doesn't fit in a single digit, which means you either should get an exception (linq to sql doesn't support in-memory validation for this) or it gets truncated in the lower levels.

Related Topic