How to convert Linq result to Datatable (using datatable not context)

Question

How can i convert linq result to data table?

Dim query =  (From e In dsCart.Tables(0).AsEnumerable()
          Group e By
            DistNum = e("DistNum"),
            DistributorName = e("DistributorName"),
            SourceID = e("SourceID")
          Into Group
          Select New With {
            .DistNum = DistNum,
            .DistributorName = DistributorName,
            .TotalOrderAmt = Group.Sum(Function(x) x.Field(Of Decimal)("ExtendedAmt")),
            .ItemQty = Group.Count(Function(x) x.Field(Of Integer)("ItemQty"))
         })

I have come across the following method to convert linq var to data table but i am getting error 'The type arguments for method cannot be inferred from the usage' when calling gv.DataSource = ToDataTable(query)

private Function ToDataTable(Of T)(collection As IEnumerable(Of T)) As DataTable
    Dim dt As New DataTable()
    Dim _t As Type = GetType(T)
    Dim pia As PropertyInfo() = _t.GetProperties()

    'Create the columns in the DataTable
    For Each pi As PropertyInfo In pia
        dt.Columns.Add(pi.Name, If(Nullable.GetUnderlyingType(pi.PropertyType), pi.PropertyType))
    Next

    'Populate the table
    For Each item As T In collection
        Dim dr As DataRow = dt.NewRow()
        dr.BeginEdit()
        For Each pi As PropertyInfo In pia
            dr(pi.Name) = pi.GetValue(item, Nothing)
        Next
        dr.EndEdit()
        dt.Rows.Add(dr)
    Next

    Return dt
End Function

Answer 1

This extension is from Microsoft originally and modified slightly to handle nulls (I hope C# is not a problem for you):

public static class CustomLINQtoDataSetMethods
{
    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
    {
        return new ObjectShredder<T>().Shred(source, (DataTable)null, (LoadOption?)null);
    }

    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        return new ObjectShredder<T>().Shred(source, table, options);
    }
}

public class ObjectShredder<T>
{
    private FieldInfo[] _fi;

    private PropertyInfo[] _pi;

    private Dictionary<string, int> _ordinalMap;

    private Type _type;

    public ObjectShredder()
    {
        _type = typeof(T);
        _fi = _type.GetFields();
        _pi = _type.GetProperties();
        _ordinalMap = new Dictionary<string, int>();
    }

    public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        if (typeof(T).IsPrimitive)
        {
            return ShredPrimitive(source, table, options);
        }
        if (table == null)
        {
            table = new DataTable(typeof(T).Name);
        }
        table = ExtendTable(table, typeof(T));
        table.BeginLoadData();
        using (IEnumerator<T> e = source.GetEnumerator())
        {
            while (e.MoveNext())
            {
                if (options.HasValue)
                {
                    table.LoadDataRow(ShredObject(table, e.Current), options.Value);
                }
                else
                {
                    table.LoadDataRow(ShredObject(table, e.Current), true);
                }
            }
        }
        table.EndLoadData();
        return table;
    }

    public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        if (table == null)
        {
            table = new DataTable(typeof(T).Name);
        }
        if (!table.Columns.Contains("Value"))
        {
            table.Columns.Add("Value", typeof(T));
        }
        table.BeginLoadData();
        using (IEnumerator<T> e = source.GetEnumerator())
        {
            object[] values = new object[table.Columns.Count];
            while (e.MoveNext())
            {
                values[table.Columns["Value"].Ordinal] = e.Current;
                if (options.HasValue)
                {
                    table.LoadDataRow(values, options.Value);
                }
                else
                {
                    table.LoadDataRow(values, true);
                }
            }
        }
        table.EndLoadData();
        return table;
    }

    public object[] ShredObject(DataTable table, T instance)
    {
        FieldInfo[] fi = _fi;
        PropertyInfo[] pi = _pi;
        if (instance.GetType() != typeof(T))
        {
            ExtendTable(table, instance.GetType());
            fi = instance.GetType().GetFields();
            pi = instance.GetType().GetProperties();
        }
        object[] values = new object[table.Columns.Count];
        FieldInfo[] array = fi;
        foreach (FieldInfo f in array)
        {
            values[_ordinalMap[f.Name]] = f.GetValue(instance);
        }
        PropertyInfo[] array2 = pi;
        foreach (PropertyInfo p in array2)
        {
            values[_ordinalMap[p.Name]] = p.GetValue(instance, null);
        }
        return values;
    }

    public DataTable ExtendTable(DataTable table, Type type)
    {
        FieldInfo[] fields = type.GetFields();
        foreach (FieldInfo f in fields)
        {
            if (!_ordinalMap.ContainsKey(f.Name))
            {
                DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name] : table.Columns.Add(f.Name, f.FieldType);
                _ordinalMap.Add(f.Name, dc.Ordinal);
            }
        }
        PropertyInfo[] properties = type.GetProperties();
        foreach (PropertyInfo p in properties)
        {
            if (!_ordinalMap.ContainsKey(p.Name))
            {
                DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name] : table.Columns.Add(p.Name, p.PropertyType);
                _ordinalMap.Add(p.Name, dc.Ordinal);
            }
        }
        return table;
    }
}

It doesn't look short and simple but it works. Sample usage:

DataTable usaCustomers =
 northwindContext.Customers
    .Where(c => c.Country == "USA")
    .CopyToDataTable();

Answer 2

I wrote a method similar to the one you found but it has a couple of improvements. First, it's an extension method so it can be called like an instance method. That's a small thing but, for many, that feels more natural. Secondly, and more importantly, it handles NULLs.

Imports System.Reflection
Imports System.Runtime.CompilerServices

''' <summary>
''' Contains methods that extend the <see cref="IEnumerable(Of T)"/> interface.
''' </summary>
Public Module EnumerableExtensions

    ''' <summary>
    ''' Returns a <see cref="DataTable"/> containing the data from the source list.
    ''' </summary>
    ''' <typeparam name="T">
    ''' The type of the list items.
    ''' </typeparam>
    ''' <param name="source">
    ''' The source list.
    ''' </param>
    ''' <returns>
    ''' A <see cref="DataTable"/> with a column for each public property in the item type and a row for each item.
    ''' </returns>
    <Extension>
    Public Function ToDataTable(Of T)(source As IEnumerable(Of T)) As DataTable
        Dim sourceType = GetType(T)
        Dim properties = sourceType.GetProperties()
        Dim table As New DataTable

        'Add a column for each public instance property of the items.
        For Each prop In properties
            table.Columns.Add(prop.Name,
                              If(Nullable.GetUnderlyingType(prop.PropertyType),
                                 prop.PropertyType))
        Next

        'Add a row for each item.
        For Each item In source
            Dim row = table.NewRow()

            row.BeginEdit()

            For Each prop In properties
                row(prop.Name) = If(prop.GetValue(item), DBNull.Value)
            Next

            row.EndEdit()

            table.Rows.Add(row)
        Next

        Return table
    End Function

End Module