Delete duplicate rows from datatable based on 2 columns in c#

Question

How do I delete the duplicate rows from the datatable where there are combinations of same name and dept combinations? I need to keep one entry.

DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("Name");
dt.Columns.Add("Dept");

dt.Rows.Add(1, "Test1", "Sample1");
dt.Rows.Add(2, "Test2", "Sample2");
dt.Rows.Add(3, "Test3", "Sample3");
dt.Rows.Add(4, "Test4", "Sample4");  // Duplicate 
dt.Rows.Add(5, "Test4", "Sample4");  // Duplicate 
dt.Rows.Add(6, "Test4", "Sample4");  // Duplicate 
dt.Rows.Add(7, "Test4", "Sample5");

Result data table should be,

dt.Rows.Add(1, "Test1", "Sample1");
dt.Rows.Add(2, "Test2", "Sample2");
dt.Rows.Add(3, "Test3", "Sample3");
dt.Rows.Add(4, "Test4", "Sample4");  
dt.Rows.Add(6, "Test4", "Sample5");

How can I do this in c#

A "silly" algorithm would be to loop over each row, create a copy, store it in a list if not stored yet. Before storing you do your comparison. Your list will have no duplicates. The bigger the table, the slower this algorithm will be. — user1085665
– user1085665, Commented Apr 7, 2020 at 22:37
I think the answer to this question may be what you need: stackoverflow.com/questions/3242892/… — Mark Arend
– Mark Arend, Commented Apr 7, 2020 at 23:12

jdweng · Accepted Answer · 2020-04-07 23:21:38Z

2

Simple

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Dept", typeof(string));

            dt.Rows.Add(1, "Test1", "Sample1");
            dt.Rows.Add(2, "Test2", "Sample2");
            dt.Rows.Add(3, "Test3", "Sample3");
            dt.Rows.Add(4, "Test4", "Sample4");  // Duplicate 
            dt.Rows.Add(5, "Test4", "Sample4");  // Duplicate 
            dt.Rows.Add(6, "Test4", "Sample4");  // Duplicate 
            dt.Rows.Add(7, "Test4", "Sample5");

            DataTable dt2 = dt.AsEnumerable()
                .OrderBy(x => x.Field<int>("id"))
                .GroupBy(x => new { name = x.Field<string>("Name"), dept = x.Field<string>("Dept") })
                .Select(x => x.First())
                .CopyToDataTable();

        }
    }
}

answered Apr 7, 2020 at 23:21

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

blue Over a year ago

I'm using .net core where I don't have AsEnumerable() extension

jdweng Over a year ago

The following article from Oct 2018 (before core 3.0) says core has linq. Did you try to put a "using System.Linq" at top of module? learn.microsoft.com/en-us/dotnet/csharp/tutorials/…

blue Over a year ago

Adding System.Data.DataSetExtensions nuget package helped

Lorenzo Bassetti Over a year ago

Excellent solution, thanks!

DarkPh03n1X · Accepted Answer · 2021-06-25 07:33:49Z

2

Here is a function that I got from someone, somewhere:

Usage:

List<string> columnName = new List<string> { "ID", "coulmn1", "coulmn_2", "Another", "however_many_columns_you_want_really" };
dataGrid = RemoveDuplicatesFromDataTable(dataGrid, columnName);

Function:

static DataTable RemoveDuplicatesFromDataTable(DataTable table, List<string> keyColumns)
{
    Dictionary<string, string> uniquenessDict = new Dictionary<string, string>(table.Rows.Count);
    StringBuilder stringBuilder = null;
    int rowIndex = 0;
    DataRow row;
    DataRowCollection rows = table.Rows;
    while (rowIndex < rows.Count - 1)
    {
        row = rows[rowIndex];
        stringBuilder = new StringBuilder();
        foreach (string colname in keyColumns)
        {
            //stringBuilder.Append(((double)row[colname]));
            stringBuilder.Append(row[colname]);
        }
        if (uniquenessDict.ContainsKey(stringBuilder.ToString()))
        {
            rows.Remove(row);
        }
        else
        {
            uniquenessDict.Add(stringBuilder.ToString(), string.Empty);
            rowIndex++;
        }
    }

    return table;
}

answered Jun 25, 2021 at 7:33

DarkPh03n1X

6801 gold badge10 silver badges19 bronze badges

1 Comment

Lorenzo Bassetti Over a year ago

Very good solution, I just add my two cents. while (rowIndex < rows.Count - 1) will return 2 lines with same values in the DT , while if you want to have only one (unique) row, correct code is while (rowIndex < rows.Count )

Collectives™ on Stack Overflow

Delete duplicate rows from datatable based on 2 columns in c#

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related