Adding blank rows for every skipped value in a DataTable

Question

So I have a DataTable that returns results within a month to month range when available. However, when I format the return, I need to still show skipped months as blanks.

I have a working solution, but it's a little verbose and I think it can be done cleaner.

Here's a setup to the problem

Dim startMonth As Date = #5/1/2011#
Dim endMonth As Date = #6/1/2012#
Dim dr As DataRow

Dim dtData As New DataTable
With dtData.Columns
    .Add("MonthDate", GetType(Date))
    .Add("OtherInfo", GetType(String))
    'other columns...
End With

With dtData.Rows
    .Add(#6/1/2011#, "Info")
    .Add(#7/1/2011#, "Info")
    .Add(#7/1/2011#, "Info")
    .Add(#1/1/2012#, "Info")
End With

This part will loop through the date ranges and generate a DataTable with one blank row per possible date

'get all dates
Dim dtAllTimes As DataTable = dtData.Clone()

Do While startMonth <= endMonth
    dr = dtAllTimes.NewRow()
    dr("MonthDate") = startMonth
    dtAllTimes.Rows.Add(dr)
    startMonth = startMonth.AddMonths(1)
Loop

This part will create a blank DataTable with one date per found value

'get used dates
Dim dtUsedTimes As DataTable = dtData.Clone()

For Each usedDate As Date In 
        From row As DataRow In dtData.AsEnumerable Select CDate(row("MonthDate")) Distinct
    dr = dtUsedTimes.NewRow()
    dr("MonthDate") = usedDate
    dtUsedTimes.Rows.Add(dr)
Next

Finally I combine those two tables to get the unused dates and add them onto the original data

'subtract used dates from all dates
Dim unusedDates As IEnumerable(Of DataRow)
unusedDates = dtAllTimes.AsEnumerable.Except(dtUsedTimes.AsEnumerable(), DataRowComparer.Default)

'add subtraction back into all
dtData = dtData.AsEnumerable.Union(unusedDates).CopyToDataTable()

Any suggestions would be great.

Update...

Implementing my own custom comparison class helps to shave off a couple lines of code and processing time by not having to compare every column in the entire row against every other column and also helps skip the step of finding used rows since that was only done to put them into a DataTable that could be used with the Default DataRowComparer.

Here's the custom DataRowComparer

Public Class DataRowDateComparer : Implements IEqualityComparer(Of DataRow)

    Public Function Equals(x As DataRow, y As DataRow) As Boolean _
            Implements IEqualityComparer(Of DataRow).Equals
        Return x("MonthDate") = y("MonthDate")
    End Function

    Public Function GetHashCode(obj As DataRow) As Integer _
            Implements IEqualityComparer(Of DataRow).GetHashCode
        Return obj.ToString.GetHashCode
    End Function

End Class

Here's the code after creating a DataTable with every date

'only add rows that aren't already in datatable
dtData = dtData.AsEnumerable _
               .Union(dtAllTimes.AsEnumerable, New DataRowDateComparer) _
               .CopyToDataTable()

Also, sorting by MonthDate in a DataView can help view the results more easily

Dim dv As New DataView(dtData)
dv.Sort = "MonthDate"

Answer 1

You can have fewer lines of code by creating a the list of missing dates in a single Linq query.

The idea is:

• Get the list of all dates in the given time period)
• Left-Outer-Join this list with the list of given dates
• Add the missing dates to the original DataTable

---

Dim missingDates = From a in Enumerable.Range(0, Int32.Maxvalue)
                   Let c = startMonth.AddMonths(a)
                   Take While c <= endMonth 'create list of all dates'
                   Group Join d in dtData On c Equals d("MonthDate") Into g = Group
                   Where Not g.Any() 'take only the missing ones'
                   Select c

For Each d in missingDates 'and add them to the DataTable'
    dtData.Rows.Add(d, Nothing)
Next

---

Using Union is also fine IMHO, but then the GetHashCode method of your comparer should return the hash code of the value you actually want to compare:

Public Function GetHashCode(obj As DataRow) As Integer _
        Implements IEqualityComparer(Of DataRow).GetHashCode
    Return obj("MonthDate").GetHashCode
End Function