get distinct rows from datatable using Linq (distinct with mulitiple columns)

Question

I am trying to distinct on multiple columns and get datarows from datatable. but getting error.

 Dim query As IEnumerable(Of DataRow) = 
            (From row As DataRow In SourceTable.AsEnumerable() _
             Select row.Field(Of String)("ColumnName1"),
                    row.Field(Of String)("ColumnName2") ).Distinct()

below error:

Unable to cast object of type '<DistinctIterator>d__7a`1[System.String]' 
to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

I want another datatable with distinct row based on given columns from SourceTable.

you could use the distinct support in datatable - http://social.msdn.microsoft.com/forums/en-US/adodotnetdataset/thread/ed9c6a6a-a93e-4bf5-a892-d8471b84aa3b/ ToTable() has an overload which lets you specify whether or not to return only distinct values, and a params string[] argument to specify which columns you want in the new table. — James Manning, Jul 13 '10 at 05:42

score 4 · Accepted Answer · answered Jul 13 '10 at 06:59

4

Try This one then

Dim query = From q In (From p In dt.AsEnumerable() Select New With {.col1= p("ColumnName1"), .col2 = p("ColumnName2")}) Select q.col1, q.col2 Distinct

answered Jul 13 '10 at 06:59

Johnny

1,555
3
14
23

How to user 'query' objects. I mean How to iterate that? – James123 Jul 14 '10 at 01:42

score 0 · Answer 2 · answered Jul 13 '10 at 05:12

0

Try this (a bit of a guess on my part):

Dim query As IEnumerable(Of DataRow) =  
        (From row As DataRow In SourceTable.AsEnumerable().Distinct()  _ 
         Select row.Field(Of String)("ColumnName1"), 
                row.Field(Of String)("ColumnName2"))

answered Jul 13 '10 at 05:12

Aaronontheweb

8,224
6
32
61

Ah shit I should have paid more attention to the error message. I've got it now. Your problem isn't your SQL syntax - it's the datatype you expect since you're marking it as an Enumerable collection of DataRows when you're projecting tuples which contain a pair of strings. – Aaronontheweb Jul 13 '10 at 05:48
Change the syntax so you're projecting a new DataRow with each of the strings added as columns - either that or strip the As IEnumerable(Of DataRow) and just accept the implict type as a result. – Aaronontheweb Jul 13 '10 at 05:50

score 0 · Answer 3 · answered Jul 13 '10 at 05:17

0

Try this

var distinctRows = (from DataRow dRow in dTable.Rows
                    select new col1=dRow["dataColumn1"],col2=dRow["dataColumn2"]}).Distinct();

this is in C#. Convert it into vb.net

answered Jul 13 '10 at 05:17

Johnny

1,555
3
14
23

You're missing a curly brace at the front of your LINQ projection, but I get your meaning. – Aaronontheweb Jul 13 '10 at 05:18
This one is not returing distinct columns – James123 Jul 13 '10 at 05:30

get distinct rows from datatable using Linq (distinct with mulitiple columns)

3 Answers3

Linked