Merge two lists and apply custom function while merging

Question

So I have two lists which has similar properties for each item in the list. Each item is of the format -

public class ZoneOccupancy
{
    public virtual string ZoneId { get; set; }
    public virtual int CurrentOccupancy { get; set; }
}


ZoneId | CurrentOccupancy

One list would be like -

ZoneId | CurrentOccupancy 

110      2
111      1
115      3

Another list would be like -

ZoneId | CurrentOccupancy 

110      1
111      1
116      3

After merging what I want is something like -

ZoneId | CurrentOccupancy 

110      3
111      2
115      3
116      3

So i want the duplicate items from the list to merge into one but in the process, the count to be added.

Edit:

list.Union(ParkingTagTicketQueryResult, ZoneComparer.Instance)
                         .GroupBy(z => z.ZoneId)
                         .Select(z => new ZoneOccupancy
                                        {
                                            ZoneId = z.First().ZoneId,
                                            CurrentOccupancy = z.Sum(row => row.CurrentOccupancy)


public class ZoneComparer : IEqualityComparer<ZoneOccupancy>
{
     public static readonly ZoneComparer Instance = new ZoneComparer();

     // We don't need any more instances
     private ZoneComparer() { }

     public int GetHashCode(ZoneOccupancy z)
     {
          return z.ZoneId.GetHashCode();
     }

     public bool Equals(ZoneOccupancy z1, ZoneOccupancy z2)
     {
          if (Object.ReferenceEquals(z1, z2))
          {
              return true;
          }

          if (Object.ReferenceEquals(z1, null) ||
              Object.ReferenceEquals(z2, null))
          {
              return false;
          }

          return z1.ZoneId.Equals(z2.ZoneId);
      }

}

What is that format? Is this a list of classes that have a `Zone` and `Count` property? It would be more helpful if you presented a short sample that actually contained some data... — Rufus L, Jul 07 '17 at 00:49
Something similar has been asked here: https://stackoverflow.com/questions/13901234/merging-2-lists-and-sum-several-properties-using-linq — Sujith, Jul 07 '17 at 00:53
Yes, this is a List. I updated the model for each item in the list. — Dibzmania, Jul 07 '17 at 00:54

John Wu · Accepted Answer · 2017-07-07T01:10:50.700

3

First let's define a class to hold our data:

class Datum
{
    public string Zone { get; set; }
    public int Count { get; set; }
}

And populate the two lists:

        var list1 = new List<Datum>();
        list1.Add(new Datum { Zone = "110", Count = 2 });
        list1.Add(new Datum { Zone = "111", Count = 1 });
        list1.Add(new Datum { Zone = "115", Count = 3 });

        var list2 = new List<Datum>();
        list1.Add(new Datum { Zone = "110", Count = 1 });
        list1.Add(new Datum { Zone = "111", Count = 1 });
        list1.Add(new Datum { Zone = "116", Count = 3 });

To join the two lists, we need to concatenate them:

        var list3 = list1.Concat(list2);

Then we need to group the rows by Zone:

        var list3 = list1.Concat(list2)
                         .GroupBy(a => a.Zone);

This will return a list of IGrouping instances, which we can use to get the Zone (the unique key) and apply aggregate functions on the remaining fields. In our case we want the Sum of the counts.

        var list3 = list1.Concat(list2);
                         .GroupBy(a => a.Zone)
                         .Select(group => new Datum 
                                          {
                                              Zone = group.Key, 
                                              Count = group.Sum(row => row.Count) 
                                          }
                                );

When we dump list3 we'll see it contains the results you were looking for:

        foreach (var item in list3)
        {
            Console.WriteLine(string.Format("Zone: {0} Count: {1}", item.Zone, item.Count));
        }

Zone: 110 Count: 3

Zone: 111 Count: 2

Zone: 115 Count: 3

Zone: 116 Count: 3

I did not see anything in your question that called for a "custom function" but it would be easy to add here in any of the LINQ statements.

edited Jul 07 '17 at 01:10

answered Jul 07 '17 at 01:00

John Wu

50,556
8
44
80

I haven't run your code yet but isn't UNION supposed to do the grouping of similar entries from the 2 lists already ? So Why do we need a GROUPBY transform again to be applied ? – Dibzmania Jul 07 '17 at 01:05
@Dibzmania No, Union will not do grouping (which is done here on a specific property of an item), but it *will* remove duplicate items. However the removal would be an object.Equals comparison, I believe, so it should be safe in this case. – Rufus L Jul 07 '17 at 01:08
To quote UNION doc `This method excludes duplicates from the return set. This is different behavior to the Concat method, which returns all the elements in the input sequences including duplicates.` – Nkosi Jul 07 '17 at 01:09
1

Yeah just remembered that. It's like UNION versus UNION ALL in SQL. I've edited my answer to use `Concat` instead. – John Wu Jul 07 '17 at 01:11
Well i passed a IEqualityComparer instance to the UNION method, which compares the same property in order to avoid duplicates. I will put that piece of code in my question. My only doubt is that the UNION might interfere with the grouping. Can you have a look at the edit ? – Dibzmania Jul 07 '17 at 01:14
@Dibz: You should not use `Union`, because there is never a case where you want duplicates to be outright removed-- you'd lose data. You want duplicates to be *merged* instead. So concatenate the lists using `Concat` then merge the records using `GroupBy`, as shown in my example. – John Wu Jul 07 '17 at 17:55
@john yeah that sounds more like it. Thanks. – Dibzmania Jul 07 '17 at 21:03
Tried with Concat. Works. – Dibzmania Jul 10 '17 at 01:35

Rufus L · Answer 2 · 2017-07-07T01:21:44.350

One way to do it would be to simply start with one of the lists, and then, for each item in the other list either add the item if there's no match, or update the existing item if there is a match:

var list1 = new List<ZoneOccupancy>
{
    new ZoneOccupancy {ZoneId = 110, CurrentOccupancy = 2},
    new ZoneOccupancy {ZoneId = 111, CurrentOccupancy = 1},
    new ZoneOccupancy {ZoneId = 115, CurrentOccupancy = 3},
};

var list2 = new List<ZoneOccupancy>
{
    new ZoneOccupancy {ZoneId = 110, CurrentOccupancy = 1},
    new ZoneOccupancy {ZoneId = 111, CurrentOccupancy = 1},
    new ZoneOccupancy {ZoneId = 116, CurrentOccupancy = 3},
};

var mergedList = list1.ToList();

foreach (var item in list2)
{
    var match = mergedList.FirstOrDefault(i => i.ZoneId == item.ZoneId);
    if (match == null)
    {
        mergedList.Add(item);
    }
    else
    {
        match.CurrentOccupancy += item.CurrentOccupancy;
    }
}

// Output results
mergedList.ForEach(i => 
    Console.WriteLine($"Zone Id: {i.ZoneId}, Current Occupancy: {i.CurrentOccupancy}"));

Output

Merge two lists and apply custom function while merging

2 Answers2