Issue

This Content is from Stack Overflow. Question asked by momoxed882

I want to list the most purchased products by users by user ID.

My table looks like this

ID  UserId  ProductId
1   10      Apple
2   10      Computer
3   10      Computer
4   11      Apple
5   11      Apple
6   11      Computer
6   11      Phone
6   11      Phone
6   11      Phone
6   12      Fax
6   12      Fax
6   12      Phone

the output i wanted:

UserId: 10, MostPurchased: Computer

UserId: 11, MostPurchased: Phone

UserId: 12, MostPurchased: Fax

var mostRequestUsers = await dbContext.UserProducts.Include(x => x.Products)
.GroupBy(x => new { UserId = x.UserId, ProductName = x.Product.Name)
.OrderByDescending(gp => gp.Count())
.Select(g => new { Key = g.Key.UserId, RequestType = g.Key.ProductName }).ToListAsync();

Solution

Its kind of a hard one but here is what I came up with

var mostRequestedUsers = await dbContext.UserProducts.Include(x => x.Products).
GroupBy(x => x.UserId). // groupBy users
Select(x =>
{
    var mostPurchased = x.GroupBy(y => y.Product.Name). // groupBy ProductID
        Select(z => new { Product = z.Key, Count = z.Count() }). // create Product/Count list for each user
        OrderBy(x => x.Count).First().Product; // pick the top item in the Product/Count list
    return $"Userid: {x.Key}, MostPurchased:{mostPurchased}";
}).ToList();

This Question was asked in StackOverflow by momoxed882 and Answered by Slack Groverglow It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.