Solved

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

Posted on 2010-09-11
10
9,140 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Expert Comment

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

Featured Post

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 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