source: KernelRecordsMVC.Web/Controllers/TopSellersController.cs@ fa0fbaf

using KernelRecordsMVC.Application.ViewModels;
using KernelRecordsMVC.Domain.Enums;
using KernelRecordsMVC.Infrastructure.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace KernelRecordsMVC.Web.Controllers;

public class TopSellersController : Controller
{
    private readonly KernelRecordsContext _context;

    public TopSellersController(KernelRecordsContext context)
    {
        _context = context;
    }

    public IActionResult Index()
    {
        var completedStatuses = new[]
        {
            OrderStatusType.PAID,
            OrderStatusType.SHIPPED,
            OrderStatusType.DELIVERED
        };

        var sellers = _context.OrderProducts
            .Where(op =>
                completedStatuses.Contains(op.Order.Status))
            .GroupBy(op => op.ProductId)
            .Select(g => new
            {
                ProductId = g.Key,
                QuantitySold = g.Sum(x => x.Quantity)
            })
            .OrderByDescending(x => x.QuantitySold)
            .Take(20)
            .ToList();

        var productIds = sellers
            .Select(x => x.ProductId)
            .ToList();

        var products = _context.Products
            .Include(p => p.Release)
                .ThenInclude(r => r.ReleaseArtists)
                    .ThenInclude(ra => ra.Artist)
            .Where(p => productIds.Contains(p.ProductId))
            .ToList();

        var result = sellers
            .Select(s =>
            {
                var product = products
                    .First(p => p.ProductId == s.ProductId);

                return new TopSellerViewModel
                {
                    ProductId = product.ProductId,
                    ReleaseId = product.ReleaseId,
                    ReleaseTitle = product.Release.Title,
                    CoverPhoto = product.Release.CoverPhoto,
                    Format = product.Format.ToString(),
                    Price = product.Price,
                    Stock = product.Stock,
                    SoldQuantity = s.QuantitySold,

                    ArtistName = string.Join(
                        ", ",
                        product.Release.ReleaseArtists
                            .OrderBy(x => x.ReleaseOrdinal)
                            .Select(x => x.Artist.ArtistName))
                };
            })
            .ToList();

        return View(result);
    }
}