CODEWITHSUNDEEP
wcfentity-frameworkodata
decho
decho

Reputation: 331

Update only modified column

I have OData service ( WCF Data Services 5.6 ) using EF and Oracle Data Provider for .Net. When i trace queries from service to db server i see that even i modified only 1 column, UPDATE contains all the columns from table. For example - i can have table with 5 columns, modify 1 on client and call SaveChanges(). On server side , in oracle i get

UPDATE col1=x, col2=x,col3=x,col4=x WHERE ....

I read a lot in internet about this problem, but still didn't find any clear solution for this.For sure, i'm not the only one with this problem, but may be someone have ideas how to fix this?

I see that EF6 will come soon, and WCF Data Services team release Alpha for EF6, but , first its still alfa, and EF6 is RC, second, it has some problems, and third - there are no guarantee that same problem not exist in new versions.

Hope someone has right answer ...

Upvotes: 0

Views: 656

Answers (2)

tne
tne

Reputation: 7261

It works today, I just generated this SQL query on a test dataset using EF5 and DS5.6:

exec sp_executesql N'update [dbo].[People]
set [City] = @0
where ([Id] = @1)
',N'@0 nvarchar(max) ,@1 bigint',@0=N'Mashville',@1=1

OData query: http://localhost:50000/People(1L)

OData payload: { "City": "Mashville" }

Maybe you forgot to use the HTTP PATCH method instead of PUT?

Upvotes: 0

decho
decho

Reputation: 331

Ok, seems i like at least one way how to do this. In WCF service, in ChangeInterceptor :

  [ChangeInterceptor("Entity")]
        public void OnChange(Entity item, UpdateOperations operations)
        {
            Dictionary<string, object> changes = new Dictionary<string, object>();
            foreach (String propName in this.CurrentDataSource.Entry(item).CurrentValues.PropertyNames)
            {
                if (this.CurrentDataSource.Entry(item).Property(propName).IsModified)
                    changes.Add(propName, this.CurrentDataSource.Entry(item).Property(propName).CurrentValue);
            }
            this.CurrentDataSource.Entry(item).State = System.Data.EntityState.Unchanged;
            var arrayOfAllChangedProps = changes.Keys.ToArray();
            foreach(string prop in arrayOfAllChangedProps)
                this.CurrentDataSource.Entry(item).Property(prop).CurrentValue = changes[prop];
            return;
        }

We get modified values, add them to the dictionary ( can be any other collection ), then reset flag for modified state. If flag is reset before this, then we cannot get changes. When flag is reset, setting current value to saved one before resetting flag for modification.

After all of this , we will see that only modified columns are in UPDATE statement.

I really cannot understand, why this is not the default behavior? And why all this magic should be done. Plus , i don't know if this is the correct way. But at least this work for me.

Upvotes: 1

Related Questions