Select distinct rows from datatable in Linq

Question

I am trying to get distinct rows based on multiple columns (attribute1_name, attribute2_name) and get datarows from datatable using Linq-to-Dataset.

I want results like this

attribute1_name    attribute2_name
--------------     ---------------

Age                State
Age                weekend_percent
Age                statebreaklaw
Age                Annual Sales
Age                Assortment

How to do thin Linq-to-dataset?

score 45 · Accepted Answer · answered Jul 14 '10 at 02:22

45

If it's not a typed dataset, then you probably want to do something like this, using the Linq-to-DataSet extension methods:

var distinctValues = dsValues.AsEnumerable()
                        .Select(row => new {
                            attribute1_name = row.Field<string>("attribute1_name"),
                            attribute2_name = row.Field<string>("attribute2_name")
                         })
                        .Distinct();

Make sure you have a using System.Data; statement at the beginning of your code in order to enable the Linq-to-Dataset extension methods.

Hope this helps!

answered Jul 14 '10 at 02:22

David Hoerster

28,421
8
67
102

I used attribute1_name there I am getting duplicate records – James123 Jul 14 '10 at 02:56
@above You should use "row" during row.Field... I used some other datarow object in from my other loop which yielded me duplicate values.Later I corrected. – Hari Nov 14 '14 at 15:16
What if there are 4 columns in a row and you want to distinct based on 2 columns? – Jogi May 10 '16 at 20:47
How do I add an `OrderBy` for let's say column `attribute2_name`? – Si8 May 17 '17 at 13:49

score 5 · Answer 2 · answered Jul 14 '10 at 02:08

5

Like this: (Assuming a typed dataset)

someTable.Select(r => new { r.attribute1_name, r.attribute2_name }).Distinct();

answered Jul 14 '10 at 02:08

SLaks

868,454
176
1,908
1,964

Don't you still need the call to AsEnumerable()? – Justin Niessner Jul 14 '10 at 02:09
@Justin: Not for a typed dataset. Tables in typed datasets inherit `TypedTableBase`, which implements `IEnumerable`. – SLaks Jul 14 '10 at 02:10
please provide me ... how iterate them – James123 Jul 14 '10 at 02:16
@James: `foreach(var pair in ...)` – SLaks Jul 14 '10 at 13:43

score 4 · Answer 3 · edited Sep 17 '12 at 17:33

4

var Test = (from row in Dataset1.Tables[0].AsEnumerable()
            select row.Field<string>("attribute1_name") + row.Field<int>("attribute2_name")).Distinct();

edited Sep 17 '12 at 17:33

Adi Lester

24,731
12
95
110

answered Sep 17 '12 at 17:03

shiju87

112
2
11

score 1 · Answer 4 · edited May 23 '17 at 11:53

1

Check this link

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

Or try this

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

EDIT: Placed the missing first curly brace.

edited May 23 '17 at 11:53

Community

1
1

answered Jul 14 '10 at 05:24

Johnny

1,555
3
14
23

@SLaks -Where should the curly brace go? – MAW74656 Feb 10 '12 at 21:23
1

@MAW74656: Around the anonymous type. – SLaks Feb 10 '12 at 23:40

score 0 · Answer 5 · edited Apr 23 '13 at 14:33

0

Dim distinctValues As List(Of Double) = (From r In _
DirectCast(DataTable.AsEnumerable(),IEnumerable(Of DataRow)) Where (Not r.IsNull("ColName")) _
Select r.Field(Of Double)("ColName")).Distinct().ToList()

edited Apr 23 '13 at 14:33

Rob

4,927
12
49
54

answered Apr 23 '13 at 14:18

user2311715

1

3

Welcome to Stack Overflow! Would you consider adding some narrative to explain why this code works, and what makes it an answer to the question? This would be very helpful to the person asking the question, and anyone else who comes along. – Andrew Barber Apr 23 '13 at 18:51

score 0 · Answer 6 · answered Dec 16 '15 at 13:31

We can get the distinct similar to the example shown below

 //example
           var  distinctValues =  DetailedBreakDown_Table.AsEnumerable().Select(r => new
            {
                InvestmentVehicleID = r.Field<string>("InvestmentVehicleID"),
                Universe = r.Field<string>("Universe"),
                AsOfDate = _imqDate,
                Ticker = "",
                Cusip = "",
                PortfolioDate = r.Field<DateTime>("PortfolioDate")

            } ).Distinct();

Select distinct rows from datatable in Linq

6 Answers6

Linked