linq加入，分组以获取子表的计数

Question

在加入工作后，我似乎被计数位卡住了。我在下面尝试的是根据下面的联接打印文档数。获取每个"guardiandocsrequired"的计数的代码是什么？

var guardianEntityType = new {EntityTypeFK = "GUARDIAN"};
return (from d in dbContext.GuardianDocsRequireds
        join p in dbContext.DocumentPrintingLogs on
        new { docTypeFK = d.DocTypeFK, entityFK = d.GuardianFK } equals
        new { docTypeFK = p.DocTypeFK, entityFK = p.EntityFK }
        where d.GuardianFK == entityPK && p.ItemGroupFK == itemGroupID && p.EntityTypeFK == "GUARDIAN"
        group d by new
        {
            d.GuardianFK,
            d.DocTypeFK,
            d.DocumentType.DocTypeDescription,
            d.RequiredStatus
        }
            into res
            select new DocumentsRequired
            {
                EntityPK = res.Key.GuardianFK,
                EntityType = entityType,
                DocTypeFK = res.Key.DocTypeFK,
                DocTypeDescription = res.Key.DocTypeDescription,
                RequiredStatus = res.Key.RequiredStatus,
                PrintCount = ???
            }
        ).ToList();

如果有帮助的话，我已经编写了sql来生成我所需要的内容，如下所示：

    SELECT gdr.DocRequiredID,gdr.RequiredDate,gdr.GuardianFK,gdr.DocTypeFK,gdr.RequiredStatus,
COUNT(dpl.DocPrintedID) AS documentsPrinted
        FROM dbo.GuardianDocsRequired gdr
        LEFT OUTER JOIN dbo.DocumentPrintingLog dpl ON gdr.DocTypeFK = dpl.DocTypeFK
            AND gdr.GuardianFK = dpl.EntityFK
            AND dpl.EntityTypeFK = 'GUARDIAN'
        WHERE gdr.GuardianFK = @entityPK
        GROUP BY gdr.DocRequiredID,gdr.RequiredDate,gdr.GuardianFK,gdr.DocTypeFK,gdr.RequiredStatus

Answer 1

你的意思是这样的吗？

var guardiandocsrequired =  (from d in dbContext.GuardianDocsRequireds
                            join p in dbContext.DocumentPrintingLogs on
                            new { docTypeFK = d.DocTypeFK, entityFK = d.GuardianFK } equals
                            new { docTypeFK = p.DocTypeFK, entityFK = p.EntityFK }
                            where d.GuardianFK == entityPK && p.ItemGroupFK == itemGroupID && p.EntityTypeFK == "GUARDIAN"
                            group d by new
                            {
                                d.GuardianFK,
                                d.DocTypeFK,
                                d.DocumentType.DocTypeDescription,
                                d.RequiredStatus
                            }
                                into res
                                select new DocumentsRequired
                                {
                                    EntityPK = res.Key.GuardianFK,
                                    EntityType = entityType,
                                    DocTypeFK = res.Key.DocTypeFK,
                                    DocTypeDescription = res.Key.DocTypeDescription,
                                    RequiredStatus = res.Key.RequiredStatus,
                                    PrintCount = ???
                                }
                            ).ToList();
int cnt = guardiandocsrequired.Count;
return guardiandocsrequired;