How to merge multiple lists based on a condition using LINQ

Question

I have two classes as follows:

public class SensorReading
{
    public DateTime DatetimeStamp { get; set; }
    public float Sound { get; set; }
    public float Temperature { get; set; }
    public float Humidity { get; set; }

    public SensorReading()
    {
    }
}

public class API_Obj
{
    public DateTime DateTimeStamp { get; set; }
    public float Value { get; set; }

    public API_Obj()
    {
    }
}

And I'm getting my sensor readings from a REST API; however, it separates the Sound, Temperature and Humidity readings into different API Endpoints. Hence I've made 3 separate API calls and stored each reading type into a list of Type List<API_Obj>. The code looks something this:

//set static objects
List<API_Obj> SoundReadings = GetReadings("sound");
List<API_Obj> TempReadings = GetReadings("temperature");
List<API_Obj> HumidReadings = GetReadings("humidity");

Now I would like to merge the 3 lists according to the DateTimeStamp class property. I've come up with this so far:

var AllReadings = SoundReadings.Union(TempReadings).Union(HumidReadings).GroupBy(obj => obj.DateTimeStamp)
    .Select(newObj => new SensorReading()
    {
        DateTimeStamp = newObj.Key,
        // Sound = ???
        // Temperature = ???
        // Humidity = ???
    }).ToList();

This would return 0 for Sound, Temperature and Humidity, however. How do I continue from here?

Answer 1

LINQ is not performant for such task, I would suggest the following function:

public static void MergeList<TItem, TKey, TResult>(IEnumerable<TItem> items, Dictionary<TKey, TResult> result,
    Func<TItem, TKey> keyFunc, Action<TItem, TResult> setterAction)
    where TResult : new()
{
    if (items == null) throw new ArgumentNullException(nameof(items));
    if (keyFunc == null) throw new ArgumentNullException(nameof(keyFunc));
    if (setterAction == null) throw new ArgumentNullException(nameof(setterAction));
    if (result == null) throw new ArgumentNullException(nameof(result));

    foreach (var item in items)
    {
        var key = keyFunc(item);
        if (!result.TryGetValue(key, out var value))
        {
            value = new TResult();
            result.Add(key, value);
        }

        setterAction(item, value);
    }
}

And usage:

var result = new Dictionary<DateTime, SensorReading>();
MergeList(SoundReadings, result, x => x.DatetimeStamp, (a, r) => r.Sound = a.Value);
MergeList(TempReadings,  result, x => x.DatetimeStamp, (a, r) => r.Temperature = a.Value);
MergeList(HumidReadings, result, x => x.DatetimeStamp, (a, r) => r.Humidity = a.Value);

Answer 2

You need to keep track of the type of value one way or another. Given your current type definitions, something like this should work:

var AllReadings = SoundReadings.Select(x => new { x.DatetimeStamp, x.Value, Type = "SoundReadings" })
    .Union(TempReadings.Select(x => new { x.DatetimeStamp, x.Value, Type = "TempReadings" })
    .Union(HumidReadings.Select(x => new { x.DatetimeStamp, x.Value, Type = "HumidReadings" })))
    .GroupBy(obj => obj.DatetimeStamp)
    .Select(newObj => new SensorReading()
    {
        DatetimeStamp = newObj.Key,
        Sound = newObj.FirstOrDefault(x => x.Type == "SoundReadings")?.Value,
        Temperature = newObj.FirstOrDefault(x => x.Type == "TempReadings")?.Value,
        Humidity = newObj.FirstOrDefault(x => x.Type == "HumidReadings")?.Value
    }).ToList();

Answer 3

As stated in the comments, you could modify your API_Obj class to hold a new string property called APIType and you can decorate the property with JsonIgnore attribute.

public class API_Obj
{
    public DateTime DatetimeStamp { get; set; }
    public float Value { get; set; }
    [JsonIgnore]
    public string APIType { get; set; }

    public API_Obj()
    {
    }
}

Then after you do your API Calls, use the List.ForEach to set the new property on each object of the list:

List<API_Obj> SoundReadings = GetReadings("sound");
List<API_Obj> TempReadings = GetReadings("temperature");
List<API_Obj> HumidReadings = GetReadings("humidity");

//set the APIType property on each list object
SoundReadings.ForEach(z => z.APIType = "sound");
TempReadings.ForEach(z => z.APIType = "temperature");
HumidReadings.ForEach(z => z.APIType = "humidity");

Now you can use these to determine which property to set in your Linq Query

Answer 4

What you came up with is nice, and of course you've got the newObj.Key there, but you didn't notice what newObj is: it is the group itself. A group = a collections of items from under that common key.

That's why the 'grouping' newObj implements not only the Key property, but also IEnumerable<API_Obj> interface, so you can inspect the items that fell to that group.

That is, if you've originally had:

SoundReadings:
    - date:0011, value: aa
    - date:0022, value: bb
    - date:0033, value: cc
TempReadings:
    - date:0011, value: dd
    - date:0055, value: ee
HumidReadings:
    - date:0055, value: ff
    - date:0066, value: gg

You will end up with these groups:

11:
    - date:0011, value: aa (sound)
    - date:0011, value: dd (temp)
22:
    - date:0022, value: bb (sound)
33:
    - date:0033, value: cc (sound)
44:
    - date:0055, value: ee (temp)
    - date:0055, value: ff (humid)
66:
    - date:0066, value: gg (humid)

Now, in your .Select() at the end, you have to inspect not only the Key with the date, but also the whole set of items held in newObj and find an item for Sound, and use its value for Sound, find an item for Temp and use its value for Temp, and so on.

For example, just to give you a general idea:

.Select(newObj => new SensorReading()
{
    DateTimeStamp = newObj.Key,
    Sound = newObj.Where(.....).FirstOrDefault()?.Value,
    ...
}).ToList();

Mind three things though:

• grouping doesn't guarantee that the item will be there. If there are some holes in the data set, you may find groups that don't have any Sound at all. Or you may find groups that have multiple Sounds for given date. All depends on the input data
• grouping won't remember for you that a Sound is a Sound. If all your API_Obj are identical and only contain different dates and different Values, you have to have a way to determine where did that value come from in the first place. Note how I added "(sound)" to the list above. You may need something like this in your data model as well, or else you may be not able to differentiate a value from Sound source from a value from Temp source.
• keying/grouping by date is tricky. Be extra careful here, because Time likes to change. If it's not only dates, but whole timestamps, with seconds, milliseconds, and so on, you may suddenly notice that all groups have only 1 element, because there were no timestamps from Sounds that perfecly matched timestamps from Temps. They might have been similar even up to seconds, but if milliseconds were different, then the keys were different. You get the idea already probably.