Asp.Net Core Linq如何执行具有多对多关系的嵌套groupby？

Question

我正在创建一个 KPI 仪表板，显示订单的总收入。业务逻辑是每个项目都通过多对多关系链接到一种事件类型，并通过一对多关系链接到供应商类型。根据供应商类型，有不同的供应商销售这些物品。还有一个表，使用 orderItemId 和 sellerId 将供应商链接到他们的订单。我想要实现的目标如下：

• 获取所有已成功履行的订单并获取其订单项目。
• 使用订单项目从我上面提到的表中获取供应商订单项目。
• 获得供应商订单商品后，我想按供应商 ID 和 iem 事件类型 ID 对它们进行分组，以便我可以显示每个供应商销售的每种事件类型的商品。
• 供应商
  • 事件类型
    • 项目列表
• 供应商
  • 事件类型
    • 项目列表

以上是我想要发生的事情。我设法按供应商 ID 对它们进行分组，但由于项目和事件类型之间存在多对多关系，我很难按 eventTypeId 对它们进行分组。

以下是模型：

 public partial class Item
{
    public Item()
    {
        Favorites = new HashSet<Favorite>();
        ItemDetails = new HashSet<ItemDetail>();
        ItemEventTypes = new HashSet<ItemEventType>();
        OrderItems = new HashSet<OrderItem>();
        SupplierItems = new HashSet<SupplierItem>();
    }

    [Key]
    public int Id { get; set; }
    [StringLength(250)]
    public string Image { get; set; }
    public string Description { get; set; }
    [StringLength(200)]
    public string Title { get; set; }
    public int? CategoryId { get; set; }
    [Column("isDeleted")]
    public bool? IsDeleted { get; set; }
    public double? Price { get; set; }
    public int? EventTypeId { get; set; }
    public int? NumberOfGuestsId { get; set; }
    public double? DaberniPrice { get; set; }
    public double? RegularPrice { get; set; }
    public int? Tax { get; set; }
    [Column("SupplierTypeID")]
    public int? SupplierTypeId { get; set; }
    [Column("SortID")]
    public int? SortId { get; set; }
    public bool? IsDisabled { get; set; }
    public int? Min { get; set; }
    public int? Max { get; set; }
    public int? Increment { get; set; }
    public bool? IsShisha { get; set; }
    public bool? IsSoldByPackage { get; set; }
    [Column("ImageAR")]
    [StringLength(250)]
    public string ImageAr { get; set; }
    [Column("DescriptionAR")]
    public string DescriptionAr { get; set; }
    [Column("TitleAR")]
    [StringLength(250)]
    public string TitleAr { get; set; }
    public int? Capacity { get; set; }

    [ForeignKey(nameof(CategoryId))]
    [InverseProperty(nameof(Catrgory.Items))]
    public virtual Catrgory Category { get; set; }
    [ForeignKey(nameof(NumberOfGuestsId))]
    [InverseProperty(nameof(NumberOfGuest.Items))]
    public virtual NumberOfGuest NumberOfGuests { get; set; }
    [ForeignKey(nameof(SupplierTypeId))]
    [InverseProperty("Items")]
    public virtual SupplierType SupplierType { get; set; }
    [InverseProperty(nameof(Favorite.Item))]
    public virtual ICollection<Favorite> Favorites { get; set; }
    [InverseProperty(nameof(ItemDetail.Item))]
    public virtual ICollection<ItemDetail> ItemDetails { get; set; }
    [InverseProperty(nameof(ItemEventType.Item))]
    public virtual ICollection<ItemEventType> ItemEventTypes { get; set; }
    [InverseProperty(nameof(OrderItem.Item))]
    public virtual ICollection<OrderItem> OrderItems { get; set; }
    [InverseProperty(nameof(SupplierItem.Item))]
    public virtual ICollection<SupplierItem> SupplierItems { get; set; }
}

public partial class ItemEventType
{
    [Key]
    public int Id { get; set; }
    public int? EventTypeId { get; set; }
    public int? ItemId { get; set; }
    public bool? IsDeleted { get; set; }

