views:

441

answers:

3

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?

  • I'm guessing changing the field to 15,3 wouldn't fix the bug, it would just shift it 1 decimal place.
  • Overriding the OnMy_Property_NameChanged() would work for this property, but I have lots of them.

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.

+1  A: 

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).

Johannes Rudolph
if i update the database directly with 23.63636363636363 then it becomes 23.64.... which is ok. if i override the property then it would fix this property but its a big db with lots of 15,2 defined fields... I dont really want to do that for all of them. I would think if this is a Linq 2 Sql bug it would have been found before?? (thanks for the answer)
Paul Rowland
I suggest digging into the linq2sql internals using reflector if you feel confident about it. Obviously it's something about the query generator.
Johannes Rudolph
A: 

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.

Adeel
But then that column will be created as (15,3) in case we use automatic database creation.
Fyodor Soikin
A: 

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

Bevan