LINQ to map a datatable into a list<MyObject>

bAN picture bAN · Nov 16, 2010 · Viewed 12.2k times · Source

I just discover LINQ so be comprehensive with me please! :-)

So! I have a Data-tier who provide me datatables and i want to convert them into lists of objects. These objects are defined in a spécific layer DTO (Data transfer Objects).

How can I map every rows of my datatable into objects and put the all objects into a list? (today i make it "manually" field after field) Is it possible with LINQ? I've heard about LINQ2Entities? am i right?

Thanks to help a beginner to understand...

Answer

Tomas Jansson picture Tomas Jansson · Nov 16, 2010

If the objects is not too complex you can use this:

public static class DataTableExtensions
{
   public static IList<T> ToList<T>(this DataTable table) where T : new()
   {
      IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
      IList<T> result = new List<T>();

      foreach (var row in table.Rows)
      {
         var item = CreateItemFromRow<T>((DataRow)row, properties);
         result.Add(item);
      }

      return result;
   }

   private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
   {
       T item = new T();
       foreach (var property in properties)
       {
           property.SetValue(item, row[property.Name], null);
       }
       return item;
   }
}

With that in place you can now write: var list = YourDataTable.ToList<YourEntityType>().

You can read about it here: http://blog.tomasjansson.com/convert-datatable-to-generic-list-extension/

And it is an answer to a previous question: Convert DataTable to Generic List in C#

EDIT: I should add that this is not linq, but some extension methods to DataTable I wrote. Also, it is working with the convention that the properties in the object you're mapping with has the same name as in the DataTable. Of course this could be extended to read attributes on the properties or the method itself could take a simple Dictionary<string,string> that could be used to do the mapping. You could also extend it with some functionality that take a params string[] excludeProperties that could be used to exclude some of the properties.