Sebi Sebi - 1 month ago 16
C# Question

C# using ExpressionTree to map DataTable to List<T>

I have written a

ToList();
extension Method to convert a DataTable to List. This just works under some circumstances but we have much old code which uses DataTables and sometimes it's needed. My Problem is that this method works with reflection what is ok but not that performant. I need about 1,2sek for 100.000 DataRows.

So i decided to build this with Expression Trees. At first i want to replace the Setter Call of Properties. Up to this time i could easily get the value:

var exactType = Nullable.GetUnderlyingType(propType) ?? propType;
var wert = Convert.ChangeType(zeile[spaltenname], exactType);


and set it:

propertyInfo.SetValue(tempObjekt, wert, null);


Now i searched StackOverflow and found this:

var zielExp = Expression.Parameter(typeof(T));
var wertExp = Expression.Parameter(propType);

var propertyExp = Expression.Property(zielExp, matchProp);
var zuweisungExp = Expression.Assign(propertyExp, wertExp);

var setter = Expression.Lambda<Action<T, int>>(zuweisungExp, zielExp, wertExp).Compile();
setter(tempObjekt, wert);


My big Problem is that the Lambda Action expects an integer. But i need this expecting the type of my Property. I have the Type of my Property via PropertyInfo. But can't get this to work. Thought i can easily make:

Action<T, object>


but this results in following excepion:


ArgumentException The ParameterExpression from Type "System.Int32"
cannot be used as Delegateparameter from Type "System.Object".


Someone out there knows a possible solution?

Answer

Instead of the generic Expression.Lambda method you can use this overload which takes a type:

public static LambdaExpression Lambda(
   Type delegateType,
   Expression body,
   params ParameterExpression[] parameters
)

Then you can use the Type.MakeGenericType method to create the type for your action:

var actionType = typeof(Action<,>).MakeGenericType(typeof(T), proptype);
var setter = Expression.Lambda(actionType, zuweisungExp, zielExp, wertExp).Compile();

Edit following the comments regarding performance:

You can also just build the expression runtime to map the DataTable to your class of type T with a select, so there's only need to use reflection once, which should greatly improve performance. I wrote the following extension method to convert a DataTable to List<T> (note that this method will throw a runtime exception if you don't plan to map all datacolumns to a property in the class, so be sure to take care of that if that might happen):

public static class LocalExtensions
{
    public static List<T> DataTableToList<T>(this DataTable table) where T : class
    {
        //Map the properties in a dictionary by name for easy access
        var propertiesByName = typeof(T)
            .GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .ToDictionary(p => p.Name);
        var columnNames = table.Columns.Cast<DataColumn>().Select(dc => dc.ColumnName);

        //The indexer property to access DataRow["columnName"] is called "Item"
        var property = typeof(DataRow).GetProperties().First(p => p.Name == "Item" 
            && p.GetIndexParameters().Length == 1 
            && p.GetIndexParameters()[0].ParameterType == typeof(string));

        var paramExpr = Expression.Parameter(typeof(DataRow), "r");
        var newExpr = Expression.New(typeof(T));

        //Create the expressions to map properties from your class to the corresponding
        //value in the datarow. This will throw a runtime exception if your class 
        //doesn't contain properties for all columnnames!
        var memberBindings = columnNames.Select(columnName =>
        {
            var pi = propertiesByName[columnName];
            var indexExpr = Expression.MakeIndex(paramExpr, property, 
                new[] { Expression.Constant(columnName) });
            //Datarow["columnName"] is of type object, cast to the right type
            var convert = Expression.Convert(indexExpr, pi.PropertyType);

            return Expression.Bind(pi, convert);
        });
        var initExpr = Expression.MemberInit(newExpr, memberBindings);
        var func = Expression.Lambda<Func<DataRow, T>>(initExpr,paramExpr).Compile();

        return table.Rows.Cast<DataRow>().Select(func).ToList();
    }
}

Then I wrote a small testclass and some code which creates a datatable of 1,000,000 rows that get mapped to a list. Building the expression + converting to a list now only takes 486ms on my pc (granted it is a very small class of course):

class Test
{
    public string TestString { get; set; }
    public int TestInt { get; set; }
}

class Program
{
    static void Main()
    {
        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("TestString", typeof(string)));
        table.Columns.Add(new DataColumn("TestInt", typeof(int)));

        for(int i = 0; i < 1000000; i++)
        {
            var row = table.NewRow();
            row["TestString"] = $"String number: {i}";
            row["TestInt"] = i;
            table.Rows.Add(row);
        }

        var stopwatch = Stopwatch.StartNew();

        var myList = table.DataTableToList<Test>();

        stopwatch.Stop();
        Console.WriteLine(stopwatch.Elapsed.ToString());
    }
}