Lambda expression Group by in C#

Question

I would like to group my LINQ query by ItemNumber and return the whole 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 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:

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

Not sure what else is wrong, but at the very least, you're missing a `return` in the second sample. — Ani, Jan 24 '12 at 10:11
Probably he's trying to treat g.Key as an object, whereas it's just the int. If he wants the item name in the result then he'll also have to store it in the key object (and make the keys an object that's comparable) or read it from the first entry in the group enumberator I guess. — Rup, Jan 24 '12 at 10:13

vc 74 · Answer 1 · 2012-01-24T10:26:06.347

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

Amar Palsapure · Answer 2 · 2012-01-24T11:02:16.840

7

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

edited Jan 24 '12 at 11:02

answered Jan 24 '12 at 10:29

Amar Palsapure

9,590
1
27
46

We are almost there - no errors until I am trying to bind my query to a gridview. I am getting this exception: System.NotSupportedException: Explicit construction of entity type 'GWportal.BusinessLayer.Repository.WebsiteOrderStatus' in query is not allowed. – KLIM8D Jan 24 '12 at 10:45
2

`Entities can be created outside of queries and inserted into the data store using a DataContext. You can then retrieve them using queries. However, you can't create entities as part of a query.` In this case you will have to create a custom class for binding and map 'WebsiteOrderStatus' to the object of that class. – Amar Palsapure Jan 24 '12 at 10:50

score 6 · Answer 3 · answered Jan 24 '12 at 10:14

I think the Select should be:

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

Jamiec · Answer 4 · 2012-01-24T10:31:59.927

5

The only problems I see with your query are

Missing return statement as per comments
The select statement should be:

-

.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

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

edited Jan 24 '12 at 10:31

answered Jan 24 '12 at 10:15

Jamiec

133,658
13
134
193

Seems like it's only returning ItemNumber and Total then. What I want is the whole table just with a Column named total, which contains the total for quantity – KLIM8D Jan 24 '12 at 10:24
okay, soo every object I want to get in the result must be in the grouping. I actually have 5 other fields in the table, could there be a better way doing this grouping other than all fields I need has to be contained in the groupby? – KLIM8D Jan 24 '12 at 10:38
@KLIM8D - No, by definition, every field you want in the output of the Group, which isn't aggregated (ie, summed) must be in the `GroupBy`. (Caveat: You can use the other suggestion which is to use `.First()` but I wouldn't recommend it, you're enumerating the list every time) – Jamiec Jan 24 '12 at 10:42

Vitali Vishneuski · Answer 5 · 2012-01-24T14:29:09.970

2

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.

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

edited Jan 24 '12 at 14:29

answered Jan 24 '12 at 10:34

Vitali Vishneuski

61
3

Why can't you use anonymous types as the return value. – Jamiec Jan 24 '12 at 10:43
1

How you specify it in signature of the method? – Vitali Vishneuski Jan 24 '12 at 10:51
MSDN: You cannot declare a field, a property, an event, or the return type of a method as having an anonymous type. Similarly, you cannot declare a formal parameter of a method, property, constructor, or indexer as having an anonymous type. To pass an anonymous type, or a collection that contains anonymous types, as an argument to a method, you can declare the parameter as type object. However, doing this defeats the purpose of strong typing. If you must store query results or pass them outside the method boundary, consider using an ordinary named struct or class instead of an anonymous type. – Vitali Vishneuski Jan 24 '12 at 10:53
Thank you, I haven't worked with custom classes before though. I'll try to give it a shot if I find my solution (edit topic) slow in performance and have the time :) – KLIM8D Jan 24 '12 at 11:18

Lambda expression Group by in C#

5 Answers5