LINQ多个嵌套表聚合查询

本文关键字：查询 嵌套 LINQ | 更新日期: 2023-09-27 18:05:33

我有4个实体，我已经定义了导航属性，如下所示:

internal class ShipSet
{
    [Key]
    [Column("SHIPSET_ID")]
    public decimal ID { get; set; }
    public virtual ICollection<InstallLocation> InstallLocations { get; set; }
}
internal class InstallLocation
{
    [Key]
    [Column("INSTLOC_ID")]
    public decimal ID { get; set; }
    [Column("SHIPSET_ID")]
    public decimal ShipSetID { get; set; }
    public virtual ICollection<ShipSetPart> ShipSetParts { get; set; }
    public virtual ShipSet ShipSet { get; set; }
}

internal class ShipSetPart
{
    [Key]
    [Column("PARTS_ID")]
    public decimal ID { get; set; }
    [Column("INSTLOC_ID")]
    public decimal InstallLocationID { get; set; }
    public virtual CmQueueItem CmQueueItem { get; set; }
    public virtual InstallLocation InstallLocation { get; set; }
}
internal class CmQueueItem
{
    [Key]
    [Column("INVENTORY_ITEM_ID")]
    public decimal ID { get; set; }
    public virtual ICollection<ShipSetPart> ShipSetParts { get; set; }
}

我有以下流畅的配置:

        modelBuilder.Entity<CmQueueItem>().HasMany(p => p.ShipSetParts).
            WithRequired(s=>s.CmQueueItem).Map(m=>m.MapKey("INVENTORY_ITEM_ID"));
        modelBuilder.Entity<ShipSetPart>().HasRequired(p => p.InstallLocation);
        modelBuilder.Entity<InstallLocation>().HasRequired(p => p.ShipSet);
        modelBuilder.Entity<ShipSet>().HasRequired(p => p.Program);
        modelBuilder.Entity<CmQueueItem>().Property(p => p.LastUpdateDate).IsConcurrencyToken();

简而言之，我有

ShipSet -> InstallLocation (1 to many)

InstallLocation -> ShipSetPart (1 to many)

CmQueueItem -> ShipSetPart (1 to many via INVENTORY_ITEM_ID)

我想弄清楚如何写一个LINQ查询，在那里我可以创建一个匿名对象，其中包括每个CmQueueItem的ShipSets计数。

            var queueItems = from c in dbContext.CmQueueItems
                             select new
                                        {
                                            InventoryItemID = c.ID,
                                            ShipSets = 0 //[magical LINQ goes here]
                                        };

它应该生成一个类似于下面的SQL语句:

  select d.inventory_item_id, count(a.shipset_id) as ShipSets 
  from shipsets a, 
  instlocs b, 
  ss_parts c, 
  cm_queue d
  where a.shipset_id = b.shipset_id
  and b.instloc_id = c.instloc_id
  and c.inventory_item_id = d.inventory_item_id
  group by d.inventory_item_id;

我是LINQ的新手，很难理解如何执行这样的聚合和分组。什么好主意吗?

下面提供的答案是在LINQ中使用"let"关键字:

var query = from c in dbContext.CmQueueItems
                let shipSets = (from s in c.ShipSetParts
                                select s.InstallLocation.ShipSet)
                select new
                            {
                                InventoryItemId = c.ID,
                                ShipSets = shipSets.Count(),
                            };

LINQ多个嵌套表聚合查询

我还没有EF模型来测试这个，但是给这个试试…

UPDATE:下面是如何执行连接，看看它是否有效。有些地方不太对，因为我们不应该手动执行连接，这就是你的属性映射的作用。

var queueItems = from c in dbContext.CmQueueItems
                 join s in dbContext.ShipSetParts
                 on c.ID equals s.CmQueueItem.ID
                 group s by s.CmQueueItem.ID into grouped
                 select new
                 {
                    InventoryItemID = grouped.Key,
                    //this may need a distinct before the Count
                    ShipSets = grouped.Select(g => g.InstallLocation.ShipSetID).Count()
                 };

这里有一个更干净的替代方法，但我不确定它是否能在EF中工作。试一试，看看你觉得怎么样。

var queueItems = from c in dbContext.CmQueueItems                     
                 let shipSets = (from s in c.ShipSetParts
                                 select s.InstallLocation.ShipSet)
                 select new
                 {
                     InventoryItemID = c.ID,
                     ShipSets = shipSets.Count()
                 };
相关文章：