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?

Selfheal

Answer 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!

Selfabuse

I used attribute1_name there I am getting duplicate records – Selfheal

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

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

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

Answer 2

Like this: (Assuming a typed dataset)

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

Cappella

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

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

please provide me ... how iterate them – Selfheal

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

Answer 3

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

Orthotropic

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

Halliday

@Cappella -Where should the curly brace go? – Chardin

@MAW74656: Around the anonymous type. – Cappella

Answer 5

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()

Comprehensible

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. – Percival

Answer 6

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();

Cyndycynera