Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-11
10
Medium Priority
?
9,450 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 64

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 64

Expert Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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