Linq produce a list of missing records for a given date range

Question

Say I have a list of the following class:

public class Holding
{
  public string HoldingId{ get; set; }      
  public DateTime date { get; set; }
}

There needs to be a holding for each day in a given date range. I need to be able to produce a list of holdings that are missing for the range.

So say I have the following data that I need to check over the range 1 June 2010 - 5 June 2010:

HoldingId Date
1         01-06-2010
1         02-06-2010
1         04-06-2010
2         02-06-2010
2         03-06-2010
2         05-06-2010
3         03-06-2010

For this set of data the missing holdings would be:

HoldingId Date
1         03-06-2010
1         05-06-2010   
2         01-06-2010
2         04-06-2010    
3         01-06-2010
3         02-06-2010
3         04-06-2010
3         05-06-2010

I have produced the list range of dates using the answer to the following question: Find missing dates for a given range.

I can't quite get my head around how to go forward from here...I assume I'll need to group by HoldingId to produce an array of dates and then do range.Except(holdings.dates) or something to that effect.

Does anyone have a nice solution to this problem using Linq?

What is your question? As far as I can see you `need to be able to produce a list of holdings that are missing for the range` and you already did that. — david.s, Aug 01 '12 at 08:53

score 2 · Accepted Answer · answered Aug 01 '12 at 08:57

you're quite right in howit should be done; here is what I got;

List<Holding> holdings = new List<Holding>();
holdings.Add(new Holding(){ date=Convert.ToDateTime("01-06-2010"), HoldingId = "1" });
holdings.Add(new Holding(){ date=Convert.ToDateTime("02-06-2010"), HoldingId = "1" });
holdings.Add(new Holding(){ date=Convert.ToDateTime("04-06-2010"), HoldingId = "1" });
holdings.Add(new Holding(){ date=Convert.ToDateTime("02-06-2010"), HoldingId = "2" });
holdings.Add(new Holding(){ date=Convert.ToDateTime("03-06-2010"), HoldingId = "2" });
holdings.Add(new Holding(){ date=Convert.ToDateTime("05-06-2010"), HoldingId = "2" });
holdings.Add(new Holding(){ date=Convert.ToDateTime("03-06-2010"), HoldingId = "3" });

List<DateTime> dateRange = new List<DateTime>();
dateRange.Add(Convert.ToDateTime("01-06-2010"));
dateRange.Add(Convert.ToDateTime("02-06-2010"));
dateRange.Add(Convert.ToDateTime("03-06-2010"));
dateRange.Add(Convert.ToDateTime("04-06-2010"));
dateRange.Add(Convert.ToDateTime("05-06-2010"));

Dictionary<string, List<DateTime>> missingHoldings = new Dictionary<string, List<DateTime>>();

foreach(var holdGrp in  holdings.GroupBy (h => h.HoldingId))
{
    var missingDates = dateRange.Except(holdGrp.Select(h => h.date)).ToList();
    missingHoldings.Add(holdGrp.Key, missingDates);
}

Ah that's how I envisioned it but I just couldn't get the implementation right. Thanks saj :) — woggles, Aug 01 '12 at 09:08

score 1 · Answer 2 · answered Aug 01 '12 at 09:20

An alternative approach:

public static List<Holding> MissingHoldings(List<Holding> existingHoldings, DateTime startDate, DateTime endDate)
{
    var missingHoldings = new List<Holding>();
    var holdingIds = existingHoldings.Select(h => h.HoldingId).Distinct().ToList();
    var dates = new List<DateTime>();
    for (var current = startDate.Date; current <= endDate.Date; current = current.AddDays(1))
    {
        dates.Add(current);
    }

    foreach (var holdingId in holdingIds)
    {
        missingHoldings
            .AddRange(
                dates.Where(date => !existingHoldings.Any(h => h.HoldingId == holdingId && h.date == date))
                .Select(date => new Holding {HoldingId = holdingId, date = date}));
    }
    return missingHoldings;
}

Julien Ch. · Answer 3 · 2012-08-01T12:37:49.203

A pure Linq Query inspired by saj's answer :

var missingHoldingsList =
 from h in holdings.GroupBy( h => h.HoldingId )
 from d in dateRange.Except( h.Select(x => x.date) ) 
 orderby h.Key, d
 select new Holding { date = d , HoldingId = h.Key };

and a loop-less version of saj's answer:

var missingHoldingsDict = (
  from h in holdings.GroupBy(h => h.HoldingId)
  select new
  {
    key = h.Key,
    holdings = 
      from d in dateRange.Except(h.Select(x => x.date))
      select new Holding { date = d, HoldingId = h.Key }
  }
).ToDictionary(
  h => h.key,
  h => h.holdings.ToList()
);

Linq produce a list of missing records for a given date range

3 Answers3