Entity Framework - Only update values that are not null

Question

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?

Answer 1

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 toProp in typepf(Widget).GetProperties())
{
    var fromProp= typeof(Widget_Change).GetProperty(toProp.Name);
    var toValue = fromProp.GetValue(widget_diff, null);
    if (toValue != null)
    {
        toProp.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();
    }
}

Answer 2

You might want to use reflection for this. Loop through all of the properties/fields for each widget/difference, get the value of that property/field, if the difference is null, 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

@p.s.w.g's answer is great, however when I tried to implement it I encountered several bugs (such as for example you can't check for null with obj.Equals(null), null doesn't have Equals method).

Here is a "complete copy-pastable solution" of @p.s.w.g's great answer (as a by-product)

A static generic method InjectNonNull gets the source entity you want to update and the destination "sparce" entity with nulls, and transfers only the non-null properties on destination entity.

    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;
    }