Monday, January 28, 2008 11:54 AM monkey9987

LINQ to DataTable

We have been adopting Linq slowly but surely and have run into some issues we never had to deal with in the past.  Currently I use DataSets in VS2005 for everything.  These are nice since they return DataTables easily and allow updates / inserts / etc without issue. 

When we dove into Linq and had to return a DataTable for the first time we were stuck.  Looking through examples & searching online did not return much. I did find an example by Kevin Goff in CoDe Magazine which outlined 13 tips for Linq.  This worked great except I had some issues with null datatypes. Those issues should be fixed in the sample below.

The following code outlines a quick way using Linq to query a database and return the results to a DataTable.

Here is my newly created class:

static public class ConvertDataTable

{

public static DataTable ToADOTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)

{

DataTable dtReturn = new DataTable();

// Could add a check to verify that there is an element 0

T TopRec = varlist.ElementAt(0);

// Use reflection to get property names, to create table

// column names

PropertyInfo[] oProps =

((Type)TopRec.GetType()).GetProperties();

foreach (PropertyInfo pi in oProps)

{

Type colType = pi.PropertyType;if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))

{

colType = colType.GetGenericArguments()[0];

}

dtReturn.Columns.Add(
new DataColumn(pi.Name, colType));

}

foreach (T rec in varlist)

{

DataRow dr = dtReturn.NewRow();foreach (PropertyInfo pi in oProps)
{
   dr[pi.Name] = pi.GetValue(rec,
null) == null ? DBNull.Value : pi.GetValue(rec, null);
}

dtReturn.Rows.Add(dr);

}

return (dtReturn);

}

public delegate object[] CreateRowDelegate<T>(T t);

}

Here is the usage in code:

LData.MyDataContext db = new LData.MyDataContext();
var query = from p in db.Extensions
w
here p.DepartmentNumber == "412"select p;

DataTable dt = query.ToADOTable(rec => new object[] { query });

 

Hope this helps someone else out.

~ Steve

 

Filed under: , ,

Comments

# re: LINQ to DataTable

Thursday, February 28, 2008 8:13 AM by Thomas

Thanks Steve.  

I was looking for something clean that does just this.  

# re: LINQ to DataTable

Tuesday, April 22, 2008 11:55 AM by Mark Kamoski

Can you give a brief sample of how this is called?

# re: LINQ to DataTable

Tuesday, April 29, 2008 9:47 AM by monkey9987

Mark,

There's an example listed above on how to use it:

1. Create your data context

LData.MyDataContext db = new LData.MyDataContext();

2. Generate your query

var query = from p in db.Extensions

where p.DepartmentNumber == "412"select p;

3. Convert the result to a datatable

DataTable dt = query.ToADOTable(rec => new object[] { query });

Let me know if that works for you or not.

~ Steve

# re: LINQ to DataTable

Tuesday, May 6, 2008 11:18 AM by James

What if my datacontext is a datatable instead of a database? I have to append the asEnumerable()  method  to the datatable in the linq statement so that it doesn't throw an error on build.

The results contain rowerrors and fail.

# re: LINQ to DataTable

Wednesday, June 18, 2008 1:40 PM by JoshRoss

Module ConvertDataTable

<System.Runtime.CompilerServices.Extension()> _

Public Function ToDataTable(Of T)(ByVal value As IEnumerable(Of T)) As DataTable

 Dim returnTable As New DataTable

 Dim firstRecord = value.First

 For Each pi In firstRecord.GetType.GetProperties

  returnTable.Columns.Add(pi.Name, pi.GetValue(firstRecord, Nothing).GetType)

 Next

 For Each result In value

  Dim nr = returnTable.NewRow

  For Each pi In result.GetType.GetProperties

   nr(pi.Name) = pi.GetValue(result, Nothing)

  Next

  returnTable.Rows.Add(nr)

 Next

 Return returnTable

End Function

End Module

# re: LINQ to DataTable

Friday, July 11, 2008 2:11 PM by Mark Kamoski

I am getting a CTE of...

Error 1 The non-generic type 'System.Collections.IEnumerable' cannot be used with type arguments C:\Code\Team\Clear\Web\App_Code\Business\Components\ConvertDataTable.cs 20 45 C:\...\Web\

...when I try to compile...

...my using statements are...

using System;

using System.Data;

using System.Collections;

using System.Configuration;

using System.Linq;

using System.Linq.Expressions;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml;

using System.Xml.Linq;

...and the class is exactly as you pasted it above.

Ideas?

Thank you.

-- Mark Kamoski

# re: LINQ to DataTable

Friday, July 11, 2008 4:00 PM by Mark Kamoski

These using statements work...

using System;

using System.Data;

using System.Collections;

using System.Collections.Generic;

using System.Collections.ObjectModel;

using System.Collections.Specialized;

using System.Configuration;

using System.Linq;

using System.Reflection;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

# re: LINQ to DataTable

Tuesday, July 29, 2008 5:49 PM by caseywills

awesome man. saved me a ton of work!

# re: LINQ to DataTable

Wednesday, September 17, 2008 8:17 PM by Adam

You Rock!  Code works great, and saved me a lot of heart ache!

# re: LINQ to DataTable

Tuesday, September 30, 2008 7:17 AM by Holf

This is just fantastic. For ASP.NET stuff when you want to bind to tabular information it is just perfect.

