From a VB multi-column DataTable select a single column of Sorted Distinct values copied to another DataTable

Question

I am recently starting out in my use of LINQ in VB.Net and clearly I am in need of help and guidance.

I have created a multi-column DataTable (TempSrvcCallTable) from which I am trying to extract a single-column(where Col is a string holding the name of the field) Sorted list  of Distinct values which I want to return in another DataTable.

The nearest I have come is:

        Dim FilterLINQ = From R In TempSrvcCallTable.AsEnumerable()
                         Select R.Field(Of String)(Col).Distinct.OrderBy(Function(x) x)
        dt = FilterLINQ.CopyToDataTable

Unfortunately the FilterLINQ.CopyToDataTable statement generates the error message " 'CopyToDataTable' is not a member of 'EnumerableRowCollection(Of IOrderedEnumerable(of Char))' " - and I don't know what this means or how to fix it.

As ever, any help or assistance will be very gratefully received.

Paul J

Answer 1

Hi Paul,

I'm not sure if the VB syntax is totally correct or not (I use C#), but this should work ... just tweak it for VB if you need to. For this particular scenario, you don't have to use LINQ.

dt = TempSrvcCallTable.DefaultView.ToTable(true, New string[] { col })

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Answer 2

Many, many thanks Bonnie - a quick C# to VB adjustment and it works!

dt = TempSrvcCallTable.DefaultView.ToTable(True, Col)

Paul J

Answer 3

You're welcome, Paul. Glad I could help.

In my reply, I used a different syntax for adding the column names to show what you would use if you needed more than one column: New string[] { col }  ... however, simply listing the column names is really all you had to use. Ignore my archaic syntax (not sure why I had it that way in my little test app, either way is correct).  So, for multiple columns it could be: ToTable(True,Col1, Col2, Col3). The True "distinct" in that case would mean distinct for all columns, but I know you just wanted one column.

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com