How can I convert a Datable to a List when class properties match table columns without using a for loop?

Question

What's the fastest way to convert a DataTable with matching class properties to a List?

Why "without using a for loop"? What if that's the fastest way? Why are you manually converting a DataTable to a List? is it always a List, or do you know some concrete type already? Stack Overflow isn't a coding service - we're not here to compete at writing the fastest code to do particular tasks. Instead, you should make your own attempt to do what you want. If you try it and it doesn't work, then you could share a [mcve] with us and clearly explain what you're doing and we can help fix it. — mason, Feb 21 '23 at 19:48
Does [this](https://stackoverflow.com/a/29625894/5509738) work for you — Karen Payne, Feb 21 '23 at 21:54

score 0 · Answer 1 · answered Feb 21 '23 at 22:15

The fastest way is to write explicit code to create new objects of the class and assign the properties from the corresponding columns directly, that is specific to the class and DataTable and returning a List<T>.

A fairly slow way is to use Reflection to gather the public properties of the class and assign the corresponding column values to new objects created in that way. However, this is very generic in that it will work with any type without any specialization required.

A combination of the two could probably be created using the C# Source Generator feature, but I don't happen to have that written or lying around. This could allow you to have a somewhat faster method written for you at compile time for any class. It would still have to use runtime lookups for the DataTable since it can't (necessarily) know the columns at compile time.

Here is the generic method:

public static class DataTableExt {
    public static Action<object, object> SetValueFunc(this MemberInfo member) {
        switch (member) {
            case FieldInfo mfi:
                return mfi.SetValue;
            case PropertyInfo mpi:
                return mpi.SetValue;
            case MethodInfo mi:
                return (destObject, value) => mi.Invoke(destObject, new object[] { value });
            default:
                throw new ArgumentException("MemberInfo must be of type FieldInfo, PropertyInfo or MethodInfo", nameof(member));
        }
    }
    public static void SetValue(this MemberInfo member, object destObject, object value) => member.SetValueFunc()(destObject, value);

    public static bool GetCanWrite(this MemberInfo member) {
        switch (member) {
            case FieldInfo mfi:
                return true;
            case PropertyInfo mpi:
                return mpi.CanWrite;
            default:
                throw new ArgumentException("MemberInfo must be if type FieldInfo or PropertyInfo", nameof(member));
        }
    }

    public static List<MemberInfo> GetPropertiesOrFields(this Type t, BindingFlags bf = BindingFlags.Public | BindingFlags.Instance) =>
        t.GetMembers(bf).Where(mi => mi.MemberType == MemberTypes.Field || mi.MemberType == MemberTypes.Property).ToList();

    public static IEnumerable<T> ToIEnumerable<T>(this DataTable dt) where T : new() {
        var props = typeof(T).GetPropertiesOrFields()
                             .Where(mi => dt.Columns.Contains(mi.Name) && mi.GetCanWrite())
                             .ToList();
        foreach (var row in dt.AsEnumerable()) {
            var aT = new T();
            foreach (var p in props)
                p.SetValue(aT, row[p.Name]);
            yield return aT;
        }
    }
}

Which you would use like

var ans = dt.ToIEnumerable<MyClass>().ToList();

How can I convert a Datable to a List when class properties match table columns without using a for loop?

1 Answers1