C# remove datatable duplicates based on field and condition

Question

Is it possible to remove duplicates from a datatable based on a specific field and a condition

Like if I have the following records:

name: Ali

appointment_type: dental

appointment_date: 8/5/2017 08:00:00

name: ali

appointment_type: dental

appointment_date: 8/5/2017 16:00:00

from the above example, the patient Ali has two appointments, I want to remove the later appointment(s) (which is on 8/5/2017 16:00:00)

in other words, remove all appointment of patient "Ali" and keep the earliest one only

is it possible to do it in LINQ?

First filter them in a Where and then apply RemoveRange as described here: http://stackoverflow.com/questions/14746783/remove-all-but-the-first-item-in-a-list — Alexandru Pupsa, May 08 '17 at 07:45

score 1 · Accepted Answer · answered May 08 '17 at 07:49

You might want to GroupBy the items and then OrderBy each group based on AppointmentDate, take only the First (earliest) from each group. The result will be the earliest appointment only:

List<Patient> patients = new List<Patient>(); //change this with your actual list/IEnumerable

IEnumerable<Patient> earliestAppointmentRecorded = patients.GroupBy(x => x.Name.ToLower().Trim())
   .Select(x => x.OrderBy(y => y.AppointmentDate).First());

Assuming the class is like below:

public class Patient {
  public string Name { get; set; }
  public string AppointmentType { get; set; }
  public DateTime AppointmentDate { get; set; }

};

And, say, you want to replace the earlier records with the ones of the earliestAppointmentRecorded, you could simply do:

patients = earliestAppointmentRecorded.ToList();

score 0 · Answer 2 · answered May 08 '17 at 07:53

Try following :

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("appointment_type", typeof(string));
            dt.Columns.Add("appointment_date", typeof(DateTime));

            dt.Rows.Add(new object[] { "Ali", "dental", DateTime.Parse("8/5/2017 08:00:00")});
            dt.Rows.Add(new object[] { "Ali", "dental", DateTime.Parse("8/5/2017 16:00:00")});

            var groups = dt.AsEnumerable().GroupBy(x => new { name = x.Field<string>("name"), type = x.Field<string>("appointment_type") }).ToList();

            dt = groups.Select(x => x.OrderBy(y => y.Field<DateTime>("appointment_date")).LastOrDefault()).CopyToDataTable();

        }

C# remove datatable duplicates based on field and condition

2 Answers2