实体框架 - 仅更新非空值 [英] Entity Framework - Only update values that are not null

问题描述

这对我来说有点新意。我被要求编写一个将两个数据集加载到同一个表中的ETL程序。数据集＃1已完整，并包含表的所有数据。但是，数据集＃2仅包含需要覆盖到第一个数据集上的更改。观察：

//数据集＃1：小部件表

  ---- + ------ + ------ + ------ + ------ + 
 | ID | COL1 | COL2 | COL3 | COL4 | 
 + ---- + ------ + ------ + ------ + ------ + 
 | 1 | abcd | abcd | abcd | abcd | 
 + ---- + ------ + ------ + ------ + ------ + 
 | 2 | abcd | abcd | abcd | abcd | 
 + ---- + ------ + ------ + ------ + ------ + 
  / pre> 
 
 
 //数据集＃2：Widgets_Changes表
  ---- + ------ + ------ + ------ + ------ + 
 | ID | COL1 | COL2 | COL3 | COL4 | 
 + ---- + ------ + ------ + ------ + ------ + 
 | 1 | | efgh | | ijkl | 
 + ---- + ------ + ------ + ------ + ------ + 
 | 2 | mnop | | qrst | | 
 + ---- + ------ + ------ + ------ + ------ + 
  / pre> 
 
 
 //预期结果：具有所有更改的小部件
  + ---- + ------ + ------ + ------ + ------ + 
 | ID | COL1 | COL2 | COL3 | COL4 | 
 + ---- + ------ + ------ + ------ + ------ + 
 | 1 | abcd | efgj | abcd | ijkl | 
 + ---- + ------ + ------ + ------ + ------ + 
 | 2 | mnop | abcd | qrst | abcd | 
 + ---- + ------ + ------ + ------ + ------ + 
  / pre> 
 
 
明显的方法（我想避免的）是将每个小部件从第一个表中拉出，并进行逐个属性比较： / p> 
 
 
  //简化的例子：
 using（var db = new MyEntityDatabase（））{
 
 var widget = from p in db.Widgets select p where p.ID == 1; 
 var widget_diff = from p in db.Widgets_Changes select p where p.ID == 1 
 
 widget.COL1 = widget_diff.COL1 ?? widget.COL1; 
 widget.COL2 = widget_diff.COL2 ?? widget.COL2; 
 widget.COL3 = widget_diff.COL3 ?? widget.COL3; 
 // ... etc 
 
 db.saveChanges（）; 
} 
  
但是，在这个特定数据集中有超过200个字段，更多的文件传入遵守相同的方法（完整的数据集伴随diff数据集），但完全不同的模式。显然，我宁愿有一些可移植的东西，我只需要运行文件，而不必对每个数据集进行硬编码的财产比较。
 
 
 
是有一种方法可以遍历两个对象的属性和更新不为null的值？
解决方案
首先，你需要使用这样的东西来选择要更新的实体：
  var widget = db.Widgets.First（p = > p.ID == 1）; 
 var widget_diff = db.Widgets_Changes.First（p => p.ID == 1）; 
  
现在您可以使用反射来更新所有字段：
 
 
 pre $  foreach（var fromProp in typepf（Widget）.GetProperties（））
 {
 var toProp = typeof（Widget_Change）.GetProperty（fromProp.Name ）; 
 var toValue = toProp.GetValue（widget_diff，null）; 
 if（toValue！= null）
 {
 fromProp.SetValue（widget，toValue，null）; 
} 
} 
  
通过构建列表可以加快一点的属性在前面，所以你只需要使用反射一次：
  public static class WidgetUtil 
 {
 public static readonly IEnumerable< Tuple< PropertyInfo，PropertyInfo>>属性映射; 
 
 static Util（）
 {
 var b = BindingFlags.Public | BindingFlags.Instance; 
 PropertyMap = 
（from f in typeof（Widget）.GetProperties（b）
 join t in typeof（WidgetChange）.GetProperties（b）on f.Name equals t.Name 
选择Tuple.Create（f，t））
 .ToArray（）; 
} 
} 
 
 ... 
 
 foreach（WidgetUtil.PropertyMap中的var propertyPair）
 {
 var toValue = propertyPair.Item2.GetValue（widget_diff，null）; 
 if（toValue！= null）
 {
 propertyPair.Item1.SetValue（widget，toValue，null）; 
} 
} 
  
