CODEWITHSUNDEEP
c#asp.netlinqdatatable
Ba5t14n
Ba5t14n

Reputation: 739

Best way to remove duplicates from DataTable depending on column values

I have a DataSet which contains just one Table, so you could say I'm working with a DataTable here.

The code you see below works, but I want to have the best and most efficient way to perform the task because I work with some data here.

Basically, the data from the Table should later be in a Database, where the primary key - of course - must be unique.

The primary key of the data I work with is in a column called Computer Name. For each entry we also have a date in another column date.

I wrote a function which searches for duplicates in the Computer Name column, and then compare the dates of these duplicates to delete all but the newest.

The Function I wrote looks like this:

private void mergeduplicate(DataSet importedData)
{
    Dictionary<String, List<DataRow>> systems = new Dictionary<String, List<DataRow>>();
    DataSet importedDataCopy = importedData.Copy();
    importedData.Tables[0].Clear();
    foreach (DataRow dr in importedDataCopy.Tables[0].Rows)
    {
        String systemName = dr["Computer Name"].ToString();
        if (!systems.ContainsKey(systemName)) 
        {
            systems.Add(systemName, new List<DataRow>());
        }
        systems[systemName].Add(dr);
    }


    foreach (KeyValuePair<String,List<DataRow>> entry in systems) {
        if (entry.Value.Count > 1) {
            int firstDataRowIndex = 0;
            int secondDataRowIndex = 1;
            while (entry.Value.Count > 1) {
                DateTime time1 = Validation.ConvertStringIntoDateTime(entry.Value[firstDataRowIndex]["date"].ToString());
                DateTime time2 = Validation.ConvertStringIntoDateTime(entry.Value[secondDataRowIndex]["date"].ToString());

                //delete older entry
                if (DateTime.Compare(time1,time2) >= 0) {
                    entry.Value.RemoveAt(firstDataRowIndex);
                } else {
                    entry.Value.RemoveAt(secondDataRowIndex);
                }
            }
        }
        importedData.Tables[0].ImportRow(entry.Value[0]);
    }
}

My Question is, since this code works - what is the best and fastest/most efficient way to perform the task?

I appreciate any answers!

Upvotes: 0

Views: 1953

Answers (3)

Jason Boyd
Jason Boyd

Reputation: 7019

I think this can be done more efficiently. You copy the DataSet once with DataSet importedDataCopy = importedData.Copy(); and then you copy it again into a dictionary and then you delete the unnecessary data from the dictionary. I would rather just remove the unnecessary information in one pass. What about something like this:

private void mergeduplicate(DataSet importedData)
{
    Dictionary<String, DataRow> systems = new Dictionary<String, DataRow>();
    int i = 0;

    while (i < importedData.Tables[0].Rows.Count)
    {
        DataRow dr = importedData.Tables[0].Rows[i];
        String systemName = dr["Computer Name"].ToString();
        if (!systems.ContainsKey(systemName)) 
        {
            systems.Add(systemName, dr);
        }
        else
        {
            // Existing date is the date in the dictionary.
            DateTime existing = Validation.ConvertStringIntoDateTime(systems[systemName]["date"].ToString());

            // Candidate date is the date of the current DataRow.
            DateTime candidate = Validation.ConvertStringIntoDateTime(dr["date"].ToString());

            // If the candidate date is greater than the existing date then replace the existing DataRow
            // with the candidate DataRow and delete the existing DataRow from the table.
            if (DateTime.Compare(existing, candidate) < 0) 
            {
                importedData.Tables[0].Rows.Remove(systems[systemName]);
                systems[systemName] = dr;
            }
            else
            {
                importedData.Tables[0].Rows.Remove(dr);
            }
        }
        i++;
    }
}

Upvotes: 2

artm
artm

Reputation: 8584

You could try to use CopyToDataTable, like this:

importedData.Tables[0] = importedData.Tables[0].AsEnumerable()
       .GroupBy(r => new {CN = r["Computer Name"], Date = r["date"]})
       .Select(g => g.OrderBy(r => r["Date"]).(First())
       .CopyToDataTable();

Upvotes: 0

fubo
fubo

Reputation: 45947

maybe not the most efficient way but you said you appreciate any answers

List<DataRow> toDelete =  dt.Rows.Cast<DataRow>()
                                .GroupBy(s => s["Computer Name"])
                                .SelectMany(grp => grp.OrderBy(x => x["date"])
                                .Skip(1)).ToList();
toDelete.ForEach(x => dt.Rows.Remove(x));

Upvotes: 0

Related Questions