Linq Group by Related Entities-CodePudding

I want to get the most items in their list the sql query I want to run

select 
    i.Id, 
    count(*) as count
from Items
    inner join ItemItemList il on i.Id = il.ItemsId
group by i.Id
order by count desc

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; }
}

my DTO

public class TopItemsInLists
{
    [BsonRepresentation(BsonType.ObjectId)]
    [BsonId]
    public string ItemId { 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 the most found items in the ItemLists. Where am I doing wrong? If anyone has any other suggestions

CodePudding user response：

Instead of grouping by collection property, you have to group by Id. Exactly as in SQL. But before that you have to multiply records by SelectMany.

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

But I would suggest to use Query syntax for such constructions which is closer to the SQL:

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

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