Best Practice: Convert LINQ Query result to a DataTable without looping

Question

What is the best practice to convert LINQ-Query result to a new DataTable?
can I find a solution better than foreach every result item?

EDIT AnonymousType

var rslt = from eisd in empsQuery
           join eng in getAllEmployees()
           on eisd.EMPLOYID.Trim() equals eng.EMPLOYID.Trim()
           select new
           {
               eisd.CompanyID,
               eisd.DIRECTID,
               eisd.EMPLOYID,
               eisd.INACTIVE,
               eisd.LEVEL,
               eng.EnglishName
           };

EDIT 2: I got exception:

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

as I try to execute the query and found the solution here IEnumerable.Except wont work, so what do I do?
and Need linq help

LaGrandMere · Accepted Answer · 2010-12-16T12:45:53.250

35

Use Linq to Dataset. From the MSDN : Creating a DataTable From a Query (LINQ to DataSet)

// Query the SalesOrderHeader table for orders placed 
// after August 8, 2001.
IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
    select order;

// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();

If you have anonymous types :

From the Coder Blog : Using Linq anonymous types and CopyDataTable

It explains how to use MSDN's How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow

edited Dec 16 '10 at 12:45

answered Dec 16 '10 at 12:23

LaGrandMere

10,265
1
33
41

1

what if the query was contains 'select new {fld1, fld2, fld3}' then it cant convert it to IEnumerable? – Rami Alshareef Dec 16 '10 at 12:30
http://ayende.com/Blog/archive/2007/05/09/Converting-an-object-collection-to-a-DataSet.aspx – Hao Dec 16 '10 at 12:45

score 4 · Answer 2 · edited Jul 23 '15 at 11:52

Converting Query result in DataTables Generic Function

 DataTable ddt = new DataTable();

 ddt = LINQResultToDataTable(query);

    public DataTable LINQResultToDataTable<T>(IEnumerable<T> Linqlist)
    {
        DataTable dt = new DataTable();


        PropertyInfo[] columns = null;

        if (Linqlist == null) return dt;

        foreach (T Record in Linqlist)
        {

            if (columns == null)
            {
                columns = ((Type)Record.GetType()).GetProperties();
                foreach (PropertyInfo GetProperty in columns)
                {
                    Type colType = GetProperty.PropertyType;

                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                    == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }

                    dt.Columns.Add(new DataColumn(GetProperty.Name, colType));
                }
            }

            DataRow dr = dt.NewRow();

            foreach (PropertyInfo pinfo in columns)
            {
                dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ? DBNull.Value : pinfo.GetValue
                (Record, null);
            }

            dt.Rows.Add(dr);
        }
        return dt;
    }

Please add some explanation – Mohit Jain Jul 23 '15 at 11:51 — Mohit Jain, Jul 23 '15 at 11:51

score 2 · Answer 3 · answered Jul 27 '17 at 14:12

I am using morelinq.2.2.0 package in asp.net web application, Nuget package manager console

PM> Install-Package morelinq

Namespace

using MoreLinq;

My sample stored procedure sp_Profile() which returns profile details

DataTable dt = context.sp_Profile().ToDataTable();

score 1 · Answer 4 · edited Jul 27 '17 at 19:13

Use System.Reflection and iterate for each record in the query object.

Dim dtResult As New DataTable
Dim t As Type = objRow.GetType
Dim pi As PropertyInfo() = t.GetProperties()

For Each p As PropertyInfo In pi
    dtResult.Columns.Add(p.Name)
Next
Dim newRow = dtResult.NewRow()
For Each p As PropertyInfo In pi
    newRow(p.Name) = p.GetValue(objRow,Nothing)
Next
dtResult.Rows.Add(newRow.ItemArray)
Return dtResult

score -1 · Answer 5 · answered Nov 20 '19 at 07:20

-1

Try Datatable dt= (from rec in dr.AsEnumerable() select rec).CopyToDataTable()

answered Nov 20 '19 at 07:20

maitri pathak

1

Best Practice: Convert LINQ Query result to a DataTable without looping

5 Answers5

Linked

Related