Grouping Orders class and Sum orderlines pr customer using LINQ

See more:

C#

LINQ

Hi,

First off, a small disclaimer ;P
I've seen other people asking the same type of questions with no really good answers, but the questions were not too detailed either. So, I am sorry for this lenghty post, but I have included code so that it should be very easy for anyone to test this.

Some background:
I iterate several XML files and create a collection of order-objects from them. The XML files contains customer information and orderlines. The customer may appear in more than one XML file with identical products, i.e:

File1.xml

<Order>
  <CustomerID>12345</CustomerID>
  <OrderLines>
    <OrderLine>
       <Product>Cheese</Product>
       <Quantity>5</Quantity>
    </OrderLine>
    <OrderLine>
       <Product>Ham</Product>
       <Quantity>2</Quantity>
    </OrderLine>
  </OrderLines>
</Order>
<Order>
</Order>

<>
File2.xml

<Order>
  <CustomerID>12345</CustomerID>
  <OrderLines>
    <OrderLine>
       <Product>Cheese</Product>
       <Quantity>2</Quantity>
    </OrderLine>
    <OrderLine>
       <Product>Bread</Product>
       <Quantity>3</Quantity>
    </OrderLine>
  </OrderLines>
</Order>

The problem:
In the above files, customer 12345 is reprented twice, and they have bought a total of 7 cheese products, 5 in file1.xml and 2 in file2.xml. Addtionally there is bread and ham products. What I have been trying to accomplish is to find all identical customers and join them into one Order object. Then, I want to take all the orderlines and join them as well, summing the total quantity for each identical product.

If this was a SQL table it would be a breeze to create a simple SQL query that would return the data, grouped and summed pr customer, pr product. But, I am thinking this is a perfect task for LINQ. Unfortunately, I do not know LINQ that well, and I cannot find any sources that tells me how to approach this scenario.

I've tried just using foreach instead, iterating through the orders and comparing them with a new list of order objects, but I always seem to end up altering the collection that I am iterating - and that will result in an exception of course...

Can anyone help me reach a solution here? I am on the virge of commiting all the orders and their orderlines to a database, but wouldn't that be admitting defeat? :)

For those who can rise to the challenge I provide the classes and some data for them:

class Order
{
    public string AccountNumber;
    public string AccountName;
    public List<OrderLine> OrderLines = new List<OrderLine>();
}

class OrderLine
{
    public string Description;
    public string ProductCode;
    public int Quantity;
}

    static void Main(string[] args)
    {
        List<Order> orders = new List<Order>()
        {
            new Order()
            {
                AccountName = "12345",
                OrderLines = new List<OrderLine>()
                {
                    new OrderLine()
                    {
                        ProductCode = "Cheese",
                        Quantity = 5
                    },
                    new OrderLine()
                    {
                        ProductCode = "Ham",
                        Quantity = 2
                    }
                }
            },
            new Order()
            {
                AccountName = "12345",
                OrderLines = new List<OrderLine>()
                {
                    new OrderLine()
                    {
                        ProductCode = "Bread",
                        Quantity = 3
                    },
                    new OrderLine()
                    {
                        ProductCode = "Cheese",
                        Quantity = 2
                    }
                }
            },
            new Order()
            {
                AccountName = "55555",
                OrderLines = new List<OrderLine>()
                {
                    new OrderLine()
                    {
                        ProductCode = "First",
                        Quantity = 50
                    },
                    new OrderLine()
                    {
                        ProductCode = "Second",
                        Quantity = 100
                    },
                    new OrderLine()
                    {
                        ProductCode = "Third",
                        Quantity = 150
                    }
                }
            }
        };

        // Amazing code to group and sum objects go here
    }

Honour and glory to whoever is able to solve this one :)

/Geir Rune