如果你有很多这样的实体类型，甚至可能想要考虑将其变成一个通用的实用程序：
  public static class WidgetUtil< T1，T2> 
 {
 public static readonly IEnumerable< Tuple< PropertyInfo，PropertyInfo>>属性映射; 
 
 static WidgetUtil（）
 {
 var b = BindingFlags.Public | BindingFlags.Instance; 
 PropertyMap = 
（从typeof（T1）中的f .GetProperties（b）
在t（）中加入t。f.Name上的GetProperties（b）等于t.Name 
选择Tuple.Create（f，t））
 .ToArray（）; 
} 
} 
  
 
This is a bit of a new one for me.  I've been asked to write an ETL program that loads two datasets into the same table.  Dataset #1 is complete and contains all the data for the table.  Dataset#2, however, only contains changes that need to be overlaid onto the first dataset.  Observe:

// Dataset #1: Widgets Table
+----+------+------+------+------+
| ID | COL1 | COL2 | COL3 | COL4 |
+----+------+------+------+------+
| 1  | abcd | abcd | abcd | abcd |
+----+------+------+------+------+
| 2  | abcd | abcd | abcd | abcd |
+----+------+------+------+------+
// Dataset #2: Widgets_Changes Table
+----+------+------+------+------+
| ID | COL1 | COL2 | COL3 | COL4 |
+----+------+------+------+------+
| 1  |      | efgh |      | ijkl |
+----+------+------+------+------+
| 2  | mnop |      | qrst |      |
+----+------+------+------+------+
// The Anticipated Result: Widgets with all Changes
+----+------+------+------+------+
| ID | COL1 | COL2 | COL3 | COL4 |
+----+------+------+------+------+
| 1  | abcd | efgj | abcd | ijkl |
+----+------+------+------+------+
| 2  | mnop | abcd | qrst | abcd |
+----+------+------+------+------+
The obvious approach (that I'm trying to avoid) is to pull each widget out of the first table and do a property-by-property comparison:
// Simplified example:
using ( var db = new MyEntityDatabase() ){

   var widget      = from p in db.Widgets select p where p.ID == 1;
   var widget_diff = from p in db.Widgets_Changes select p where p.ID == 1

   widget.COL1 = widget_diff.COL1 ?? widget.COL1;
   widget.COL2 = widget_diff.COL2 ?? widget.COL2;
   widget.COL3 = widget_diff.COL3 ?? widget.COL3;
   // ...etc

   db.saveChanges();
}
However, there are over 200 fields in this particular dataset, with more files incoming that adhere to this same methodology (complete dataset accompanied by diff dataset) but have a different schema entirely.  Obviously, I'd rather have something portable that I can just run the files through instead of having to hard-code property-by-property comparisons for each dataset.

Is there a way I can iterate through the properties of both objects and update values that aren't null?
解决方案
First off, you need to use something like this to select the entities you want to update:
var widget      = db.Widgets.First(p => p.ID == 1);
var widget_diff = db.Widgets_Changes.First(p => p.ID == 1);
Now you can simply use reflection to update all fields:
foreach(var fromProp in typepf(Widget).GetProperties())
{
    var toProp = typeof(Widget_Change).GetProperty(fromProp.Name);
    var toValue = toProp.GetValue(widget_diff, null);
    if (toValue != null)
    {
        fromProp.SetValue(widget, toValue, null);
    }
}
This can be sped up a bit by building a list the of properties up front, so you only have to use reflection once:
public static class WidgetUtil
{
    public static readonly IEnumerable<Tuple<PropertyInfo, PropertyInfo>> PropertyMap;

    static Util()
    {
        var b = BindingFlags.Public | BindingFlags.Instance;
        PropertyMap = 
            (from f in typeof(Widget).GetProperties(b)
             join t in typeof(WidgetChange).GetProperties(b) on f.Name equals t.Name
             select Tuple.Create(f, t))
            .ToArray();
    }
}

...

foreach(var propertyPair in WidgetUtil.PropertyMap)
{
    var toValue = propertyPair.Item2.GetValue(widget_diff, null);
    if (toValue != null)
    {
        propertyPair.Item1.SetValue(widget, toValue, null);
    }
}
If you have many such entity types, you might even want to consider making this into a generic utility:
public static class WidgetUtil<T1, T2>
{
    public static readonly IEnumerable<Tuple<PropertyInfo, PropertyInfo>> PropertyMap;

    static WidgetUtil()
    {
        var b = BindingFlags.Public | BindingFlags.Instance;
        PropertyMap = 
            (from f in typeof(T1).GetProperties(b)
             join t in typeof(T2).GetProperties(b) on f.Name equals t.Name
             select Tuple.Create(f, t))
            .ToArray();
    }
}


                        
这篇关于实体框架 - 仅更新非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！