Lambda Group expression in C #

Question

I would like to group my LINQ query using ItemNumber and return the entire table with the total for 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'm 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 cannot be compiled):

 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 all the answers, I have to meet him. It is quite difficult for you to work with anonymous types, in my opinion, so I found another solution.

I made another method that sums up the number of user elements 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); } 

On the page where I have gridview, I did:

 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)}); 

Answer 1

 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) }); } 

Answer 2

  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) } ); } 

Answer 3

The only problems that I see with your request are

• Missing return according to comments
• The select statement should be:

-

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

---

EDIT: if you want to get, let's specify ItemNumber and ItemName, in the resulting object you should also group by these fields

 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) }); 

Answer 4

I think Select should be:

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

Notice the change in the first line of the anonymous type. The grouping key is already the position number.

Answer 5

You cannot use an anonymous type for the return type. This way you will never compile code.

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

I believe 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 also corrected my answer :)