Getting duplicates count for each distinct value from a datatable

Question

Hello

I've a datatable which has a single text column 'Title' which can have multiple values with duplicates. I can remove the duplicates using a dataview.

DataView v = new DataView(tempTable);
tempTable = v.ToTable(true, "Title");

But how can i get the number of duplicates for each distinct value without any looping?

Answer 1

The way I would get the results that you want would look something like this:

tempTable.Rows.Cast<DataRow>()
    .Select(dr => Convert.ToString(dr[0]))
    .GroupBy(dr => dr)
    .Select(g => new { Title = g.Key, Count = g.Count() });

However, it's actually looping under the hood. In fact, I can't think of a way to do that kind of a grouping without inspecting each record.

The drawback is that the result of that expression is a sequence of anonymous type instances. If you still want the result to be a DataView, you could rewrite the last Select to create a new DataRow with two columns, and shove them into a new DataTable which you pass to the DataView.

Answer 2

If you don't want to loop or use Linq, so there is no way to do that but you can use a computed column on the data table with one more condition if applicable with you. That is the data should be in two related tables like this.

DataRelation rel = new DataRelation("CustToOrders", data.Tables["Customers"].Columns["customerid"],    data.Tables["Orders"].Columns["customerid"]);
data.Relations.Add(rel);

Given that customerid field as a Foreign key in the Orders table so it has duplicates. You can get the count of the duplicates this way:

data.Tables["Customers"].Columns.Add("Duplicates",
   GetType(Decimal), "Count(child.customerid)");