Solved

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

Posted on 2010-09-11
10
8,632 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:cdakz
  • 4
  • 4
  • 2
10 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 33654156
i dont think there is anything built in .net to convert a linq query to DataTable
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 33654162
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
 

Author Comment

by:cdakz
ID: 33654213
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 33654242
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
 

Author Comment

by:cdakz
ID: 33654271
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 33654350
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
 

Author Comment

by:cdakz
ID: 33654367
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
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 33654370
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
 

Author Closing Comment

by:cdakz
ID: 33654451
Thanks! The iterative code you provided is what I need to get my project done.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33654502
Not a problem, glad I was able to help.  ;=)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now