LINQ Multiple Nested Table Aggregate Query

I have 4 entities which I've defined Navigation properties on like the following:

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

I have the following fluent config:

        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);
        modelB开发者_如何学编程uilder.Entity<ShipSet>().HasRequired(p => p.Program);
        modelBuilder.Entity<CmQueueItem>().Property(p => p.LastUpdateDate).IsConcurrencyToken();

So in a nutshell, I have

ShipSet -> InstallLocation (1 to many)

InstallLocation -> ShipSetPart (1 to many)

CmQueueItem -> ShipSetPart (1 to many via INVENTORY_ITEM_ID)

I am trying to figure out how to write a LINQ query where I can create an anonymous object which includes the count of ShipSets for each CmQueueItem.

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

It should generate a SQL statement similar to the following:

  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;

I'm new to LINQ and am having a hard time understanding how to perform aggregates and groupings like this. Any ideas?

The answer as provided below is to use the "let" keyword in LINQ:

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(),
                            };

I haven't got an EF model to test this against, but give this a try...

UPDATE: Here's how to perform the join, see if that works. Something isn't right though because we shouldn't have to perform the join manually, that's what your property mapping is for.

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

Here's an alternate approach that's much cleaner, but I'm unsure if it will work in EF. Give it a try and see what you think.

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