更新仅对不同的变化 [英] Update only the changes that are different

问题描述

我有一个实体集 employee_table ，我得到通过Excel表，我在记忆已装入的数据和用户会点击保存保存在数据库中，并与该改变的都好，第一次插入记录，也没有问题。

但我怎么能仅更新所做的更改？这意味着，让说我有10行5列，并从10行说，行第七届进行了修改，并从5列让说第3列被修改，我只是只需要更新这些变化，并保持其他的现有值列。

我可以做检查如果（myexistingItem.Name！= dbItem.Name）{//更新} ，但其非常繁琐和效率不高，我敢肯定有是一种更好的方式来处理。

下面是我得到那么远。

  VAR excelData = SessionWrapper.GetSession_Model（）DataModel.OrderBy（X =＆GT; x.LocalName）。.ToList（）;;
变种dbData = context.employee_master.OrderBy（X =＆GT; x.localname）.ToList（）;employee_master = dbEntity = employee_master（）;如果（dbData.Count大于0）
{
   //更新
   的foreach（在dbData VAR dbItem）
   {
      的foreach（在excelData VAR xlData）
      {
         如果（dbItem.customer == xlData.Customer）
         {
            dbEntity.customer = xlData.Customer;
         }
         //...do检查道具休息....
         db.Entry（dbEntity）.STATE = EntityState.Modified;
         db.employee_master.Add（dbEntity）;
      }
   }   //保存
   db.SaveChanges（）;
}
其他
{
  //插
}
 

解决方案

更新：

  

我更新这个答案提供一点更多上下文，为什么我建议不要与现在手卷基于反射的解决方案去;我也想澄清的是，没有什么错用这样的解决方案本身，一旦你已经确定，这符合该法案。

• 首先，我从code，这是一项正在进行的工作，因此不完整的假设。在这种情况下，我觉得code不需要的更多的复杂性之前，它的完成和手卷基于反射的方法更code为你编写，测试，调试和维护。




• 例如，现在你的看起来的有一种情况，有一个简单的1：1的简单从数据在Excel中复制的数据在 employee_master 对象。因此，在这种情况下，反射似乎是一个没有脑子，因为它可以节省你无聊手动属性分配的负载。




• 但会发生什么时，HR（或任何人使用这个应用程序）回来找你的要求：如果字段X是在Excel工作表空白，则该值空白复制到目标领域，除非这是星期五，在这种情况下，复制值NA。的




• 现在的通用解决方案必须适应自定义业务逻辑，并可能开始变得沉重的负担。我一直在这种情况下，除非你非常小心，也容易与从长远看转向一个烂摊子就结了。




• 我只是想指出这一点 - 并建议至少在 Automapper 看，因为这已经提供了一个非常行之有效的方法来解决问题。

  

在效率方面的担忧，他们只提到，因为这个问题提到他们，我想指出的是，目前在在code发挥更大效率低下发布，而不是手动键入40+效率低属性分配，或者实际上仅更新变化领域的关注。

为什么不写循环：

 的foreach（在excelData VAR xlData）
{
    //查找数据库中的数据现有记录：
    变种现有= dbData.FirstOrDefault（D =＆GT; d.customer == xlData.Customer）;
    如果（现！= NULL）
    {
        //它已经存在于数据库中，更新
        //请参阅下面的这个笔记。    }
    其他
    {
        //它不存在，创建employee_master并将其插入上下文
        //或执行验证，以查看是否插入可以做到的，等等。
    }
    //并提交：
    context.SaveChanges（）;
}
 

  

这可以让你避免初始如果（dbData.Count大于0），因为你总是会插入来自Excel工作表中的任何行不具有一个匹配的条目 dbData ，所以你不需要code为第一次插入一个单独的块。
  它也比当前循环更有效，因为现在你迭代中的每个对象dbData在xlData每个对象;这意味着，如果你在每一个有1000个项目，你有一百万次迭代...

在更新过程和效率

注释一般

（注：我知道这个问题是不是直接有关的效率，但因为你在复制性的背景下提到这个问题，我只是想给一些耐人寻味）

• 除非你正在建设一个具有做此操作多个实体的系统，我会告诫不要通过建立一个基于反射的特性复印机增加更多的复杂性，您的code。


• 如果你认为你必须复制的属性的数量（即 foo.x = bar.x 的号码类型语句），然后再考虑$ C $款要求有一个的强大的，全面的测试和可证明的有效的基于反射的特性复印机（即内置的缓存，这样你就不必不断地重新反思类型属性，一种机制，允许您指定例外，搬运，凡任何不明原因你发现随机列X中的值空是要在某些情况下，等）区别对待稍胜一筹的情况下，你可能会发现，前者实际上是显著少工作：）


• 请记住，即使是最快的基于反射的解决方案将始终还是多好的老式 foo.x = bar.x 分配慢。


• 将一切意味着，如果你有10或20个独立的实体做到这一点的操作，考虑到一般情况，否则我的建议是，手动编写的财产分配副本，得到它的权利，再想想要概括 - 还是看 Automapper ，例如。


• 在已更改只更新场方面 - 我不知道，你甚至需要。如果在数据库中存在的记录，用户只需presented，他们自称是该对象的正确版本，记录的副本，则只需复制他们给了所有的值，并将其保存到数据库中。



• 我之所以说这是因为在所有的可能性，仅在发送，例如4修改的字段与25或什么的效率，根本算不了什么旁边的实际往返数据库本身的开销;我会感到惊讶，如果你能够通过不发送所有列以观察这些类型的操作有意义的性能提升 - 除非当然，所有列 NVARCHAR（MAX）或东西：）




• 如果并发（即其他用途可能会被修改同一数据），则包括发出 ROWVERSION 数据库中的表类型列，中映射它实体框架，并处理并发问题，如果当他们出现。

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
}

解决方案

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.

这篇关于更新仅对不同的变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！