Thanks!

# re: LINQ to DataTable

Monday, November 3, 2008 11:14 PM by Hang Ta

It's help me resolved my problem.

thanks

# re: LINQ to DataTable

Wednesday, July 8, 2009 3:32 PM by Federico Colombo

What about the properties order?

GetProperties() doesn´t return the properties in a native or alphabetical order.......

So... The column order will not be *always* the same for the exactly same query.....

# re: LINQ to DataTable

Thursday, July 30, 2009 12:40 PM by joe

Thanks that was almost too easy.

# re: LINQ to DataTable

Tuesday, August 4, 2009 11:38 AM by Ed de la Rey

Thanks Very Much,

Still need to play with it, but on first galnce it has solved my problem. Grateful for people sharing code like this, it saves days of hassle.

# re: LINQ to DataTable

Thursday, August 6, 2009 3:51 AM by Daniel Valcarce

Thanks for this code!!

# re: LINQ to DataTable

Wednesday, September 2, 2009 11:14 AM by Thuydhv

Thanks steve

This code help to resolveds

# re: LINQ to DataTable

Tuesday, November 3, 2009 10:57 AM by EduardoAndres

Hey, thanks for this code. The bad thing is that I'm getting an error that I just cannot solve.

CS0121: The call is ambiguous between the following methods or properties: 'CustomerPriceAgreement.ConvertDataTable.ToADOTable<AnonymousType#1>(System.Collections.Generic.IEnumerable<AnonymousType#1>, CustomerPriceAgreement.ConvertDataTable.CreateRowDelegate<AnonymousType#1>)' and 'CustomerPriceAgreement.ConvertDataTable.ToADOTable<AnonymousType#1>(System.Collections.Generic.IEnumerable<AnonymousType#1>, CustomerPriceAgreement.ConvertDataTable.CreateRowDelegate<AnonymousType#1>)'

Any ideas?

# re: LINQ to DataTable

Saturday, March 13, 2010 2:08 AM by jignesh Patel

HEy Thankfor the code , I got an error as below

f:\CMS\CMS\CMS\App_Code\clsUser.cs(280,22): error CS0121: The call is ambiguous between the following methods or properties: 'CMS.clsConvertDataTable.ToADOTable<AnonymousType#1>(System.Collections.Generic.IEnumerable<AnonymousType#1>, CMS.clsConvertDataTable.CreateRowDelegate<AnonymousType#1>)' and 'CMS.clsConvertDataTable.ToADOTable<AnonymousType#1>(System.Collections.Generic.IEnumerable<AnonymousType#1>, CMS.clsConvertDataTable.CreateRowDelegate<AnonymousType#1>)'

f:\CMS\CMS\CMS\App_Code\clsUser.cs(322,26): error CS0121: The call is ambiguous between the following methods or properties: 'CMS.clsConvertDataTable.ToADOTable<AnonymousType#1>(System.Collections.Generic.IEnumerable<AnonymousType#1>, CMS.clsConvertDataTable.CreateRowDelegate<AnonymousType#1>)' and 'CMS.clsConvertDataTable.ToADOTable<AnonymousType#1>(System.Collections.Generic.IEnumerable<AnonymousType#1>, CMS.clsConvertDataTable.CreateRowDelegate<AnonymousType#1>)'

# re: LINQ to DataTable

Tuesday, May 25, 2010 11:44 AM by Sean McGuiggan

Just wanted to say thanyou very much.  I was going a little insane until I found your article.  Works like a dream.

# re: LINQ to DataTable

Tuesday, June 22, 2010 6:28 AM by Nripin

Great Code Mate !!!!!

# re: LINQ to DataTable

Tuesday, September 21, 2010 1:24 PM by Shaul B

It seems that the fn parameter (and the associated delegate) are basically dead code...?

Otherwise - thanks a ton!  You helped me out a lot!

# re: LINQ to DataTable

Wednesday, September 22, 2010 4:00 AM by Shaul B

It seems simple enough to do this conversion without relying on there being any rows in the anonymous collection.  Based on this on my previous comment, I respectfully submit the following method to amend the original:

public static DataTable ToADOTable<T>(this IEnumerable<T> varlist)

{

DataTable dtReturn = new DataTable();

// Use reflection to get property names, to create table

// column names

PropertyInfo[] oProps = typeof(T).GetProperties();

foreach (PropertyInfo pi in oProps)

{

Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))

{

colType = colType.GetGenericArguments()[0];

}

dtReturn.Columns.Add(new DataColumn(pi.Name, colType));

}

foreach (T rec in varlist)

{

DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)

{

dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);

}

dtReturn.Rows.Add(dr);

}

return (dtReturn);

}

# re: LINQ to DataTable

Wednesday, February 9, 2011 3:07 PM by Kunal

Great post. I just used the last post to create my DataTable and it worked straight out of the box. GREAT code! Although I was slightly confused by the generic bit in the first foreach statement.

# re: LINQ to DataTable

Friday, January 20, 2012 8:46 AM by Humayun

ToADOTable is great solution :).  this is my first post in fact feedback on the Internet and ToADOTable  is reason behind this :).. Thank you

# re: LINQ to DataTable

Wednesday, March 14, 2012 2:22 AM by Chintan

Thanks it helps me a lot

Leave a Comment

(required) 
(required) 
(optional)
(required)