CODEWITHSUNDEEP
vb.netlinq
GDutton
GDutton

Reputation: 176

Convert a Linq result to a datatable

I am trying to convert a Linq result in to a datatable

I have a linq that is created from a dataset of many tables. It returns results, but I need to get the results in to a new datatable.

Examples I have seen say I sould be able to use .CopyToDataTable But for some reason this doesn't work?

I have noticed that I can to .ToArray perhaps I can then turn the array in to a datatable? Seems line an unnecessary step?

Here is my query: (it works)

 Dim R2 = From Inq In DS.Tables!CNLocalInquiry.AsEnumerable()
             Join Cust In DS.Tables!CustomerID.AsEnumerable() On Inq.Field(Of Integer)("CNLocalInquiry_Id") Equals Cust.Field(Of Integer)("CNLocalInquiry_Id")
                Select New With {.date = Inq.Field(Of String)("date"),
                                .CName = Cust.Field(Of String)("CustomerNumber"),
                                .Name = Cust.Field(Of String)("name")}

Dim MemberInq as new datatable 
MemberInq = R2.CopyToDataTable() <-- this doesn't work

This is what my query returns: query returns

Upvotes: 0

Views: 2039

Answers (2)

John Bustos
John Bustos

Reputation: 19544

I've saved this as an extension method and it's always worked perfectly: https://msdn.microsoft.com/en-us/library/bb669096.aspx

Examples here: https://msdn.microsoft.com/en-us/library/bb386921.aspx

Hope that does the trick!

Upvotes: 1

Matthew Whited
Matthew Whited

Reputation: 22433

(this is the easy to code way... this will not be performant for large datasets)

public static class ToolsEx
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> items)
    {
        var t = typeof(T);
        var dt = new DataTable(t.Name);
        var props = t.GetProperties()
                     .Select(p => new { N = p.Name, Getter = p.GetGetMethod() })
                     .Where(p => p.Getter != null)
                     .ToList();
        props.ForEach(p => dt.Columns.Add(p.N));

        foreach (var item in items)
            dt.Rows.Add(props.Select(p => p.Getter.Invoke(item, null)).ToArray());

        return dt;
    }
}

Upvotes: 1

Related Questions