Is it possible to group data for 2 columns using Linq

Question

I have the following code which fetches data from an Entify Framework DB.

return r.Find()
        .GroupBy(x => x.ProductID)
        .Select(x => new ProductCount
                     {
                         ProductID = x.Key,
                         Quantity = x.Count()
                     })
        .ToList();

As you can see the data is grouped on ProductID and a count is returned. Product is a child of Buyer which is also in the same entity, so I wondered if it would be possible to return a count at both Product level and Buyer level in one transaction.

Into a class such as:

public class BuyerAndProductCount
{
    public int BuyerID { get; set; }
    public int BuyerCount { get; set; }
    public int ProductID { get; set; }
    public int ProductCount { get; set; }
}

UPDATE

Here is the entity that is being queried.

public partial class ApplicationHistory
{
    public int ApplicationID { get; set; }
    public decimal Commission { get; set; }
    public long ResponseTimeMS { get; set; }
    public string ResponseInfo { get; set; }
    public System.Guid ApplicantID { get; set; }
    public int ApplicationResultID { get; set; }
    public int BuyerID { get; set; }
    public int ProductID { get; set; }
    public System.DateTime ExpiresOn { get; set; }
    public System.DateTime CreatedOn { get; set; }

    public virtual Application Application { get; set; }
    public virtual Applicant Applicant { get; set; }
    public virtual Product Product { get; set; }
}

How exactly would `BuyerCount` be calculated? Please show an example input set and the resulting output. — p.s.w.g, Apr 30 '14 at 14:19
Please provide a definition of your entity, a.k.a. a complete code sample, so that answers can be correct. — maxwellb, Apr 30 '14 at 14:27
The entity consists of a number of ID's, including BuyerID and ProductID - its simply a case of counting the number of Buyer transactions, just like the product transactions are counted. — dotnetnoob, Apr 30 '14 at 14:28
Where is your BuyerID coming from? It is not in your `ApplicationHistory` class? This is not answerable. — andleer, Apr 30 '14 at 14:36
possible duplicate of [Group By Multiple Columns](http://stackoverflow.com/questions/847066/group-by-multiple-columns) — Mark Rotteveel, Apr 30 '14 at 15:51

score 2 · Answer 1 · answered Apr 30 '14 at 14:19

2

You can do it using an anonymous type in the GroupBy method, for sample:

return r.Find()
        .GroupBy(x => new { x.ProductID, x.BuyerID })
        .Select(x => new ProductCount
                     {
                         ProductID = x.Key.ProductID,
                         Quantity = x.Count()
                     })
        .ToList();

answered Apr 30 '14 at 14:19

Felipe Oriani

37,948
19
131
194

Thanks - as you can see from the output class, I'm looking to return 2 counts - 1 for the Buyer and 1 for the Product – dotnetnoob Apr 30 '14 at 14:25
from his code you should be able to extract a working solution for 2 counts... create 1st groupby into var, create 2nd groupby into other var, combine 2 vars into return value.. – MaxOvrdrv Apr 30 '14 at 14:41

Is it possible to group data for 2 columns using Linq

1 Answers1