CODEWITHSUNDEEP
c#sql-serverlinq-to-sqlrounding
Paul Rowland
Paul Rowland

Reputation: 8352

Decimal places not rounding correctly - is this a LINQ to SQL bug?

My database field (sql server 2005) is defined with numeric(15,2).

The LINQ 2 SQL generated property is

    [Column(Storage="_My_Property_Name", DbType="Decimal(15,2)", UpdateCheck=UpdateCheck.Never)]
    public System.Nullable<decimal> My_Property_Name
    {
        get
        {
            return this._My_Property_Name;
        }
        set
        {
            if ((this._My_Property_Name != value))
            {
                this.OnMy_Property_NameChanging(value);
                this.SendPropertyChanging();
                this._My_Property_Name = value;
                this.SendPropertyChanged("My_Property_Name");
                this.OnMy_Property_NameChanged();
            }
        }
    }

In debug I check the entity value for this property = 23.6363636363 (etc)

I then step over context.SubmitChanges()

I have SQL Profiler running and this is the update statement.

exec sp_executesql N'
UPDATE [Staging].[My_Table_Name]
    SET [LAST_UPDATE_DATE] = @p2, [Field1] = @p3, [Field2] = @p4, [Field3] = @p5, [Field4] = @p6, [Field5] = @p7, [Field6] = @p8, [Field7] = @p9
WHERE ([Id] = @p0) AND ([RecordVersion] = @p1)

SELECT [t1].[RecordVersion] FROM [Staging].[My_Table_Name] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[Id] = @p10)',N'@p0 int,@p1 timestamp,@p2 datetime,@p3 decimal(21,8),@p4 decimal(21,8),@p5 decimal(21,8),@p6 decimal(21,8), @p7 decimal(21,8),@p8 decimal(21,8),@p9 decimal(15,2),@p10 int',@p0=2935,@p1=0x0000000000323018,@p2='2010-02-26 16:49:21:690', @p3=99.99992307,@p4=99.99992307,@p5=99.99992307,@p6=99.99992307,@p7=99.99992307,@p8=99.99992307,
@p9=23.63,@p10=2935

As you can see @p9 = 23.63, I would expect it to be 23.64.

Update

My question is,

If this is a LINQ to SQL bug I would expect it to be a known one, where would I find this out; is there a maintained bug list somewhere?

Also what would be the best work around?

Update 2

this didn't work either, it goes into this piece of code before submitchanges and appears to work, but the generated update sql still has the truncated value, not this updated rounded value.

partial void OnMy_Property_Name_ChangingChanging(decimal? value)
{
    if (!value.HasValue)
    {
        return;
    }
    value = 
        Math.Round(value.Value, 2, MidpointRounding.AwayFromZero);
}

The fix I've got at the moment is just to update the entity value directly.

Upvotes: 8

Views: 4256

Answers (3)

Bevan
Bevan

Reputation: 44327

It looks to me as though Link to SQL is truncating the value to 2 decimal places instead of rounding it.

Upvotes: 1

Adeel
Adeel

Reputation: 19238

it seems the bug of LINQ. you need to change DBType from Decimal(15,2) to Decimal(15,3). That is increment 1 extra precision than your column precision in database.

Upvotes: 0

Johannes Rudolph
Johannes Rudolph

Reputation: 35761

MSDN says:

Decimal and Money Types The default precision of SQL Server DECIMAL type (18 decimal digits to the left and right of the decimal point) is much smaller than the precision of the CLR Decimal type that it is paired with by default. This can result in precision loss when you save data to the database. However, just the opposite can happen if the SQL Server DECIMAL type is configured with greater than 29 digits of precision. When a SQL Server DECIMAL type has been configured with a greater precision than the CLR System.Decimal, precision loss can occur when retrieving data from the database.

You could handle this via overriding OnMy_Property_NameChanged() and rounding it there. Make sure you specify the correct rounding mode (to even or from zero).

Upvotes: 1

Related Questions