Remove matched Rows from two Datatable

Question

Here I have two DataTables:

• dtTotalDateRangeExcludeSundays
• dtAbsentsLeavesHolidaysWorks

Each has 10k+ values, and I need to delete matched rows.

foreach (DataRow rw in dtTotalDateRangeExcludeSundays.Select())
 {
 DateTime dateFromRange=Convert.ToDateTime(rw[0].ToString());
 string strPrnt = dateFromRange.ToShortDateString();
 foreach (DataRow row in dtAbsentsLeavesHolidaysWorks.Select())
 {
 DateTime getDate = Convert.ToDateTime(row[0].ToString());
 string strchild = getDate.ToShortDateString();
 if (strPrnt == strchild)
 {
 rw.Delete();
 }
 }
 }

I want to know if any better alternative suggestion, because the foreach loop takes time when bulk data is there. I am not good with Linq, so I want to know if any Linq tricks work.

Answer 1

Assuming that you load the data into your DataTable instances from database, it's better to issue a direct DELETE FROM ... query over database rather than iterate on a client.

As a side note - it is an awful practice to compare dates by comparing strings. If your table has a DateTime column it's much better to cast the value to DateTime and compare typed dates like that:

DateTime dateFromRange = ((DateTime)rw[0]).Date;
....
 DateTime getDate = ((DateTime)row[0]).Date;
 if (dateFromRange == getDate)
....

Answer 2

First and foremost:

• Don't Select(). It's useless.
  foreach (var c in collection) is equivalent to:
  foreach (var c in collection.Select()), which is also the same as:
  foreach (var c in collection.Select(a => a).Select().Select().Select(b => b)).

• Don't use column indexes. Column names are less error prone.

• Don't ToString a date to parse it immediately after in order to stringify it again. I imagine that this is what you do with the first row.

• Use uniform style. For example, putting spaces before and after '=' in some places but not in others doesn't increase readability. StyleCop may help.

• Don't use Hungarian notation (you can read the article by Joel Spolsky among others to know why).

• Use meaningless names for variables. There is no need to use cryptic names like prnt: they don't help understanding the code, and making a longer, more explicit name is not something hard to do. The same comes from rw and row: in general, rw is an abbreviation for read/write; if you use it for "row", what does row mean in your code? strchild is a terrible name too.

Using suggestions below, this gives:

foreach (var allDaysRecord in this.totalDateRangeExcludeSundays)
{
 var allDaysRecordDay = ((DateTime)allDaysRecord["Date"]).Date;
 foreach (var absence in this.absentsLeavesHolidaysWorks)
 {
 var absenceDay = ((DateTime)absence["Date"]).Date;
 if (allDaysRecordDay == absenceDay)
 {
 allDaysRecord.Delete();
 }
 }
}

You can, if you want, LINQify that:

foreach (var allDaysRecord in this.totalDateRangeExcludeSundays)
{
 var allDaysRecordDay = ((DateTime)allDaysRecord["Date"]).Date;
 var hasAbsence = this.absentsLeavesHolidaysWorks
 .Select(c => ((DateTime)c["Date"]).Date)
 .Contains(allDaysRecordDay);
 if (hasAbsence)
 {
 allDaysRecord.Delete();
 }
}

You can go further:

var matches = from allDaysRecord in this.totalDateRangeExcludeSundays
 let allDaysRecordDay = ((DateTime)allDaysRecord["Date"]).Date
 where this.absentsLeavesHolidaysWorks
 .Select(c => ((DateTime)c["Date"]).Date)
 .Contains(allDaysRecordDay)
 select allDaysRecord;
foreach (var m in matches.ToList())
{
 m.Delete();
}

The code is cleaner, but you won't gain in terms of performance. If performance matters in this case, why aren't you doing the same thing as a single SQL query?