LINQ Select distinct on DataTable not working

Question

I have the following datatable - Let's say called MyTable:

Col1:    Col2:    Col3:    Col4:
1        abc      def      <null>
2        abc      def      ghi
1        abc      def      <null>
3        abc      def      <null>
1        abc      def      <null>

And I'm trying to get the distinct rows:

Col1:    Col2:    Col3:    Col4:
1        abc      def      <null>
2        abc      def      ghi
3        abc      def      <null>

I tried the following LINQ statement:

MyTable = (From dr As DataRow In MyTable Select dr).Distinct.CopyToDataTable

But it's returning the original datatable with the repeated rows back to me.

What am I doing wrong AND how can I get my desired output??

check this - http://stackoverflow.com/questions/3242892/select-distinct-rows-from-datatable-in-linq — rs., Jan 04 '13 at 21:38

Servy · Accepted Answer · 2013-01-04T21:42:16.497

Distinct relies on the objects all implementing IEquatable and/or having sensible implementations of GetHashCode and Equals. The DataRow class...doesn't. It doesn't check that the value of each column is equal in the Equals method, it just uses the default implementation, which is to say that it checks that the references are equal, and that's not what you want.

You can provide your own IEqualityComparer, since you can't change the methods in DataRow.

The following should work, if you provide an instance of it to to Distinct:

public class DataRowComparer : IEqualityComparer<DataRow>
{
    public bool Equals(DataRow x, DataRow y)
    {
        for (int i = 0; i < x.Table.Columns.Count; i++)
        {
            if (!object.Equals(x[i], y[i]))
                return false;
        }
        return true;
    }

    public int GetHashCode(DataRow obj)
    {
        unchecked
        {
            int output = 23;
            for (int i = 0; i < obj.Table.Columns.Count; i++)
            {
                output += 19 * obj[i].GetHashCode();
            }
            return output;
        }
    }
}

Thanks, @Servy - Makes sense... How could I get my desired result? I'm guessing i'm going to have to look at `ItemArray`, but can't figure out how to do it... — John Bustos, Jan 04 '13 at 21:38
Adding to @Servy Answer above, you can use Distinct(DataRowComparer.Default) to get unique rows — Ninja, Aug 21 '23 at 02:37

score 2 · Answer 2 · answered Jan 04 '13 at 21:37

2

Try like this;

var distinctRows = (from DataRow dRow in MyTable.Rows
                    select new {col1=dRow["Col1"],col2=dRow["Col2"], col3=dRow["Col3"], col4=dRow["Col4"]}).Distinct();

foreach (var row in distinctRows) 
{
 //
}

answered Jan 04 '13 at 21:37

Soner Gönül

97,193
102
206
364

Thanks @Soner - I found this solution online in a few places, but my example really trivialized the problem - My real datatable has about 20 columns... Is there a way to do it without having to mention each column explicitly?? – John Bustos Jan 04 '13 at 21:41
@JohnBustos Hmm.. I'm not sure but check http://stackoverflow.com/questions/1681715/how-can-i-access-the-loop-index-inside-a-linq-select and http://stackoverflow.com/questions/1044236/nested-foreach-vs-lambda-linq-query-performancelinq-to-objects – Soner Gönül Jan 04 '13 at 21:44
@JohnBustos Yes, there is a way to do it without mentioning each option. See my answer. It should work for any datatable. – Servy Jan 04 '13 at 21:46

score 1 · Answer 3 · answered Jan 04 '13 at 22:01

Although @Servy's question truly was the correct way to do this - Thank you so much Servy! I also found another solution that at least seemed cleaner:

    MyTable = MyTable.DefaultView.ToTable(True)

This allows you to ask for only distinct records, but negates by original request of doing it via LINQ - Figured I'd add it in for anyone else looking at this question in the future.

score 0 · Answer 4 · edited Jan 04 '13 at 21:40

0

The problem is that every DataRow in your DataTable is a different object. Each is an instance.

edited Jan 04 '13 at 21:40

Soner Gönül

97,193
102
206
364

answered Jan 04 '13 at 21:36

Arturo Martinez

3,737
1
22
35

LINQ Select distinct on DataTable not working

4 Answers4

Linked