?
Solved

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

Posted on 2010-09-11
10
Medium Priority
?
9,309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
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 63

Accepted Solution

by:
Fernando Soto earned 2000 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 63

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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