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-16 12:45:53Z

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

what if the query was contains 'select new {fld1, fld2, fld3}' then it cant convert it to IEnumerable?

Mohit Jain · Accepted Answer · 2015-07-23 11:52:10Z

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; }

Pranesh Janarthanan · Accepted Answer · 2017-07-27 14:12:04Z

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();

MPhil · Accepted Answer · 2017-07-27 19:13:17Z

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

maitri pathak · Accepted Answer · 2019-11-20 07:20:39Z

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

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Linked

Related