get most repetitive data group by user id and product-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Here is another approach:

var mostRequestUsers = await dbContext.UserProducts 
    .Include(x => x.Products)
    .GroupBy(x => new { x.UserId, x.ProductId })
    .Select(g => new
    {
        MostPurchased = g.Key.ProductId,
        g.Key.UserId,
        Count = g.Count()
    })
    .GroupBy(x => x.UserId)
    .Select(g => g.OrderByDescending(t => t.Count).First());

Result:

UserId: 10, MostPurchased: Computer, Count: 2

UserId: 11, MostPurchased: Phone, Count: 3

UserId: 12, MostPurchased: Fax, Count: 2