Linq SubmitChanges not updating certain fields

raz0rf1sh posted on Sunday, December 21, 2008

I am having an issue where I am trying to update a database table using Linq. I outputted the Log file, which looks like:

UPDATE [dbo].[InvoiceItem]
SET [Number] = @p1, [InvoiceId] = @p2, [ItemId] = @p3, [Description] = @p4, [Units] = @p5, [ModifiedBy] = @p6, [ModifiedDate] = @p7, [CreatedBy] = @p8, [CreatedDate] = @p9
WHERE [InvoiceItemId] = @p0
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [6b908420-b20d-4f8e-a9fa-0b64ae65eb4c]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p2: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [780586ae-546c-4ecc-964b-972de1e0a171]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [fc0e989f-609d-4d8a-b8c2-5d33f9604fb7]
-- @p4: Input VarChar (Size = 35; Prec = 0; Scale = 0) [Software Development Payment 3 of 3]
-- @p5: Input Decimal (Size = 0; Prec = 10; Scale = 4) [1.0000]
-- @p6: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [3766787e-4166-4b37-bd05-26a0bafd3d9e]
-- @p7: Input DateTime (Size = 0; Prec = 0; Scale = 0) [12/21/2008 9:41:53 PM]
-- @p8: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [6b77840e-effe-4847-a611-f79371d45126]
-- @p9: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/24/2008 11:21:00 AM]
For some reason it is skipping two fields, one called UnitPrice and another called Price. I am using CSLA for my application framework. Here is the code that executing the Linq query.

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)
{
data.Number = this.ReadProperty(NumberProperty);
data.InvoiceId = parent.InvoiceId;
data.ItemId = this.ReadProperty(ItemIdProperty);
data.Description = this.ReadProperty(DescriptionProperty);
data.Units = this.ReadProperty(UnitsProperty);
data.UnitPrice = this.ReadProperty(UnitPriceProperty);
data.Price = this.ReadProperty(PriceProperty);
data.ModifiedBy = parent.ModifiedBy;
data.ModifiedDate = parent.ModifiedDate;
data.CreatedBy = this.ReadProperty(CreatedByProperty);
data.CreatedDate = this.ReadProperty(CreatedDateProperty);
}
}
}

Any ideas why this might be happening? I step through and it looks like the properties are updating correctly in the business objects. Any help would be appreciated!

raz0rf1sh replied on Sunday, December 21, 2008

This only seems to happen when I try to set the value to ZERO, any other values saves correctly.

raz0rf1sh replied on Sunday, December 21, 2008

In fact ... anytime I try to zero out a decimal value it is ignored in the Linq query.

JoeFallon1 replied on Tuesday, December 23, 2008

As a test, have you tried to use 0.0 instead of 0?

Or CDec(0)?

Joe

 

raz0rf1sh replied on Tuesday, December 23, 2008

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!

Does this sound right?

ajj3085 replied on Tuesday, December 23, 2008

Argh, I should have seen this, because I had hit this problem as well.  That's how linq works; you could get the object via linq, then change properties, and call SubmitChanges as well.  In that case you don't need Attach at all.

An alternative is to build up another object with the original values; I sort of do this, but I ONLY set the key properties and leave everything else at default.  (all my properties are Nullable, even if the backing field won't accept null in the database).  Then I use ctx.Attach( data, original ), and that works as well.

Sorry for not catching it..

raz0rf1sh replied on Tuesday, December 23, 2008

I thought about getting the object again ... but wouldn't that be an excessive hit? 

I just want to be clear ... so while you database may not except NULL values, you allow your entities to?  This makes sense ... and would also fix my problem.

Thanks for your input!

ajj3085 replied on Tuesday, December 23, 2008

Yes, it would be  a hit.  Whether that matters in the grand scheme though depends on your application.  In my case, my application is running in IIS on the database server and so the "Shared memory" connection should be used.  I would hope that's much less overhead than tcp/ip. 

And yes, all of my entities accept null for every property.  It doesn't seem to cause linq any trouble, and I'd rather an exception due to not supplying a value instead of accidentally have the default .net value go.

HTH
Andy

raz0rf1sh replied on Tuesday, December 23, 2008

I tried the NULL route, but I would have to make some serious modifications to my code because the data types are all NULLABLE now. I would have to test for that ... how do you handle this Andy?

ajj3085 replied on Friday, December 26, 2008

It depends.  If the field can't ever be nullable, my data access code assumes it won't be null, and blindly calls the .Value member when loading.  Otherwise, the ?? operator is your friend, assuming that the Csla property can't be null.  You could do:

using( BypassPropertyChecks ) {
    IsEnabled = dataObj.IsEnabled ?? false;
}

That will load the value of IsEnabled, or if it's null IsEnabled will be set to false.

So.. it again depends on your application. 

HTH
Andy

raz0rf1sh replied on Friday, December 26, 2008

I'm going to give this try ...

Another point of interest is that when I do the following:

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

My child business objects start throwing error messages about certain db values ... again ... not being set.

Ugh! I'm trying to get away from stored procedures ... but struggling!

ajj3085 replied on Monday, December 29, 2008

Well, only do it if it makes sense and you think you'll gain something.

I didn't have much luck with the Attach( object, bool ) overload; it still never seemed to work quite right.  That's when I moved to using the Attach( object, object ) overload, and it seems to work for me.  You need to set any primary key values and version fields for the second object though, or it won't work correctly either.  linq will complain about primary key's changing, or that the row version has changed.

I do use stored procedures for all database changes as well, and I don't intend to stop doing that. 

HTH
Andy