Datatable from dynamic (non-generic) linq result

Question

I use linq to make a query on an EntityFramework model with multiple .Include()d tables and I want to produce an Excel spreadsheet (using EPPlus) from the query result.

Query:

var statements = Entities.MainStatements.Include(s => s.RevenueStatements)
  .Include(s => s.ExpensesStatements).Include(Branches)
  .Where(b => branchlist.Contains(b.Branches.BranchName));

There are more, but this gives the idea.

This query gives me an IQueryable<MainStatements> or IEnumerable<MainStatements> if I make a ToList() out of it.  I also have an array of fields that the user wants to report on, but if I select these in the query above, the result is a DynamicClass1.

    var statements = ...
      .Select { fieldArray };

I can only make a spreadsheet from a Collection<T> or a DataTable.

I have looked at CopyToDataTable but all overloads require an IEnumerable<T>.  So I get all the fields if I don't use the Select and it's not an IEnumerable<T> if I do.  If I try to create it manually using PropertyInfo, I hit obstacles when trying to find the fields of the Included tables.

How can I make a DataTable from a dynamic Linq result?  Or, how can I (using reflection or otherwise) iterate through a linq query result, including iterating through Included table data to extract values to populate a DataTable?

Answer 1

Hi nob0dy,

>>How can I make a DataTable from a dynamic Linq result?  Or, how can I (using reflection or otherwise) iterate through a linq query result, including iterating through Included table data to extract values to populate a DataTable?

Please try the following extension method.

public static DataTable ToDataTable<T>(this IEnumerable<T> items)
{
    var tb = new DataTable(typeof(T).Name);

    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

    foreach(var prop in props)
    {
        tb.Columns.Add(prop.Name, prop.PropertyType);
    }

     foreach (var item in items)
    {
       var values = new object[props.Length];
        for (var i=0; i<props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }

        tb.Rows.Add(values);
    }

    return tb;
}

#Usage;

IEnumerable<MainStatements> ms = //"theresultfromquery";
ms.ToDataTable<MainStatements>();

Best regards,

Zhanglong

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].