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?

Solution 1

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!

Solution 2

Like this: (Assuming a typed dataset)

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

Solution 3

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

Solution 4

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.

View more solutions

Comments

[Justin Niessner]

Don't you still need the call to AsEnumerable()?

[SLaks]

@Justin: Not for a typed dataset. Tables in typed datasets inherit TypedTableBase<TRow>, which implements IEnumerable<TRow>.

[James123]

please provide me ... how iterate them

[James123]

I used attribute1_name there I am getting duplicate records

[SLaks]

@James: foreach(var pair in ...)

[MAW74656]

@SLaks -Where should the curly brace go?

[SLaks]

@MAW74656: Around the anonymous type.

[Andrew Barber]

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.

[Hari]

@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.

[Jogi]

What if there are 4 columns in a row and you want to distinct based on 2 columns?

[Si8]

How do I add an OrderBy for let's say column attribute2_name?