C#中的Lambda表达式Group by

Lambda expression Group by in C#

提问by KLIM8D

I would like to group my LINQ query by ItemNumberand return the whole table with the total for Quantity.

我想将我的 LINQ 查询分组ItemNumber并返回整个表以及Quantity.

Example:
ItemNumber - ItemName - Quantity
100          Item1       1
150          Item2       2
100          Item1       2
200          Item3       1
150          Item2       2

Should be:
ItemNumber - ItemName - Quantity
100          Item1       3
150          Item2       4
200          Item3       1

This is the query I am trying to group:

这是我试图分组的查询：

public IQueryable<WebsiteOrderStatus> GetOrderStatusByAccountNumberWithoutDeleted
        (string accountNumber)
{
    return db.WebsiteOrderStatus
             .Where(x => x.AccountNumber == accountNumber && x.LastUpdatedStatus != 1);
}

And my best result so far(this can't compile though):

到目前为止我最好的结果（虽然无法编译）：

public IQueryable<IGrouping<Int32?, WebsiteOrderStatus>> lol(string accountNumber)
{
     db.WebsiteOrderStatus
       .Where(x => x.AccountNumber == accountNumber && x.LastUpdatedStatus != 1)
       .GroupBy(g => g.ItemNumber)
       .Select(g => new
                    {
                         g.Key.ItemNumber,
                         Column1 = (Int32?)g.Sum(p => p.Quantity)
                    });
 }

EDIT:

编辑：

Thanks for the replies everyone, I must face it. Theese anonymous types are pretty hard to work with in my opinion, so I found another solution.

谢谢大家的回复，我必须面对。在我看来，这些匿名类型很难处理，所以我找到了另一个解决方案。

I made another method, which sums the quantity of the users items and grouped the first one.

我做了另一种方法，它总结了用户项目的数量并将第一个分组。

public IQueryable<WebsiteOrderStatus> GetOrderStatusByAccountNumberWithoutDeleted(string accountNumber)
{
    return db.WebsiteOrderStatus.Where(x => x.AccountNumber == accountNumber && x.LastUpdatedStatus != 1).GroupBy(x => x.ItemNumber).Select(grp => grp.First());
}

public int GetQuantityOfUsersItem(string accountNumber, string itemNumber)
{
    return db.WebsiteOrderStatus.Where(x => x.ItemNumber == itemNumber && x.AccountNumber == accountNumber).Sum(x => x.Quantity);
}

At the page where I have my gridview I did:

在我拥有 gridview 的页面上，我做了：

var query = websiteOrderStatusRep.GetOrderStatusByAccountNumberWithoutDeleted(AppSession.CurrentLoginTicket.AccountNumber).Select(x => new { x.ItemName, x.ItemNumber, x.FormatName, x.Price, x.Status, x.Levering, Quantity = websiteOrderStatusRep.GetQuantityOfUsersItem(x.AccountNumber, x.ItemNumber)});

回答by vc 74

public IQueryable<IGrouping<Int32?, WebsiteOrderStatus>> lol(string accountNumber)
{
     db.WebsiteOrderStatus
       .Where(x => x.AccountNumber == accountNumber && x.LastUpdatedStatus != 1)
       .GroupBy(g => g.ItemNumber)
       .Select(g => new
                    {
                         ItemNumber = g.Key,
                         ItemName = g.First().ItemName,
                         Count = g.Sum(item => item.Quantity)
                    });
 }

回答by Daniel Hilgarth

I think the Selectshould be:

我认为Select应该是：

   .Select(g => new
                {
                     ItemNumber = g.Key,
                     Column1 = (Int32?)g.Sum(p => p.Quantity)
                });

Note the change in the first line of the anonymous type. The key of the grouping is already the item number.

注意匿名类型第一行的变化。分组的关键字已经是项目编号。

回答by Jamiec

The only problems I see with your query are

我在您的查询中看到的唯一问题是

1. Missing returnstatement as per comments
2. The select statement should be:

1. return根据评论缺少声明
2. 选择语句应该是：

-

——

.Select(g => new {
       ItemNumber = g.Key,
       Total = g.Sum(p => p.Quantity)
    });

---

---

EDIT: If you want to get, lets say ItemNumber and ItemName , in the resulting object, you must also group on those fields

编辑：如果你想得到，比如说 ItemNumber 和 ItemName ，在结果对象中，你还必须对这些字段进行分组

db.WebsiteOrderStatus
   .Where(x => x.AccountNumber == accountNumber && x.LastUpdatedStatus != 1)
   .GroupBy(g => new { g.ItemNumber, g.ItemName })
   .Select(g => new
                {
                     ItemNumber = g.Key.ItemNumber,
                     ItemName = g.Key.ItemName,
                     Count = g.Sum(item => item.Quantity)
                });

回答by Amar Palsapure

 public IQueryable<OrderStatus > lol(string accountNumber)
 {
     return db.WebsiteOrderStatus
        .Where(x => x.AccountNumber == accountNumber && x.LastUpdatedStatus != 1)
        .GroupBy(g => g.ItemNumber)
        .Select(g => 
                new OrderStatus //This is your custom class, for binding only
                {
                     ItemNumber = g.Key,
                     ItemName = g.First().ItemName,
                     Quantity = g.Sum(g => g.Quantity)
                }
        );
 }

回答by Vitali Vishneuski

You cannot use anonymous type for return value type. So you will never compile the code.

您不能将匿名类型用于返回值类型。所以你永远不会编译代码。

Also your linq expression has IQueryable< [anonymous type] > result type.

此外，您的 linq 表达式具有 IQueryable< [匿名类型] > 结果类型。

I believe that you can do something like this:

我相信你可以做这样的事情：

public IQueryable<OrderStatus> lol(string accountNumber) 
{ 
     db.WebsiteOrderStatus 
       .Where(order => order.AccountNumber == accountNumber && order.LastUpdatedStatus != 1) 
       .GroupBy(order => order.ItemNumber) 
       .Select(grouping => new OrderStatus //This is your custom class, for binding only
                    { 
                         ItemNumber = grouping.Key, 
                         ItemName = grouping.First().ItemName, 
                         Quantity = grouping.Sum(order => order.Quantity) 
                    }); 
}

I`ve fixed my answer too :)

我也修正了我的答案:)