Linq-to-Sql SubmitChanges not updating fields ... why?

Question

I posted this question yesterday evening, which has led me to discover a huge problem!

I have a decimal column in my database called Units, anytime I set the value of the column to a NON ZERO, and SubmitChanges the column updates with the new value. If I try to set the value of the column to ZERO, the SubmitChanges does not update the column.

data.Units = this.ReadProperty<decimal>(UnitsProperty);
data.UnitPrice = this.ReadProperty<decimal>(UnitPriceProperty);
data.Price = this.ReadProperty<decimal>(PriceProperty);

I've taken a look at the DataContext log and I can see that the field with the ZERO value is not included in the query. Even if I try to hard code the change Linq ignores it.

data.Units = 0;
data.UnitPrice = 0;
data.Price = 0;

Needless to say this is killing me! Any ideas why this happening?

Solution

I figured out my problem with the help of the SO community. My problem was being caused by the fact when I created my entity to attach, the default value of the column was set to zero, so when it tried to assign the value to zero ... LinqToSql says hey ... nothing changed, so I am not updating the value.

What I am doing now ... just to make it work is the following:

ctx.DataContext.InvoiceItems.Attach(data, true);

That seems to force all the values to write themselves to the database. This works for now.

Solution 1

I figured out my problem with the help of the SO community. My problem was being caused by the fact when I created my entity to attach, the default value of the column was set to zero, so when it tried to assign the value to zero ... LinqToSql says hey ... nothing changed, so I am not updating the value.

What I am doing now ... just to make it work is the following:

ctx.DataContext.InvoiceItems.Attach(data, true);

That seems to force all the values to write themselves to the database. This works for now.

Solution 2

I have tried to reproduce this with a the following code, but for me it works.

using (DataClasses1DataContext ctx = new DataClasses1DataContext())
{
    var obj = ctx.DecimalColumnTables.First();
    Debug.Assert(obj.B != 0);
    obj.B = 0;
    ctx.SubmitChanges();
}

So I think there must be something special in your domain that causes this. I suggest you to create a such simple repro with your domain model and see what happens.

LINQ to SQL ignores updates to the current value, so if the field was already zero, you may not see any updates.

Off: The OR/M you use is LINQ to SQL. LINQ is the name of the querying capability in .NET, but LINQ does not define nor implement any update logic. So the issue relates to LINQ to SQL, not LINQ.

Solution 3

Obvious question, but are you sure the column is mapped in the dbml / mapping file?

Also - is it a calculated column? (i.e. price => units * unitprice)

Solution 4

Some more information ... I figured out my problem ... it's more of a lack of understanding about LinqToSql ... where I am doing:

private void Child_Update(Invoice parent)
{
      using (var ctx = Csla.Data.ContextManager
           .GetManager(Database.ApplicationConnection, false))
      {
           var data = new Gimli.Data.InvoiceItem()
           {
                InvoiceItemId = ReadProperty(InvoiceItemIdProperty)
           };

           ctx.DataContext.InvoiceItems.Attach(data);

           if (this.IsSelfDirty)
           {
                // Update properties
           }
     }
}

I thought this would load the original values ... what happens is that it creates a new object with default values ... empty values, like 0 for decimals, Guid.Empty for uniqueidentifiers and so on.

So when it updates the properties it sees the Units already as 0 and it sets it to zero. Well LinqToSql doesn't recognize this as a change so it doesn't up date the field. So what I have had to do is the following:

ctx.DataContext.InvoiceItems.Attach(data, true);

Now all the modifications are generated in the update statement whether there is really a change or not. This works ... seems a bit hackish!

Solution 5

The correct answer is as many pointed out to use the special overload of Attach which accepts a boolean parameter to consider it as modified, (make the mistake of using another overload and it simply won't work):

ctx.DataContext.InvoiceItems.Attach(data, true);

Note however that you still might need to have a "Version" column in the table of type "timestamp".

View more solutions