使用LINQ对客户分组订单类和总订单行

问题描述

首先，有一个小的免责声明； P
我见过其他人问同样类型的问题，却没有很好的答案，但是问题也不是很详尽.因此，对于这个冗长的帖子，我们感到抱歉，但是我已经包含了代码，因此任何人都可以轻松测试它.

一些背景:
我迭代几个XML文件，并从中创建订单对象的集合. XML文件包含客户信息和订单行.客户可能出现在多个具有相同产品的XML文件中，即:

File1.xml

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

<>
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>

问题:
在上述文件中，客户12345被两次拒绝，并且他们总共购买了7种奶酪产品，其中file1.xml中有5种，file2.xml中有2种.此外，还有面包和火腿产品.我一直试图实现的目标是找到所有相同的客户，并将他们加入一个Order对象.然后，我要把所有订单行也加入进来，将每个相同产品的总数量相加.

如果这是一个SQL表，那么创建一个简单的SQL查询将很容易，该查询将返回分组并汇总了PR客户，PR产品的数据.但是，我认为这对于LINQ是一项完美的任务.不幸的是，我不太了解LINQ，也找不到任何资料可以告诉我如何解决这种情况.

我试过只使用 foreach 来代替，遍历订单并将它们与新的订单对象列表进行比较，但是我似乎总是最终改变了我要迭代的集合，这将当然会导致异常...

谁能帮我在这里找到解决方案?我可以将所有订单及其订单行提交数据库，但那不是承认失败吗? :)

对于那些可能会面临挑战的人，我为他们提供了课程和一些数据:

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
    }

能解决这个问题的人感到荣幸和荣耀:)

/Geir Rune

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

/Geir Rune

推荐答案

var sweetLovelyResult = from order in orders
                                 group order by order.AccountName into newOrders
                                 select new
                                 {
                                     AccountNumber = newOrders.Key
                                     ,
                                     TotalProductsQuantities = newOrders.Sum(a => a.OrderLines.Sum(b => b.Quantity))
                                     ,
                                     OrderLines = from orderline in newOrders.SelectMany(o => o.OrderLines)
                                                  group orderline by orderline.ProductCode into newOrderlines
                                                  select new
                                                  {
                                                      ProductCode = newOrderlines.Key
                                                      ,
                                                      Quantity = newOrderlines.Sum(q => q.Quantity)
                                                  }
                                 };

希望这是您要的:)

Hope this what you was asking for :)

这篇关于使用LINQ对客户分组订单类和总订单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！