    [ForeignKey(nameof(EventTypeId))]
    [InverseProperty("ItemEventTypes")]
    public virtual EventType EventType { get; set; }
    [ForeignKey(nameof(ItemId))]
    [InverseProperty("ItemEventTypes")]
    public virtual Item Item { get; set; }
}

 public partial class SupplierAssignedOrderItem
{
    [Key]
    public int Id { get; set; }
    [Column("SupplierID")]
    public int? SupplierId { get; set; }
    [Column("ItemID")]
    public int? ItemId { get; set; }
    public int? Status { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime? CreatedDate { get; set; }

    [ForeignKey(nameof(ItemId))]
    [InverseProperty(nameof(OrderItem.SupplierAssignedOrderItems))]
    public virtual OrderItem Item { get; set; }
    [ForeignKey(nameof(Status))]
    [InverseProperty(nameof(OrderStatus.SupplierAssignedOrderItems))]
    public virtual OrderStatus StatusNavigation { get; set; }
    [ForeignKey(nameof(SupplierId))]
    [InverseProperty("SupplierAssignedOrderItems")]
    public virtual Supplier Supplier { get; set; }
}

感谢任何帮助。谢谢。

Answer 1

我尝试使用 EF Core，您可以获得订单和供应商，如下所示：

DBcontext：

public class EFCoreDbContext : DbContext
{
    public EFCoreDbContext(DbContextOptions<EFCoreDbContext> options)
        : base(options)
    {
    }

    public DbSet<Item> Item { get; set; }
    public DbSet<Order> Order { get; set; }
    public DbSet<Supplier> Supplier { get; set; }  
   
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Item>().ToTable("Item");        
        modelBuilder.Entity<Order>().ToTable("Order");     

        modelBuilder.Entity<OrderItem>().HasKey(x => new { x.ItemId, x.OrderId });
        modelBuilder.Entity<OrderItem>().HasOne(x => x.Item).WithMany(x => x.OrderItems).HasForeignKey(x => x.ItemId);
        modelBuilder.Entity<OrderItem>().HasOne(x => x.Order).WithMany(x => x.OrderItems).HasForeignKey(x => x.OrderId);
        modelBuilder.Entity<OrderItem>().ToTable("OrderItem");

        modelBuilder.Entity<Supplier>().HasMany(x => x.Orders).WithOne(x => x.Supplier).HasForeignKey(x => x.SupplierId);
        modelBuilder.Entity<Supplier>().ToTable("Supplier");

        modelBuilder.Entity<SupplierItem>().HasKey(x => new { x.ItemId, x.SupplierId });
        modelBuilder.Entity<SupplierItem>().HasOne(x => x.Item).WithMany(x => x.SupplierItems).HasForeignKey(x => x.ItemId);
        modelBuilder.Entity<SupplierItem>().HasOne(x => x.Supplier).WithMany(x => x.SupplierItems).HasForeignKey(x => x.SupplierId);
        modelBuilder.Entity<SupplierItem>().ToTable("SupplierItem");
    }

控制器：

var orderlist = _context.Order.Include(p => p.OrderItems).ThenInclude(q => q.Item).ToList();
            
var supplierlist = _context.Supplier.Include(p => p.SupplierItems).ThenInclude(q => q.Item).ToList();

我认为如果您从项目类中删除一些属性并将它们添加到供应商项目类中，例如价格属性和事件类型，效果会更好财产。

如果您从不同的供应商、不同的日子购买，某件商品可能会有不同的价格。如果事件类型用于描述交易状态，则也应将其删除。

public class Item
    {
        public Item()
        {
            OrderItems = new List<OrderItem>();
            
            SupplierItems = new List<SupplierItem>();
        }
        public int ItemId { get; set; }
        public string ItemName { get; set; }        
        public List<OrderItem> OrderItems { get; set; }        
        public List<SupplierItem> SupplierItems { get; set; }
    }
public class Supplier
    {
        public Supplier()
        {
            Orders = new List<Order>();
            SupplierItems = new List<SupplierItem>();            
        }
        public int SupplierId { get; set; }
        public string SupplierName { get; set; }
        public List<Order> Orders { get; set; }
        public List<SupplierItem> SupplierItems { get; set; }
        
    }    
public class SupplierItem
    {
        
        public Item Item { get; set; }
        public int ItemId { get; set; }
        public Supplier Supplier { get; set; }
        public int SupplierId { get; set; }
        
        public double SupplierItemPrice { get; set; }       

    }

如果您确实想按两个属性对供应商列表进行分组
你可以尝试：

var somesupplierslist = supplierlist.GroupBy(x => new { x.SupplierId, x.SupplierName }).ToList();