Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: LINQ VB.NET
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 10-Jun-10 2:47am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

I searched for days and finally came across this great function written by Lus Oliveira.
( Article )
The actual function:
Public Function EQToDataTable(ByVal parIList As System.Collections.IEnumerable) As System.Data.DataTable
        Dim ret As New System.Data.DataTable()
        Try
            Dim ppi As System.Reflection.PropertyInfo() = Nothing
            If parIList Is Nothing Then Return ret
            For Each itm In parIList
                If ppi Is Nothing Then
                    ppi = DirectCast(itm.[GetType](), System.Type).GetProperties()
                    For Each pi As System.Reflection.PropertyInfo In ppi
                        Dim colType As System.Type = pi.PropertyType
                        If (colType.IsGenericType) AndAlso
                           (colType.GetGenericTypeDefinition() Is GetType(System.Nullable(Of ))) Then colType = colType.GetGenericArguments()(0)
                        ret.Columns.Add(New System.Data.DataColumn(pi.Name, colType))
                    Next
                End If
                Dim dr As System.Data.DataRow = ret.NewRow
                For Each pi As System.Reflection.PropertyInfo In ppi
                    dr(pi.Name) = If(pi.GetValue(itm, Nothing) Is Nothing, DBNull.Value, pi.GetValue(itm, Nothing))
                Next
                ret.Rows.Add(dr)
            Next
            For Each c As System.Data.DataColumn In ret.Columns
                c.ColumnName = c.ColumnName.Replace("_", " ")
            Next
        Catch ex As Exception
            ret = New System.Data.DataTable()
        End Try
        Return ret
    End Function
 
It is simply called by:
dim q = <linq query="" you="" write=""></linq>
Dim dt as DataTable = EQToDataTable(q)
  Permalink  
Comments
CHill60 at 30-May-14 21:48pm
   
4 years late for the OP though
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

This may help
 
<a href="http://dotnetinbox.blogspot.in/2013/02/linq-query-to-datatable.html">linq to datatable</a>
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
 

 

 
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()
  Permalink  
Comments
ledtech3 at 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 at 13-Nov-12 17:55pm
   
This should be posted as a reply to Solution #1, it is not an answer.
dmcgill50 at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
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/[^]
  Permalink  
Comments
dmcgill50 at 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)

  Print Answers RSS
0 Marcin Kozub 330
1 OriginalGriff 256
2 Sergey Alexandrovich Kryukov 215
3 Praneet Nadkar 197
4 Richard MacCutchan 182
0 OriginalGriff 8,048
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 30 May 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100