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 - social.msdn.microsoft.com/forums/en-US/adodotnetdataset/thread/… 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
– James Manning, Commented Jul 13, 2010 at 5:42

Johnny · Accepted Answer · 2010-07-13 06:59:09Z

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, 2010 at 6:59

Johnny

1,5733 gold badges14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James123 Over a year ago

How to user 'query' objects. I mean How to iterate that?

Aaronontheweb · Accepted Answer · 2010-07-13 05:12:36Z

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, 2010 at 5:12

Aaronontheweb

8,4096 gold badges34 silver badges63 bronze badges

2 Comments

Aaronontheweb Over a year ago

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 Over a year ago

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.

Johnny · Accepted Answer · 2010-07-13 05:17:36Z

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, 2010 at 5:17

Johnny

1,5733 gold badges14 silver badges23 bronze badges

2 Comments

Aaronontheweb Over a year ago

You're missing a curly brace at the front of your LINQ projection, but I get your meaning.

James123 Over a year ago

This one is not returing distinct columns

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related