Linq Group by relative entites-CodePudding

My scenario: Users will be able to create lists and add items to these lists. What I want to do is to find the items in the lists created by the users at most.

Item Entity

public  class Item:BaseEntity
{
    public string Name { get; set; }
    public decimal Price { get; set; }
    public decimal DiscountedPrice{ get; set; }
    public virtual ICollection<ItemList> ItemLists { get; set; }
}

Item List Entity

public class ItemList:BaseEntity
{
    public string Name { get; set; }
    public string Description { get; set; }
    public int UserId { get; set; }
    public  ICollection<Item> Items { get; set; }
    [ForeignKey("UserId")]
    public virtual User User { get; set; }
}

User Entity

public class User:BaseEntity
{
    public string Name { get; set; }
    public string Surname { get; set; }
    public string Gsm { get; set; }
    public string Email { get; set; }
    public virtual ICollection<ItemList> ItemLists{ get; set; }
}

my DTO

public class TopItemsForUsers
{
    [BsonRepresentation(BsonType.ObjectId)]
    [BsonId]
    public string ItemId { get; set; }
    public string UserId { get; set; }
    public int Quantity { get; set; }
}

My Item repository

var query = _context.Items.Include(l => l.ItemLists)
            .GroupBy(g => g.ItemLists)
            .Select(z => new TopItemsInLists { ItemId = z.Key.ToString(), Quantity = z.Count() })
            .OrderByDescending(z => z.Quantity)
            .Take(10);

I want to get products that are very present in users' lists Where am I doing wrong? If anyone has any other suggestions

CodePudding user response：

Try this query. I hope I understand question correctly.

var query = 
    from u in _context.Users
    from il in u.ItemLists
    from i in il.Items
    group i by new { UserId = u.Id, ItemId = i.Id } into g 
    select new TopItemsInLists
    {
        UserId = g.Key.UserId.ToString(), 
        ItemId = g.Key.ItemId.ToString(), 
        Quantity = g.Count()
    };

query = query
    .OrderByDescending(z => z.Quantity)
    .Take(10);