Update only the changes that are different

Question

I have a Entity-Set employee_table, I'm getting the data through excel sheet which I have loaded in the memory and the user will click Save to save the changes in the db and its all good for the first time inserting records and no issue with that.

but how can I update only the changes that are made? meaning that, let say I have 10 rows and 5 columns and out of 10 rows say row 7th was modified and out of 5 column let say 3rd column was modified and I just need to update only those changes and keep the existing value of the other columns.

I can do with checking if (myexistingItem.Name != dbItem.Name) { //update } but its very tedious and not efficient and I'm sure there is a better way to handle.

here is what I got so far.

var excelData = SessionWrapper.GetSession_Model().DataModel.OrderBy(x => x.LocalName).ToList();;
var dbData = context.employee_master.OrderBy(x => x.localname).ToList();

employee_master = dbEntity = employee_master();

if (dbData.Count > 0)
{
   //update
   foreach (var dbItem in dbData)
   {
      foreach(var xlData in excelData)
      {
         if(dbItem.customer == xlData.Customer)
         {
            dbEntity.customer = xlData.Customer;
         }
         //...do check rest of the props....
         db.Entry(dbEntity).State = EntityState.Modified;
         db.employee_master.Add(dbEntity);
      }
   }

   //save
   db.SaveChanges();
}
else
{
  //insert
}

Answer 1

You can make this checking more generic with reflection.

Using this answer to get the value by property name.

public static object GetPropValue(object src, string propName)
{
    return src.GetType().GetProperty(propName).GetValue(src, null);
}

Using this answer to set the value by property name.

public static void SetPropertyValue(object obj, string propName, object value)
{
    obj.GetType().GetProperty(propName).SetValue(obj, value, null);
}

And this answer to list all properties

public static void CopyIfDifferent(Object target, Object source)
{
    foreach (var prop in target.GetType().GetProperties())
    {
        var targetValue = GetPropValue(target, prop.Name);
        var sourceValue = GetPropValue(source, prop.Name);
        if (!targetValue.Equals(sourceValue))
        {
            SetPropertyValue(target, prop.Name, sourceValue);
        }
    }
}

Note: If you need to exclude some properties, you can implement very easy by passsing the list of properties to the method and you can check in if to be excluded or not.

Answer 2

Update:

I am updating this answer to provide a little more context as to why I suggested not going with a hand-rolled reflection-based solution for now; I also want to clarify that there is nothing wrong with a such a solution per se, once you have identified that it fits the bill.

• First of all, I assume from the code that this is a work in progress and therefore not complete. In that case, I feel that the code doesn't need more complexity before it's done and a hand-rolled reflection-based approach is more code for you to write, test, debug and maintain.

• For example, right now you seem to have a situation where there is a simple 1:1 simple copy from the data in the excel to the data in the employee_master object. So in that case reflection seems like a no-brainer, because it saves you loads of boring manual property assignments.

• But what happens when HR (or whoever uses this app) come back to you with the requirement: If Field X is blank on the Excel sheet, then copy the value "Blank" to the target field, unless it's Friday, in which case copy the value "N.A".

• Now a generalised solution has to accomodate custom business logic and could start to get burdensome. I have been in this situation and unless you are very careful, it tends to end up with turning a mess in the long run.

• I just wanted to point this out - and recommend at least looking at Automapper, because this already provides one very proven way to solve your issue.

In terms of efficiency concerns, they are only mentioned because the question mentioned them, and I wanted to point out that there are greater inefficiencies at play in the code as posted as opposed to the inefficiency of manually typing 40+ property assignments, or indeed the concern of only updating changed fields.

Why not rewrite the loop:

foreach (var xlData in excelData)
{
    //find existing record in database data:
    var existing = dbData.FirstOrDefault(d=>d.customer==xlData.Customer);
    if(existing!=null)
    {
        //it already exists in database, update it
        //see below for notes on this.

    }
    else
    {
        //it doesn't exist, create employee_master and insert it to context
        //or perform validation to see if the insert can be done, etc.
    }
    //and commit:
    context.SaveChanges();
}

This lets you avoid the initial if(dbData.Count>0) because you will always insert any row from the excel sheet that doesn't have a matching entry in dbData, so you don't need a separate block of code for first-time insertion. It's also more efficient than the current loop because right now you are iterating every object in dbData for every object in xlData; that means if you have 1,000 items in each you have a million iterations...

Notes on the update process and efficiency in general

(Note: I know the question wasn't directly about efficiency, but since you mentioned it in the context of copying properties, I just wanted to give some food for thought)

• Unless you are building a system that has to do this operation for multiple entities, I'd caution against adding more complexity to your code by building a reflection-based property copier.
• If you consider the amount of properties you have to copy (i.e the number of foo.x = bar.x type statements) , and then consider the code required to have a robust, fully tested and provably efficient reflection-based property copier (i.e with built-in cache so you don't have to constantly re-reflect type properties, a mechanism to allow you to specify exceptions, handling for edge cases where for whatever unknown reason you discover that for random column X the value "null" is to be treated a little differently in some cases, etc), you may well find that the former is actually significantly less work :)
• Bear in mind that even the fastest reflection-based solution will always still be slower than a good old fashioned foo.x = bar.x assignment.
• By all means if you have to do this operation for 10 or 20 separate entities, consider the general case, otherwise my advice would be, manually write the property copy assignments, get it right and then think about generalising - or look at Automapper, for example.
• In terms of only updating field that have changed - I am not sure you even need to. If the record exists in the database, and the user has just presented a copy of that record which they claim to be the "correct" version of that object, then just copy all the values they gave and save them to the database.

• The reason I say this is because in all likelihood, the efficiency of only sending for example 4 modified fields versus 25 or whatever, pales into insignificance next to the overhead of the actual round-trip to the database itself; I'd be surprised if you were able to observe a meaningful performance increase in these kinds of operations by not sending all columns - unless of course all columns are NVARCHAR(MAX) or something :)

• If concurrency is an issue (i.e. other uses might be modifying the same data) then include a ROWVERSION type column in the database table, map it in Entity Framework, and handle the concurrency issues if and when they arise.