Amend list using linq via second list

Question

I have 2 lists. The first is the main list containing a bunch of objects, each having timestamp (Datetime), type (int), value (double) and flag (int). The second list has type (int) and description (string).

My problem is that I need the first list to have all types at each timestamp. In the case where there is no such object matching (timestamp,type), I need to insert a new object with (timestamp,type, null value and null flags).

How do I do that using Linq?

Example objects:

var date = DateTime.UtcNow;
var points = new List<Point> {
    new Point { ts_utc = date            , type = 300, value = 25     , flags = 0 },
    new Point { ts_utc = date            , type = 400, value = 250    , flags = 0 },
    new Point { ts_utc = date            , type = 500, value = 2500   , flags = 0 },
    new Point { ts_utc = date.AddDays(1) , type = 300, value = 26     , flags = 0 },
    //new Point { ts_utc = date.AddDays(1) , type = 400, value = 260, flags = 0 },
    new Point { ts_utc = date.AddDays(1) , type = 500, value = 2600   , flags = 0 },
    new Point { ts_utc = date.AddDays(2) , type = 300, value = 27     , flags = 0 },
    new Point { ts_utc = date.AddDays(2) , type = 400, value = 270    , flags = 0 },
    new Point { ts_utc = date.AddDays(2) , type = 500, value = 2700   , flags = 0 }
};
var desc = new List<Description> {
    new Description{ type = 300, description = "300" },
    new Description{ type = 400, description = "400" },
    new Description{ type = 500, description = "500" },
};

 var q = from p in points
         join d in desc on p.type equals d.type into joined
         from subd in joined.DefaultIfEmpty(...)
         ...

The query is incomplete. In the list above, I have commented one Point as an example of a missing value for that (timestamp,type) combination. In that spot, I'd like to have a default object inserted with the timestamp from an existing type, but with the missing type inserted. Value and flags should be null. I guess a group by might come in handy?

Answer 1

I found this to fix my list without having to resort to multiple queries:

var w = from p in points
        group p by p.ts_utc into g
        from d in desc
        select points.Where(r => r.ts_utc == g.Key && r.type == d.type).DefaultIfEmpty(new Point
        {
            ts_utc = g.Key,
            type = d.type
        }).FirstOrDefault();

I thinks it doesn't get any shorter than this. Both (at this time) other answers helped me to arrive at this solution. It doesn't create unnecessary new points, is 1 linq query, and I think (using 1 linq query) that it couldn't be much more efficient.

Answer 2

Correct me if I'm wrong but you want to 'fill in the gaps' for list 1 - such that it contains entries with every existing type for every existing date?

If so, I would just get all distinct items to iterate through and create a nested foreach loop...

List<DateTime> allExistingDates = points.Select(o => o.ts_utc).Distinct().ToList();
List<int> allExistingTypes = points.Select(o => o.type).Concat(desc.Select(o => o.type)).Distinct().ToList();

foreach (DateTime d in allExistingDates)
    foreach (int t in allExistingTypes)
        if (points.Any(o => o.ts_utc == d && o.type == t) == false)
            points.Add(new Point { ts_utc = d, type = t});

Answer 3

If I understand you correctly you want the set of time stamps and type combinations that do not exist in the first collection based on that collection having one entry for a time stamp, but not all the types in the second collection.

var missing = points.GroupBy(p => p.ts_utc)
                    .SelectMany(g => desc.Where(d => g.All(p => p.type != d.type))
                                          .Select(d => new 
                                                       { 
                                                           ts = g.Key, 
                                                           type = d.type 
                                                       }));

This will first group the points by the time stamp then for each grouping it will filter from the description list any descriptions that have a type that exists in the group, leaving only the descriptions with a type that doesn't exist for this time stamp. Then it selects the time stamp from the group key and the "missing" type. finally the SelectMany will flatten the results.

You can then iterate over the results and add entries into the points collection.

foreach(var m in missing)
    points.Add(new Point { ts_utc = m.ts, type = m.type });