Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi all,

Really sorry to ask this as im sure its fairly simple once you know.
Im trying to join to datatables using linq and return columns from both tables and then insert the result into another datatable.
The problem is that I cant use the query.CopyToDataTable() method as there are anonymous types in the query (or something). Please see the code below.

I have been looking online now for absolutely ages! I have seen the ObjectShredder class advice given on MSDN but cant quite work it out, and i have tried a huge variety of other potential solutions, alas to no avail.

Could someone please either point out where im going wrong or may be a quick pointer on how to get the ObjectShredder Class to resolve this?
Any help at this stage is more than welcome!
Thanks in advance

Chris

The original bit of code:

Dim query = From a In dt _    Join b In dtLossEvents _    On _    a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _    Select New With { _                    a, _                    b _                    }


This it the latest flawed incarnation:
Dim query = From a In dt _            Join b In dtLossEvents _            On _            a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _            Select New With { _                             .Gross_Loss = a.Field(Of Double ("Gross_Loss"), _                             .Net_Loss = a.Field(Of Double)("Net_Loss"), _                             .EventID = b.Field(Of Integer)("EventID") _}
Posted
Updated 20-Aug-17 22:06pm

Hi there

Thanks so much for your help. Sorry its taken a few days to test.

Unfortunately I'm still getting the error "CopyToDataTable is not a member of System.Collections.Generic.IEnumerable(Of anonymous type)". Its very frustrating as i felt you had put me on the right course there. Below is my code, any help would be most welcome now!

Thanks all in advance

Chris





VB
Dim items = From a In dt.AsEnumerable() _
    Join b In dtLossEvents.AsEnumerable() On _
    a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _
    Select New With { _
                     .Gross_Loss = dt("Gross_Loss"), _
                     .Net_Loss = dt("Net_Loss"), _
                     .EventID = dtLossEvents("EventID") _
                    }



Dim result As DataTable = items.CopyToDataTable()
 
Share this answer
 
Comments
ledtech3 10-Jun-12 0:18am    
the object browser has CopyToDataTable listed as in
System.Data.DataTableExtension.CopyToDataTable......

just drop CopyToDataTable into the object browser search and you should find 3 references.
Hope that helps some.
wizardzz 13-Nov-12 17:55pm    
This should be posted as a reply to Solution #1, it is not an answer.
dmcgill50 13-Nov-12 18:20pm    
OK, seriously...I've gotten two pieces of feedback on a single solution post. The first one wanted to know how I "found" a question that was 2.5 years old. Answer: I used the ability to filter to a certain of question answered. Not that difficult and it appears as if that is what's intended by the developer of the site. ;)
Second told me not to answer questions that are too old as the OP won't care. I wasn't the OP and I cared, so I assume that those behind me will care. These Q&As aren't just for the OP. That should be obvious...
kesiw 15-Jul-13 13:39pm    
I am not following. I am using C# - I can't get the copyToDataTable to work. I am using a query that joins two tables. It gives results but fails when I try to copy the results to the datatable using copyToDataTable
I use C#, not VB.Net, so please forgive me for answering using samples in C#, but I hope I can provide some help nevertheless.

Your second example above seems to be 90% of the way to a working solution, with two things (possibly only one, because I don't know what conveniences VB.Net provides for Linq to DataTables) missing that are preventing you from getting a DataTable from Linq.

I created a test app and attempted to create a Linq query like the ones you model above. I discovered that I had to use from xxx in tableName.AsEnumerable() ... (where the call to AsEnumerable() was the crucial insight) to get Linq to accept a DataTable name in the from clause. If your Linq query would not compile, this might be your solution to that part of the problem.

The second thing I experimented with was getting Linq to return a DataTable - because, as you say, Linq by itself returns an IEnumerable of an anonymous type. This is where the ObjectShredder comes in. I copied the ObjectShredder example code verbatim, and it worked. To use the ObjectShredder, you convert the IEnumerable returned by your Linq query into a DataTable with the following: DataTable result = queryResult.CopyToDataTable();.

Here's my test method:
C#
private static DataTable JoinDataTablesWithLinq()
    {
    var query = from inv in _tblInvoice.AsEnumerable()
                join item in _tblLineItem.AsEnumerable()
                    on inv["InvoiceId"] equals item["InvoiceId"]
                select new
                        {
                            CustomerName = inv["CustomerName"],
                            ItemName = item["ItemName"],
                            Quantity = item["Quantity"]
                        };

    return query.CopyToDataTable();
    }


Here are some useful links:
ObjectShredder (which you probably already have): http://msdn.microsoft.com/en-us/library/bb669096.aspx[^]
Using Linq with DataTables: http://dotnetarchitect.wordpress.com/2009/03/18/using-linq-to-manipulate-data-in-datasetdatatable/[^]
 
Share this answer
 
Comments
dmcgill50 13-Nov-12 18:13pm    
Dim items = From a In dt.AsEnumerable() _
Join b In dtLossEvents.AsEnumerable() On _
a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _
Select New With { _
.Gross_Loss = dt("Gross_Loss"), _
.Net_Loss = dt("Net_Loss"), _
.EventID = dtLossEvents("EventID") _
}



Dim result As DataTable = items.CopyToDataTable()


should be

Dim items = From a In dt.AsEnumerable() _
Join b In dtLossEvents.AsEnumerable() On _
a.Field(Of Integer)("EventID") Equals b.Field(Of Integer)("EventID") _
Select New With { _
.Gross_Loss = a("Gross_Loss"), _
.Net_Loss = a("Net_Loss"), _
.EventID = b("EventID") _
}



Dim result As DataTable = items.CopyToDataTable()

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900