LINQ to SQL - Decimal datatype truncated instead of rounded

Question

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

Answer 1

Have you tried pulling the raw SQL out of the LINQ query with the DataContext.Log? If the query works when you do it manually through management studio then the provider may be truncating the value in the query. Here's an article on how to get the raw SQL. You may also want to check that the type of the column is represented properly in the .dlinq file.

PS- Found your question.. small web eh?

Answer 2

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.