LINQ to SQL - Decimal datatype truncated instead o

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

I had the same issue. You can control the precision with which LINQ/EF exports data to SQL by adding an EntityConfiguration to your Entity:

namespace your.namespace.in.here
{
    class foobarEntityConfiguration : EntityTypeConfiguration<foobarEntity>
    {
        public foobarEntityConfiguration()
        {
            this.Property(x => x.foobarPropertyBeingTruncated)
                .HasPrecision(18, 5);
        }
    }
}

As LINQ/EF has this bug, and as a general rule, I find that keeping as much precision as possible until display time is a good thing. Let the presentation layer do any rounding/truncation as required. Then if there is a post-LINQ-export calculation say in SQL or report builder, the pennies in your currencies are much more likely to match.

Note, in my case for no visible reason, LINQ was previously truncating at 2 decimal places. According to the documentation, LINQ/EF decides based on the precision of the decimals in question.

Answer 3

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.