DataTable Error - Can't CopyToDataTable()

Question

Suppose I have the following 2 datatables:

Table1:                               Table2:
Col1:                                 Col1:         Col2:
1                                     1             a
2                                     2             b
2                                     3             c
3                                     4             d
3                                     5             e
4                                     6             f
                                      7             g

And I want a simple Linq query that creates a third DataTable based upon a join between the two and giving distinct values - Looking as follows:

Result:
Col1:       Col2:
1           a
2           b
3           c
4           d

I assumed this would do it:

Dim Result as DataTable = (From dr1 As DataRow In Table1.AsEnumerable
             Join dr2 As DataRow In Table2.AsEnumerable
             On dr1.Field(Of Double)("Col1") Equals dr2.Field(Of Double)("Col1")
             Select New With {
                                 .Col1 = dr1.Field(Of Double)("Col1"),
                                 .Col2 = dr2.Field(Of String)("Col2")
                             }).Distinct.CopyToDataTable

But I'm getting a host of different errors regardless what I try...

My first error is that CopyToDataTable method is not a member of System.Generic.IEnumerable

When I remove that, and leave it as:

Dim Result = (From dr1 As DataRow In Table1.AsEnumerable
             Join dr2 As DataRow In Table2.AsEnumerable
             On dr1.Field(Of Double)("Col1") Equals dr2.Field(Of Double)("Col1")
             Select New With {
                                 .Col1 = dr1.Field(Of Double)("Col1"),
                                 .Col2 = dr2.Field(Of String)("Col2")
                             }).Distinct

It runs, but it doesn't filter the results down to a unique list - It leaves all the col1 values in there from Table1.

I'm trying to understand Linq, but am obviously missing something - Can someone please explain to me what's going on here and how to fix this??

As an addition onto @Magnus' solution, for anyone who comes across this kind of problem in the future, the CopyToDataTable method for an IEnumerable of Anonymous Type (as Magnus pointed out was what I gave in this question) can be found here and an implementation of it here.

score 2 · Accepted Answer · edited May 23 '17 at 12:18

2

You need to add the Key identifier to the properties on Anonymous Types in VB. (In C# all properties are key properties) Only the values of key properties are compared in order to determine whether two instances are equal, and you need this for Distinct() to work.

Select New With {
                 Key .Col1 = dr1.Field(Of Double)("Col1"),
                 Key .Col2 = dr2.Field(Of String)("Col2")
                }

As for getting CopyToDataTable to work on anonymous types check out this answer.

edited May 23 '17 at 12:18

Community

1
1

answered Jan 14 '13 at 20:37

Magnus

45,362
8
80
118

Thank you!! - The first problem of duplicates being brought back was solved with the `Key` identifier, but I still can't get the `CopyToDataTable` to work... :( – John Bustos Jan 14 '13 at 20:41
1

`CopyToDataTable()` only works on `IEnumerable(of DataRow)` and you have IEnumerable of an anonymous type. – Magnus Jan 14 '13 at 20:43
Again, @Magnus, THANKS!! - That makes sense... the thing that got me confused is MSDN documentation like here: http://msdn.microsoft.com/en-us/library/bb386921.aspx - How do they manage to do it in the 3rd example? - Am I missing something??? – John Bustos Jan 14 '13 at 21:12
... I apologize - I read it again, they offer a routine to do the work for you... @Magnus, thank you very much!!!! – John Bustos Jan 14 '13 at 21:15

DataTable Error - Can't CopyToDataTable()

1 Answers1