Linq Group by Related Entities

Question

我想获得他们列表中最多的项目我要运行的sql查询

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

项目实体

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

项目列表实体

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

我的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);

我想在ItemLists中找到最多的项目。我哪里做错了?如果有人有其他建议

Answer 1

不是按集合属性分组，而是按Id分组。与SQL完全相同。但在此之前，您必须将记录乘以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);

但是我建议使用Query语法，它更接近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);