Entity Framework - update non-null values

Question

This is a little new to me. I was asked to write an ETL program that loads two sets of data into the same table. Dataset No. 1 is complete and contains all the data for the table. However, data set No. 2 contains only the changes that need to be applied to the first data set. Note:

// Dataset No. 1: widget 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 | | +----+------+------+------+------+ 

// Expected Result: Widgets with all changes

 +----+------+------+------+------+ | ID | COL1 | COL2 | COL3 | COL4 | +----+------+------+------+------+ | 1 | abcd | efgj | abcd | ijkl | +----+------+------+------+------+ | 2 | mnop | abcd | qrst | abcd | +----+------+------+------+------+ 

The obvious approach (which I am trying to avoid) is to pull each widget from the first table and compare the properties by properties:

 // 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, in this particular data set there are more than 200 fields with a large number of input files that adhere to the same methodology (a complete data set accompanied by a diff data set), but have a completely different scheme. Obviously, I would rather have something portable so that I can just run the files, rather than matching properties by properties for each data set.

Is there a way in which I can iterate over the properties of both objects and update values ​​that are not null?

Answer 1

First you should use something like this to select the objects 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 slightly accelerated by building the property list up, so you only need 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 of these types of entities, you might even think about doing this in a common 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(); } } 

Answer 2

You might want to use reflection for this. Scroll through all the properties / fields for each widget / difference, get the value of this property / field, if the difference is zero, then use the original value.

 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; var properties = typeof(MyWidgetType).GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach(var property in properties) { //widget.column = widget_diff.column ?? widget.colum; property.SetValue(property.GetValue(widget_diff) ?? property.GetValue(widget), widget); } //You can do the same for fields here if the entity has any fields (probably not). } 

Answer 3

@pswg the answer is great, however, when I tried to implement it, I encountered several errors (for example, you cannot check null with obj.Equals (null), null does not have an Equals method).

Here is the “complete copyable solution” from @pswg's excellent answer (as a by-product)

The InjectNonNull static general method gets the source object that you want to update and the final "sparse" object with zeros and transfers only non-zero properties to the target object.

  private static class PropertyLister<T1, T2> { public static readonly IEnumerable<Tuple<PropertyInfo, PropertyInfo>> PropertyMap; static PropertyLister() { 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(); } } public static T InjectNonNull<T>(T dest, T src) { foreach (var propertyPair in PropertyLister<T, T>.PropertyMap) { var fromValue = propertyPair.Item2.GetValue(src, null); if (fromValue != null && propertyPair.Item1.CanWrite) { propertyPair.Item1.SetValue(dest, fromValue, null); } } return dest; }