JOIN two lists on a Key, and sum up totals with CROSS?

Question

I have two List<AccountBalance>

And AccountBalance has an ID GUID and a Balance DECIMAL value.

One list has all credits, and one list has all debits. These have been summed, so there will be unique account IDs in the lists (but will repeat in one another. i.e. An account will appear ONCE in creditBalances, but will probably be found in both lists.

var creditBalances = new List<AccountBalance>();

var debiBalances = new List<AccountBalance>();

What I need to do is return one list, with the total for each account.

So, I need to kind of do a join, based on the ID of the two tables... And for each account, add amount in the credit, and subtract the amount in the debit.

Not all accounts will have a credit, and not all will have a debit, but my result set needs to include all the accounts.

What I am trying:

 var result = creditBalances
                .Join(debitBalances, a => a.ExternalId, b => b.ExternalId, (a, b) => new AccountBalance { Id = a.ExternalId, Balance = a.amount - b.amount })
                .ToList();

But this seems to do an INNER join, and only give me results where 'creditBalances' has a value. I need to basically do a CROSS JOIN?

Credit:

Acc: 1, amount 10
Acc: 2, amount 5

Debit:

Acc 2: amount 2
Acc 3: amount 5

Should result in:

Acc 1: 10
Acc 2: 3
Acc 3: -5

Does this answer your question? [LINQ - Full Outer Join](https://stackoverflow.com/questions/5489987/linq-full-outer-join) — peeyush singh, Nov 18 '19 at 01:52

Kei · Accepted Answer · 2019-11-18T01:58:41.940

You could do a full outer join.

For example, take a look at the FullOuterJoin implementation from this answer

Adapting to your specific case:

public class AccountBalance
{
    public decimal amount { get; set; }
    public decimal ExternalId { get; set; }
}

// From https://stackoverflow.com/a/13503860/11981207
public static IEnumerable<TResult> FullOuterJoin<TA, TB, TKey, TResult>(
     this IEnumerable<TA> a,
     IEnumerable<TB> b,
     Func<TA, TKey> selectKeyA,
     Func<TB, TKey> selectKeyB,
     Func<TA, TB, TKey, TResult> projection,
     TA defaultA = default(TA),
     TB defaultB = default(TB),
     IEqualityComparer<TKey> cmp = null)
{
    cmp = cmp ?? EqualityComparer<TKey>.Default;
    var alookup = a.ToLookup(selectKeyA, cmp);
    var blookup = b.ToLookup(selectKeyB, cmp);

    var keys = new HashSet<TKey>(alookup.Select(p => p.Key), cmp);
    keys.UnionWith(blookup.Select(p => p.Key));

    var join = from key in keys
               from xa in alookup[key].DefaultIfEmpty(defaultA)
               from xb in blookup[key].DefaultIfEmpty(defaultB)
               select projection(xa, xb, key);

    return join;
}

// ---

var creditBalances = new List<AccountBalance> {
    new AccountBalance { ExternalId = 1, amount = 10 },
    new AccountBalance { ExternalId = 2, amount = 5 },
};

var debitBalances = new List<AccountBalance> {
    new AccountBalance {ExternalId = 2, amount = 2 },
    new AccountBalance { ExternalId = 3, amount = 5 },
};

var result = creditBalances.FullOuterJoin(debitBalances, 
        credit => credit.ExternalId,
        debit => debit.ExternalId,
        (credit, debit, key) => 
            new AccountBalance() { 
                 ExternalId = key, 
                 amount= (credit?.amount ?? 0) - (debit?.amount ?? 0)
            }
);

Thanks @Kei ! That solved my issues. Now to try work out what that extension method is doing! Thanks for the help! — Craig, Nov 20 '19 at 04:11
As I understand it, first the method creates a [lookup](https://learn.microsoft.com/en-us/dotnet/api/system.linq.lookup-2?view=netframework-4.8) based on the join key. Then it creates a HashSet with keys on both sides (keys in collection A unioned with keys in collection B). Finally, it loops through each key. For each key, it selects the key, and one corresponding item from each collection (or the default value if none; for reference types the default value is null). — Kei, Nov 20 '19 at 05:01
If there are multiple items that relate to the key, the query will add extra rows (since it's doing a cross join between `key`, `xa`, and `xb` where xa and xb represent all matching items in collections A and B). I just realized that this might not be what you want if you could potentially have multiple balances with the same type (credit/debit) and ExternalId. For example, if you had the Debit balance `Acc 3: amount 5` in your collection twice, `Acc 3: -5` would be outputted twice as well. — Kei, Nov 20 '19 at 05:02
If it's possible that there may be duplicate `ExternalId`s, use the `FullOuterGroupJoin` in the linked answer, and change `amount= (credit?.amount ?? 0) - (debit?.amount ?? 0)` in my answer to `amount = (credit?.Sum(x => x.amount) ?? 0) - (debit?.Sum(x => x.amount) ?? 0)` — Kei, Nov 20 '19 at 05:02

JOIN two lists on a Key, and sum up totals with CROSS?

1 Answers1