c# Merge List with another List based on key

Question

My main object is a list of customers. Because of the way I have to obtain the data I have two lists I need to merge as shown below. The balances list includes the balances for all customers. Basically I want to add the balances associated with a particular customer to the customers list. I'm trying to use Linq to merge the two based on their common key of CustomerNumber, but haven't been able to make it work.

List<Customer> customers
List<Balance> balances

public class Customer
{
     int CustomerNumber {get;set;}
     List<Balance> Balances{get;set;}
}

public class Balance
{
     int CustomerNumber {get;set;}
     decimal Amount {get;set;}
}

So lets save I have this list of customers

List<Customer> customers = new List<Customer>{
new Customer {CustomerNumber = 1},
new Customer {CustomerNumber = 2},
new Customer {CustomerNumber = 3}
};

And this list of balances

List<Balance> balances = new List<Balance>{
new Balance {Amount = 10, CustomerNumber = 1},
new Balance {Amount = 20, CustomerNumber = 1},
new Balance {Amount = 30, CustomerNumber = 2},
new Balance {Amount = 80, CustomerNumber = 2},
new Balance {Amount = 100, CustomerNumber = 2},
new Balance {Amount = 50, CustomerNumber = 3},
};

In the end I would have

Customer 1 would have a list with the 10 and 20 amounts
Customer 2 would have a list with the 30, 80, and 100 amounts
Customer 3 would have a list with the 50 amount.

The current solution looks like:

foreach (var customer in customers)
{
    var matchingBalances = balances
                     .Where(x => x.CustomerNumber == customer.CustomerNumber);
    customer.Balances.AddRange(matchingBalances);
}

Update: Here's what I ended up using.

var merge2 = customers.GroupJoin(balances,
        c => c.CustomerNumber,
        b => b.CustomerNumber,
        (c, b) =>
        {                
            c.Balances = b.ToList()
            return c;
        });

Well that sounds like a join - so what have you tried - and what are you expecting the result to be? — Jon Skeet
– Jon Skeet, Commented May 22, 2014 at 19:20
I see you commented on @KevinOwens answer that you are using that solution, which is in my estimation a fine way to handle that. If you are looking to optimize an existing function, you should put what you are already using and what what ways specifically you are looking to optimize, faster processing, lower memory footprint etc. — Mabdullah
– Mabdullah, Commented May 22, 2014 at 19:32
I posted a GroupJoin solution below too, this might also help. — Christian Phillips
– Christian Phillips, Commented May 22, 2014 at 20:41

Christian Phillips · Accepted Answer · 2014-05-22 19:53:07Z

3

Give GroupJoin a try...

This will sum the Amounts...

var merge = customers.GroupJoin(balances, 
            c => c.CustomerNumber, 
            b => b.CustomerNumber,
            (c, b) => new
            {
                custname = c.CustomerNumber, 
                custamount = b.Sum(b2 => b2.Amount)
            });

            foreach (var cust in merge)
            {
                Console.WriteLine("{0}: {1}", cust.custname, cust.custamount);
            }

The below will list them out....

        var merge2 = customers.GroupJoin(balances,
            c => c.CustomerNumber,
            b => b.CustomerNumber,
            (c, b) => new
            {
                custname = c.CustomerNumber,
                custamount = b.ToList()
            });

        foreach (var cust in merge2)
        {
            Console.WriteLine("Customer {0} has following amounts: ", 
                cust.custname);

            foreach (var amount in cust.custamount)
            {
                Console.WriteLine(amount.Amount);
            }
        }

The image shows the output.

enter image description here

edited May 22, 2014 at 19:53

answered May 22, 2014 at 19:40

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mmeyer Over a year ago

I ended up using a combination of this and Toons33 solution. I'll update the OP.

Christian Phillips Over a year ago

@mmeyer That's essentially what I posted here?

Kevin Owens · Accepted Answer · 2014-05-22 19:27:26Z

0

Try this:

foreach (var customer in customers)
{
    var matchingBalances = balances.Where(x => x.CustomerNumber == customer.CustomerNumber);
    customer.Balances.AddRange(matchingBalances);
}

answered May 22, 2014 at 19:27

Kevin Owens

2091 gold badge2 silver badges4 bronze badges

1 Comment

mmeyer Over a year ago

This is what I'm doing currently, and I'm looking for a more efficient method of linking the two.

Christian Phillips · Accepted Answer · 2014-05-22 20:44:56Z

0

var combined = from c in customers
               join b in balance on b.CustomerNumber equal c.CustomerNumber
               select new{
                   Amount = b.Amount,
                   Balance = c.Balance,
                   };

edited May 22, 2014 at 20:44

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

answered May 22, 2014 at 19:30

Neel

11.7k3 gold badges46 silver badges63 bronze badges

1 Comment

Servy Over a year ago

You're on the right track, but this doesn't produce the desired results.

Collectives™ on Stack Overflow

c# Merge List with another List based on key

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related