Get distinct items from DataTable using LINQ

Question

I have data being returned in a DataTable that looks like:

Department | EmployeeName

1 | Employee1

1 | Employee1

2 | Employee2

3 | Employee3

3 | Employee3

3 | Employee3

I'm trying to get only distinct rows and put it into a collection, like so:

IEnumerable<Department> departments = dt.AsEnumerable().Select(row =>
    new Department
        {
            DepartmentID = Int32.Parse(row["DepartmentID"].ToString()),
            Employee = new Employee { EmployeeName = row["EmployeeName"].ToString() }
        }).Distinct();

It should only return 3 rows, but instead it's returning all 6.

What am I doing wrong here?

Answer 1

what i think the problem here is that you are creating different objects with have no implementation for comparing the equality based on some propertiese. so the Distinct() method compares the refference of the objects and hence concludes that all the objects are different because they point to different reffrences.

what you shoud do instead is try parsing the information in an object that implements IEqualityComparer and then use .Select(<IEqualityComparer>).Distinct()

Answer 2

Class/Schema

public class abc
{
    public int id;
}

---

DataTable and Records

DataTable dt = new DataTable();
DataColumn dc = new DataColumn("id", Type.GetType("System.Int32"));
dt.Columns.Add(dc);

DataRow dr = dt.NewRow();
dr["id"] = 1;
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["id"] = 1;
dt.Rows.Add(dr);

---

Query

var qa = dt.AsEnumerable()
.Select(row => new
{
    Id = row.Field<int>("id"),
})
.Distinct();

---

Answer 3

Not sure but one of the following may work for you ..........

just do this

var distrows= table1.AsEnumerable().Distinct();

will do your task..........than create collection of department form the distrow..

IEnumerable<Department> departments = (from DataRow dRow in distrows   
                         new Department
        {
            DepartmentID = Int32.Parse(row["DepartmentID"].ToString()),
            Employee = new Employee { EmployeeName = row["EmployeeName"].ToString() }
        });

OR

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


IEnumerable<Department> departments = (from DataRow dRow in distrows   
                             new Department
            {
                DepartmentID = Int32.Parse(distinctRows.col1),
                Employee = new Employee { EmployeeName = distinctRows.col2.ToString() }
            });