LINQ枢轴前5行到列

期望的输出是一组行，其中包含有关医生的详细信息以及他们执行的前5个程序(按数量计算)，这些程序不在我们的基准测试表中-每月至少一次。即使我有限的LINQ经验，我已经设法让它工作，但性能很糟糕。本着在LINQ上做得更好的精神，我想看看是否有更好的方法来解决这个问题(除了用SQL编码)。

这是整个方法，尽管我认为最后的语句(var report =)最值得注意。

//Get the Providers for the selected AccessLevel & Report Period
var providerRiskLevels = GetProviderRiskLevelForAccessLevel(userAccessLevelId, reportPeriodId, providerNamePiece);
short benchmarkYear = (
        from rp in db.ReportPeriods
        where rp.ReportPeriodId == reportPeriodId
        select rp.BenchmarkDataYear
        ).FirstOrDefault();
byte Duration = (
        from rp in db.ReportPeriods
        where rp.ReportPeriodId == reportPeriodId
        select rp.Duration
        ).FirstOrDefault();
//Summarize counts by procedure code (ignoring modifiers)
var codeCounts =
    from pp in db.ProviderProductions
    //332 - restrict to valid codes
    join pc in db.ProcedureCodes on pp.ProcedureCode equals pc.ProcedureCode1
    where pp.ReportPeriodId == reportPeriodId
        //show deleted codes as potential issue
        && pc.TerminatedDate == null
        && (codeFilter == null || pp.ProcedureCode == codeFilter)
    join pc in db.PspsProcedures
        on new { col1 = pp.ProcedureCode, col2 = benchmarkYear } equals new { col1 = pc.ProcedureCode, col2 = pc.Year } into left
    from l_d in left.DefaultIfEmpty()
    where l_d == null
    group pp by
        new { pp.ProviderId, pp.ProcedureCode }
        into g
        orderby g.Key.ProviderId, g.Sum(x => x.Volume) descending
        where g.Sum(x => x.Volume) > Duration
        select new NewCodesEntity
        {
            ProviderId = g.Key.ProviderId,
            ProcedureCode = g.Key.ProcedureCode,
            Volume = g.Sum(x => x.Volume)
        };
//Get top 5 procedures performed by provider
var newCodes =
    from cc in codeCounts
    group cc by
        new { cc.ProviderId }
        into g
        select new ProviderNewCodesEntity
        {
            ProviderId = g.Key.ProviderId,
            Codes = g.OrderByDescending(x => x.Volume).Take(5).ToList()
        };
//Build the report
var report =
    from pr in providerRiskLevels
    join nc in newCodes on pr.Provider.ProviderId equals nc.ProviderId
    where pr.RiskCategoryId == RiskCategoryIds.VisibleRisk
        && (filterRiskLevelNums.Contains(pr.RiskLevelNum))
        && (filterSpecialtyId == 0 || pr.Provider.Specialty.SpecialtyId == filterSpecialtyId)
    select new ProbeAuditEntity
    {
        ProviderId = pr.Provider.ProviderId,
        ProviderName = pr.Provider.Name,
        ProviderCode = pr.Provider.ProviderCode,
        SpecialtyName = pr.Provider.Specialty.Name,
        SpecialtyCode = pr.Provider.Specialty.SpecialtyCode,
        VisibleRisk = pr.RiskScore,
        NewCode1 = nc.Codes.OrderByDescending(c => c.Volume).FirstOrDefault().ProcedureCode,
        NewCode2 = nc.Codes.OrderByDescending(c => c.Volume).Skip(1).FirstOrDefault().ProcedureCode,
        NewCode3 = nc.Codes.OrderByDescending(c => c.Volume).Skip(2).FirstOrDefault().ProcedureCode,
        NewCode4 = nc.Codes.OrderByDescending(c => c.Volume).Skip(3).FirstOrDefault().ProcedureCode,
        NewCode5 = nc.Codes.OrderByDescending(c => c.Volume).Skip(4).FirstOrDefault().ProcedureCode
    };
return report;

谢谢你的建议。

//Build the report
var report =
    (from pr in providerRiskLevels
    join nc in newCodes on pr.Provider.ProviderId equals nc.ProviderId
    where pr.RiskCategoryId == RiskCategoryIds.VisibleRisk
        && filterRiskLevelNums.Contains(pr.RiskLevelNum)
        && (filterSpecialtyId == 0 
            || pr.Provider.Specialty.SpecialtyId == filterSpecialtyId)
    select new 
    {
        RiskLevel = pr,
        NewCodes = nc.Codes.OrderByDescending(c => c.Volumn).Select(c => c.ProcedureCode).Take(5)
    })
    .AsEnumerable()
    .Select(x => new ProbeAuditEntity
    {
        ProviderId = x.RiskLevel.Provider.ProviderId,
        ProviderName = x.RiskLevel.Provider.Name,
        ProviderCode = x.RiskLevel.Provider.ProviderCode,
        SpecialtyName = x.RiskLevel.Provider.Specialty.Name,
        SpecialtyCode = x.RiskLevel.Provider.Specialty.SpecialtyCode,
        VisibleRisk = x.RiskLevel.RiskScore,
        NewCodes = x.NewCodes.Concat(Enumerable.Repeat(<DefaultProcedureCodeHere>, 5)).Take(5)
    });

//Build the report
var report =
    (from pr in providerRiskLevels
    join nc in newCodes on pr.Provider.ProviderId equals nc.ProviderId
    where pr.RiskCategoryId == RiskCategoryIds.VisibleRisk
        && filterRiskLevelNums.Contains(pr.RiskLevelNum)
        && (filterSpecialtyId == 0 
            || pr.Provider.Specialty.SpecialtyId == filterSpecialtyId)
    select new ProbeAuditEntity
    {
        ProviderId = x.RiskLevel.Provider.ProviderId,
        ProviderName = x.RiskLevel.Provider.Name,
        ProviderCode = x.RiskLevel.Provider.ProviderCode,
        SpecialtyName = x.RiskLevel.Provider.Specialty.Name,
        SpecialtyCode = x.RiskLevel.Provider.Specialty.SpecialtyCode,
        VisibleRisk = x.RiskLevel.RiskScore,
        NewCodes = nc.Codes.OrderByDescending(c => c.Volumn).Select(c => c.ProcedureCode).Take(5)
    })
    .ToList();
report
.ForEach(x => x.NewCodes = x.NewCodes.Concat(Enumerable.Repeat(<DefaultProcedureCodeHere>, 5)).Take(5);