How to select and group fields using LINQ?

I want to know how l can group rows in my table using LINQ. The below code successfully loaded my grid with data from the database, so my requirement is to select and group them into store and item.

 [HttpGet]
    public object Get()
    {
        return new { Items = _context.farmtable
        .Select(x => new {x.Id, x.Item, x.Store, x.Location })
        .ToList(), Count = _context.farmtable.Count() };
    }

Id	Item	Store	Location
1	Apple	House A	Line-04
2	Mango	House B	Line-23
1	Apple	House A	Line-15
1	Apple	House B	Line-08
3	Banana	House A	Line-02

This is what l had tried, but it is not displaying anything.

[HttpGet]
    public object Get()
    {
        return new { Items = _context.farmtable.GroupBy(x => x.Store, x => x.Item )
       .Select(group => new { Store = group.Key, Item = group.Key, Items = group.ToList() })
       .ToList(), Count = _context.farmtable.Count() };         
    }

Expected output

Store	Item	Count
House A	Apple	2
House A	Banana	1
House B	Apple	1
House B	Mango	1

Solution

First an efficiency Improvement

For efficiency reason, while concatenating LINQ statements, you should keep the intermediate results IEnumerable<...> (or IQueryable<...>) as long as possible. So don't use ToList or ToArray if you are not going to use the results

The reason for this, is that if you caller does not want all items, only the first few, then you use a lot of processing power for things that are not used.

For example, if the caller of your Get only wants to know if there is any item to get:

public bool AreFarmsAvailabe()
{
    return this.Get().Any();
}

If method Get uses ToList, then thousands of Farms are put into a List, and the only thing that is done with this is that you check whether there is anything in the list. So you could have stopped as soon as you've got one Farm.

Therefore, consider to change method Get

class FarmStoreLocation             // TODO: invent a proper name
{
    public int Id {get; set;}
    public string Item {get; set;}        // might be an enum, or a class
    public string Store {get; set;}
    public string Location {get; set;}
}

And your method Get:

IEnumerable<FarmStoreLocation> GetFarmStoreLocations()
{
    IEnumerable<FarmStoreLocation> fetchedFarmStoreLocations = _context.FarmTable
    .Select(farm => new FarmStoreLocation
    {
        Id = farm.Id,
        Item = farm.Item,
        Store = farm.Store,
        Location = farm.Location,
    });

    return fetchedFarmStoreLocations;
}

I don't know if your _context.FarmTable returns an IEnumerable<...> or an IQueryable<...>, if it is the latter, then let the procedure return an IQueryable.

The advantage of changing your Get method, is that it is way easier to check if the method returns what you want. You can unit test it. You can reuse it in other queries, and if later the input changes, for instance, if later you get the data from a database, or from a CSV file, the users of your Get method won't have to change.

Back to your question

Requirement: Given an input sequence of FarmStoreLocations, transform this into a sequence of combinations of [Store, Item], and count how many these combinations are in the input sequence.

So you want to count how many times you see the [House A, Apple] combination, and how many times you see the [House A, Banana] combination, etc.

As you already gathered, for this you need one of the overloads of Enumerable.GroupBy. You want to make groups of FarmStoreLocations that have the same value for [Store, Item]. After that you want to count the number of FarmStoreLocations in each group. For this I use the overload of Enumerable.GroupBy that has both parameters KeySelector and ResultSelector:

IEnumerable<FarmStoreLocation> farmStoreLocations = GetFarmStoreLocations();

var countedStoreItems = farmStoreLocations.GroupBy (

// parameter keySelector: make groups of farmStoreLocations that have the same
// value for [Store, Item]
farmStoreLocation => new
{
    Store = farmStoreLocation.Store,
    Item = farmStoreLocation.Item,
},

// parameter resultSelector: from every found combination of [Store, Item]
// and all farmStoreLocations that have these values for Store and Item
// make one new:
(storeItemCombination, farmStoreLocationsWithThisStoreItemCombination) => new
{
    Store = storeItemCombination.Store,
    Item = storeItemCombination.Item,

    Count = farmStoreLocationsWithThisStoreItemCombination.Count(),
});

In words: from your input sequence of farmStoreLocations make groups of FarmStoreLocations that have the same value for properties Store and Item. The key of the group is the combination object [Store, Item].

The result is a sequence of groups, where every group will have a Key, which is a [Store, Item], and all FarmStoreLocations that have these values for Store and Item.

From every group, take the key (= one storeItemCombination) and all members of the group (= all farmStoreLocations with this storeItemCombination), and make for every group exactly one object.

This one object contains the value of the Store that all farmStoreLocations of the group have, and the value of the Item that all farmStoreLocations of the group have. Finally, this one object contains the number of farmStoreLocations in the group.