What is the right way to show only group by column in datatable

Question

I have below data in my Datatable

on above data, I'm filtering those records which are duplicate using below LINQ query

DataTable HasDuplicates = dt.AsEnumerable()
                              .GroupBy(g => g["Empolyee_CRC"])
                              .Where(c => c.Count() > 1)
                              .Select(g => g.OrderBy(r => r["Empolyee_CRC"]).First())
                              .CopyToDataTable();

Which returns me below data

Above data is successfully filtered but the issue which I'm facing is Budget_CRC column is not remove. So, for this I write below query and achieved my desire result.

DataTable HasDuplicates = dt.AsEnumerable()
                          .GroupBy(g => g["Empolyee_CRC"])
                          .Where(c => c.Count() > 1)
                          .Select(g => g.OrderBy(r => r["Empolyee_CRC"]).First())
                          .CopyToDataTable();

HasDuplicates.Columns.Remove("Budget_CRC");
HasDuplicates.AcceptChanges();

My question is how I get only one (Emplolyee_CRC) filtered column using my above LINQ query without writing below lines of code

HasDuplicates.Columns.Remove("Budget_CRC");
HasDuplicates.AcceptChanges();

Answer 1

You need to load another DataTable if you want your query to be returns DataTable

So use below query,

DataTable dtResult = new DataTable();
dtResult.Columns.Add("Empolyee_CRC");

DataTable HasDuplicates = dt.AsEnumerable()
                            .GroupBy(g => g["Empolyee_CRC"])
                            .Where(c => c.Count() > 1)
                            .OrderBy(x => x.Key)
                            .Select(g => dtResult.LoadDataRow(new object[] { g.FirstOrDefault().Field<string>("Empolyee_CRC") }, false))
                            .CopyToDataTable();

OR

DataTable HasDuplicates = dt.AsEnumerable()
                            .GroupBy(g => g["Empolyee_CRC"])
                            .Where(c => c.Count() > 1)
                            .OrderBy(x => x.Key)
                            .Select(g => dtResult.LoadDataRow(new object[] { g.Key }, false))
                            .CopyToDataTable();

Output:

Edit:

If you want to avoid creating new data table then you can simply project your selected data into list like.

In if below you can use any of both condition that separated with && or you can use as it is. depending upon your need.

if (dt.Rows.Count > 0 && dt.AsEnumerable().Select(x => x["Empolyee_CRC"]).Count() > 0)
{
    var result = dt.AsEnumerable()
                   .GroupBy(g => g["Empolyee_CRC"])
                   .Where(c => c.Count() > 1)
                   .OrderBy(x => x.Key)
                   .Select(g => new { Empolyee_CRC = g.FirstOrDefault().Field<string>("Empolyee_CRC") })
                   .ToList();
}

Output:

Answer 2

It seems that you have wrong select statement in your LINQ, consider following example:

DataTable HasDuplicates = dt.AsEnumerable()
                          .GroupBy(g => g["Empolyee_CRC"])
                          .Where(c => c.Count() > 1)
                          .Select(g => g
                                .OrderBy(r => r["Empolyee_CRC"])
                                .Select(r => r["Empolyee_CRC"])
                                .First())
                          .CopyToDataTable();