Need to copy LINQ query to datatable (CopyToDataTable not working)

I need to copy a LINQ query to a datatable, and was hoping to do this simply using CopyToDataTable. FYI this is running on ASP.NET 4.0 framework.

From what I've gathered, the LINQ query needs to be "Enumerable." I've tried a couple of variations on code samples I've found, but nothing works. Here are the 2 different code samples:

Dim dc As New UserDataContext
Dim q As IEnumerable(Of DataRow) = From iterVar In dc.UserDetails _
  Select iterVar.ID, iterVar.FirstN, iterVar.LastN
Dim dtTest As DataTable = q.CopyToDataTable()

Open in new window


Running this causes the error:
Unable to cast object of type 'System.Data.Linq.DataQuery`1[VB$AnonymousType_0`3[System.Int32,System.String,System.String]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

... and ...
Dim dc As New UserDataContext  
Dim q = From iterVar In dc.UserDetails.AsEnumerable _
  Select iterVar.ID, iterVar.FirstN, iterVar.LastN
Dim dtTest As DataTable = q.CopyToDataTable()

Open in new window


Running this causes the error: Compiler Error Message: BC30456: 'CopyToDataTable' is not a member of 'System.Collections.Generic.IEnumerable(Of <anonymous type>)'.

Any suggestions? I was excited to see that LINQ was providing an easy way to export its results into a datatable, but it doesn't seem to be straightforward after all.
cdakzAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi cdakz;

There is no way to create DataTable or DataRow's directly in a Linq to SQL query. The CopyToDataTable method works with two data tables and copying rows from one to the other.
Now if you already have a data table created and wand to add rows to it from a Linq to SQL query you can do the following assuming that the DataTable that exists is called dt, see code snippet. If no table exists yet you will need to create one then use below code.

Fernando
Dim dc As New UserDataContext()
Dim q = From iterVar In dc.UserDetails
            Select iterVar.ID, iterVar.FirstN, iterVar.LastN

For Each row In q
    Dim dr As DataRow = dt.NewRow
    dr("ID") = row.ID
    dr("FirstN") = row.FirstN
    dr("LastN") = row.LastN
    dt.Rows.Add(dr)
Next

DataGridView1.DataSource = dt

Open in new window

0
 
Gautham JanardhanCommented:
i dont think there is anything built in .net to convert a linq query to DataTable
0
 
Gautham JanardhanCommented:
ooops sorry my bad... scratch my previous post..

try this


Dim dc As New UserDataContext
Dim q As IEnumerable(Of DataRow) = From iterVar In dc.UserDetails _
  Select iterVar
Dim dtTest As DataTable = q.CopyToDataTable();
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
cdakzAuthor Commented:
That causes the same error as my first code example. The only difference between it and your example seems to be the Select cause.

Is there something else that should be different, to fix the "Unable to cast object of type 'System.Data.Linq.DataQuery`1[VB$AnonymousType_0`3[System.Int32,System.String,System.String]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'." error?
0
 
Gautham JanardhanCommented:
what is the type of dc.UserDetails ??  is dc a data set ? the method we are playing around with(CopyToDataTable) will only work if 'dc' is a dataset/datatable

other wise we need to create a method which will do the conversion.
0
 
cdakzAuthor Commented:
dc.UserDetails is just a regular datacontext for use by LINQ. What I'm trying to do is take LINQ query and convert it into a datatable. This can be done according to http://msdn.microsoft.com/en-us/library/bb386921.aspx.

However, I can't get the code to work.
0
 
Gautham JanardhanCommented:
yep, in the article u have posted order is a data table extracted from ds.Tables("SalesOrderHeader"). In your case as dc.UserDetails from context and hence it wont work.
0
 
cdakzAuthor Commented:
Oh, yeah, I totally missed that. I was Googling on "fill datatable with linq query" and most of the pages I saw *did* deal LINQ queries that did NOT use datatables, and so I overlooked this on  http://msdn.microsoft.com/en-us/library/bb3869

So, back to the original question. How to export a datatable from a LINQ (to SQL) query? Any simple methods?
0
 
cdakzAuthor Commented:
Thanks! The iterative code you provided is what I need to get my project done.
0
 
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.