使用LINQ对客户分组订单类和总订单行 [英] Grouping Orders class and Sum orderlines pr customer using 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
推荐答案
我认为这可以给您想要的结果，加油！ :)
I think this one could give you the result you want , cheers! :)
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对客户分组订单类和总订单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
