linq, best sales in age group-CodePudding

Before that, I asked a similar question, but as it turned out, I set the task incorrectly and eventually got the wrong result.

I have multiple db tables: book-order-item-order-users

I need to get a list of the best-selling books in a given age category. Everything works correctly in Mysql:

SELECT book.Id, book.Title, SUM(orderitem.Amount) as sales FROM book JOIN orderitem ON book.Id = orderitem.Fk_BookId 
JOIN webbookshop.order ON webbookshop.order.Id = orderitem.Fk_OrderId 
JOIN aspnetusers ON aspnetusers.Id = webbookshop.order.UserId
WHERE TIMESTAMPDIFF(YEAR, aspnetusers.DateofBirth, CURDATE()) >=17 AND TIMESTAMPDIFF(YEAR, aspnetusers.DateofBirth, CURDATE()) <= 27 GROUP BY book.Id ORDER BY sales DESC;

but there are problems when transver to LINQ. The number of sales for each order is stored in the OrderItem table and the Amount field. But in both cases I can't interact with it normally. Instead of field sales with operator AS I create in BookForRecomendVM field NumOfSales. But in the end, I can't properly calculate this value

I have tried these options:

var result = (from b in _context.Book
              join oi in _context.OrderItem on b.Id equals oi.Fk_BookId
              join o in _context.Order on oi.Fk_OrderId equals o.Id
              join u in _context.Users on o.UserID equals u.Id
              where EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) >= 17 && EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) <= 27
              select new BookForRecomendVM()
              {
                   Id = b.Id,
                   Title = b.Title,
                   Pages = b.Pages,
                   NumOfSales = oi.Sum(Amount)
               }).Distinct().OrderByDescending(x => x.NumOfSales);

In the first case, I use Distinct, but I cant use NumOfSales = oi.Sum(x => x.Amount).

var result = (from b in _context.Book
              join oi in _context.OrderItem on b.Id equals oi.Fk_BookId
              join o in _context.Order on oi.Fk_OrderId equals o.Id
              join u in _context.Users on o.UserID equals u.Id
              where EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) >= 17 && EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) <= 27
              select b).GroupBy(x => new { Id = x.Id, Title = x.Title, Pages = x.Pages
              }).Select(b => new BookForRecomendVM()
              {
                  Id = b.Key.Id,
                  Title = b.Key.Title,
                  Pages = b.Key.Pages,
                  NumOfSales = 

              }).OrderByDescending(x => x.NumOfSales);

In the second version, I can't access the orderitem, so again I can't calculate the number of sales.

In the end, I'm completely confused, and I don't understand how to do it right. I would be very grateful if someone could help me with a solution. Thank you.

Update:

public class Book
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    public int Pages { get; set; }
    public string Format { get; set; }
    public string LongDescription { get; set; }
    public string ShortDescription { get; set; }
    public int Amount { get; set; }
    public float Price { get; set; }
    public string ImageUrl { get; set; }
    public bool IsFavor { get; set; }
    public DateTime? ReleaseDate { get; set; }

    public int Fk_PublisherId { get; set; }
    [ForeignKey("Fk_PublisherId")]
    public Publisher Publisher { get; set; }

    [JsonIgnore]
    public List<OrderItem> OrderItem { get; set; }
    public List<Book_Author> Book_Author { get; set; }
    public List<Book_Genre> Book_Genre { get; set; }
}

public class OrderItem
{
    public int Amount { get; set; }
    public int Fk_BookId { get; set; }
    [ForeignKey("Fk_BookId")]
    public virtual Book Book { get; set; }
    public int Fk_OrderId { get; set; }
    [ForeignKey("Fk_OrderId")]
    public virtual Order Order { get; set; }

}

public class Order
{
    [Key]
    public int Id { get; set; }
    public string ContactEmail { get; set; }
    public string ContactPhone { get; set; }
    public string ContactName { get; set; }
    public string Address { get; set; }
    public double Sum { get; set; }
    public string UserID { get; set; }
    [ForeignKey("UserID")]
    public ApplicationUser ApplicationUser { get; set; }
    public int Fk_OrderStatusId { get; set; }
    [ForeignKey("Fk_OrderStatusId")]
    public OrderStatus OrderStatus { get; set; }
    public int Fk_DeliveryId { get; set; }
    [ForeignKey("Fk_DeliveryId")]
    public Delivery Delivery { get; set; }
    public List<OrderItem> OrderItem { get; set; }

}

public class ApplicationUser: IdentityUser
{
    public string FullName { get; set; }
    public DateTime? DateofBirth { get; set; }

}

And BookForRecomendVM

public class BookForRecomendVM
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int Pages { get; set; }
    public string Format { get; set; }
    public string LongDescription { get; set; }
    public string ShortDescription { get; set; }
    public int Amount { get; set; }
    public float Price { get; set; }
    public string ImageUrl { get; set; }
    public bool IsFavor { get; set; }
    public DateTime? ReleaseDate { get; set; }
    public string PublisherName { get; set; }
    public int NumOfSales { get; set; }
}

CodePudding user response：

Try the following query:

var query = 
    from b in _context.Book
    from oi in b.OrderItem
    where EF.Functions.DateDiffYear(oi.Order.ApplicationUser.DateofBirth, DateTime.Now) >= 17 
        && EF.Functions.DateDiffYear(oi.Order.ApplicationUser.DateofBirth, DateTime.Now) <= 27
    group oi by new { Id = b.Id, Title = b.Title, Pages = b.Pages } into g
    select new BookForRecomendVM()
    {
        Id = g.Key.Id,
        Title = g.Key.Title,
        Pages = g.Key.Pages,
        NumOfSales = g.Sum(x => x.Amount)
    };

query = query.OrderByDescending(x => x.NumOfSales);

In this query group oi specifies what should be accessible for select and aggregation.