Merging 2 rows from 2 different lists with same UserId?

Question

If I have 2 different lists,

list1 contains:

UserId: fcec4d6c-c971-4690-90da-be8411dcf251 Col1: 32 Col2: 2 Col3: 0 Col4: 0

UserId: 783ffaa5-03ef-4883-80d2-0500ef489832 Col1: 50 Col2: 4 Col3: 0 Col4: 0

and list2 contains:

UserId: fcec4d6c-c971-4690-90da-be8411dcf251 Col1: 0, Col2: 0, Col3: 45, Col4: 50

If I want to merge these to lists, so that the result would end up being a UserDto list which contains: UserId: fcec4d6c-c971-4690-90da-be8411dcf251 Col1: 32 Col2: 2 Col3: 34 Col4: 50 UserId: 783ffaa5-03ef-4883-80d2-0500ef489832 Col1: 50 Col2: 4 Col3: 0 Col4: 0

How would one go about doing that?

UserDto just contains something like

 [JsonSchema(JsonObjectType.String, Format = "uuid")]
        public Guid UserId { get; set; }
        public int Col1 { get; set; }
        public int Col2 { get; set; }
        public int Col3 { get; set; }
        public int Col4 { get; set; }

I've tried

list1.AddRange(list2);
list1.GroupBy(e => e.UserId, (key, g) => new { User = key, Columns = g.ToList() }).ToList();
return list1;

list1 returns 2 UserIds, where fcec4d6c-c971-4690-90da-be8411dcf251 now has a Columns list that contains 2 columns, one with col1 + col2 filled and col3 + col4 filled. Please note that these lists will contain a lot of these instances.

Edit 1: I should've made it more clear that I want the sum of these instances in the end. I have now received a proper solution.

Answer 1

Select proper aggregate function:

var result = list1.Concat(list2)
   .GroupBy(e => e.UserId)
   .Select(g => new User 
    {
        UserId = g.Key,
        Col1 = g.Max(x => x.Col1),
        Col2 = g.Max(x => x.Col2),
        Col3 = g.Max(x => x.Col3),
        Col4 = g.Max(x => x.Col4),
    })
   .ToList();

Answer 2

You didn't specify why you didn't change Col1 and Col2, but changed Col3 and Col4. Do you always want to replace these two columns? Or do you only want to replace them if they have value zero? Or maybe you want to replace all columns with a zero value?

Anyway, first you need to get every list1Element with all zero or more list2Elements that have the same UserId.

Whenever you want to fetch "items with their sub-items", like Schools with their Students, Customers with their Orders, or list1Elements with their list2Elements, consider to use one of the overloads of Enumerable.GroupJoin.

As parameter keySelector use the properties that makes it "its list2Element"

IEnumerable<User> list1 = ...
IEnumerable<User> list2 = ...

var result = list1.GroupJoin(list2
    list1User => list1User.UserId,   // from every user in list1 take the UserId
    list2User => list2User.UserId,   // from every user in list2 take the UserId

    // parameter resultSelector: from every user in list1, with the zero or more
    // users from list2 that have the same UserId, make one new
    (list1User, list2UsersWithSameId) => new
    {
        // decide what you want. 
        // Replace all 0 properties with the corresponding list2 column?
        Col3 = (list1User.Col3 != 0) ? list1User.Col3 :
               list2UsersWithSameId.Select(list2User => list2User.Col3)
                   .FirstOrDefault(),

So if listUser.Col3 not zero, use this Col3 value,
otherwise, from the zero or more list2UsersWithSameId take the Col3 and use the first or default. If there is a list2 user with same Id, you have got its Col3, if not, you get the value zero.

So the value is only replaced if Col3 is zero, and there is at least one list2 with the same Id. If there is none, Col3 remains zero.

Do the same for the other columns that you want to replace.

TODO: you didn't specify that UserId in list2 is unique. If not, it can be that a list item has more than one corresponding list2 item with the same userId. You have to decide which value to use: the first one? the largest one?