ccampj ccampj - 1 month ago 16
C# Question

Select Distinct Row from Data Table with Dynamic Columns

I need to run a LINQ query against a DataTable to get distinct values using multiple columns. If I know the columns I need to select beforehand, I can use the answer from David Hoarser at Select distinct rows from datatable in Linq:

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


However, in my case, I have the column names I need to select in a list of objects:

public class PC{
public string Name{get;set;}
public string NumFormat{get;set}
}
List<PC>cols=new List<PC>();
cols.AddRange(new PC[]{new PC{Name="FullName",NumFormat=""},
{new PC{Name="Salary",NumFormat="$ #,##0"}});


I could use David's example from above to build the select statement as:

var distinctValues = dsValues.AsEnumerable()
.Select(row => new {
attribute1_name = row.Field<string>(cols[0].Name),
attribute2_name = row.Field<string>(cols[1].Name)
})
.Distinct();


But this won't work since I don't know how many columns I need to select Distinct over since the cols list can vary. How can I loop over the list of columns to build my Select statement to ultimately get my Distinct values?

Answer

You can implement custom IEqualityComparer for that task

class DataRowComparer : IEqualityComparer<DataRow>
{
    private readonly List<string> _columns = new List<string>();
    public DataRowComparer(DataColumnCollection cols)
    {
        foreach (DataColumn col in cols)
        {
            _columns.Add(col.ColumnName);
        }
    }

    public bool Equals(DataRow x, DataRow y)
    {
        foreach (var column in _columns)
        {
            if (!IsEqual(x, y, column))
                return false;
        }

        return true;
    }


    public int GetHashCode(DataRow obj)
    {
        unchecked
        {
            var hash = 19;
            foreach (var column in _columns)
            {
                hash = hash*31 + (obj[column] == null ? 0 : obj[column].GetHashCode());
            }

            return hash;
        }
    }

    private static bool IsEqual(DataRow x, DataRow y, string column)
    {
        if (x[column] == null && y[column] == null)
            return true;

        if (x[column] == null || y[column] == null)
            return false;

        return x[column].Equals(y[column]);
    }
}

Usage

var distinctValues = dsValues
                 .AsEnumerable()
                 .Distinct(new DataRowComparer(dsValues.Columns))
                 .ToList();

You can also create extension method for DataTable

static class DataTableExtension
{
    public static IEnumerable<DataRow> DistinctRow(this DataTable table)
    {
        return table
            .AsEnumerable()
            .Distinct(new DataRowComparer(table.Columns));
    }
}

Usage

var distinctValues = dsValues
                    .DistinctRow()
                    .ToList();
Comments