c# - 使用 LINQ 加入和转置多个列表

Question

您好，我有以下代码可以返回正确的数据，但似乎必须有更好的方法来组合 3 个列表，基于它们的公共(public)字段并将结果转置到给定类型的新列表中使用 LINQ，而不是最后求助于 foreach。有什么想法吗？

  public IEnumerable<StagSummaryByCflHistoricalItem> GetSummaryByCflHistorical(DateTime currentDate)
  {
     var allRecords =
        this.preGrantSummaryHistoricalRepository
           .AllWithFetch(this.preGrantSummaryHistoricalRepository.All, x => x.CaseFileLocation)
           .Where(
           x => x.Date >= currentDate.FirstDayOfQuarterFromDateTime()
           && x.Date <= currentDate.LastDayOfQuarterFromDateTime())
           .ToList();

     var summaryForQuarter =
        allRecords.GroupBy(x => new { x.CaseFileLocation.Id, x.CaseFileLocation.Name }).Select(
           x =>
           new
              {
                 CaseFileLocationId = x.Key.Id,
                 Description = x.Key.Name,
                 TotalCasesEnteredCfl = x.Sum(y => y.TotalCasesEntered),
                 TotalNetFeeEnteredCfl = x.Sum(y => y.TotalNetFeeEntered),
                 TotalCasesLeftCfl = x.Sum(y => y.TotalCasesLeft),
                 TotalNetFeeLeftCfl = x.Sum(y => y.TotalNetFeeLeft)
              })
              .OrderBy(x => x.CaseFileLocationId)
              .ToList();

     var summaryForMonth =
        allRecords.Where(x => x.Date >= currentDate.FirstDayOfMonthFromDateTime())
        .GroupBy(x => new { x.CaseFileLocation.Id, x.CaseFileLocation.Name }).Select(
           x =>
           new
              {
                 CaseFileLocationId = x.Key.Id,
                 Description = x.Key.Name,
                 TotalCasesEnteredCfl = x.Sum(y => y.TotalCasesEntered),
                 TotalNetFeeEnteredCfl = x.Sum(y => y.TotalNetFeeEntered),
                 TotalCasesLeftCfl = x.Sum(y => y.TotalCasesLeft),
                 TotalNetFeeLeftCfl = x.Sum(y => y.TotalNetFeeLeft)
              })
              .OrderBy(x => x.CaseFileLocationId)
              .ToList();

     var summaryForWeek =
        allRecords.Where(x => x.Date >= currentDate.FirstDayOfWeekFromDateTime(DayOfWeek.Monday)).GroupBy(
           x => new { x.CaseFileLocation.Id, x.CaseFileLocation.Name }).Select(
              x =>
              new
                 {
                    CaseFileLocationId = x.Key.Id,
                    Description = x.Key.Name,
                    TotalCasesEnteredCfl = x.Sum(y => y.TotalCasesEntered),
                    TotalNetFeeEnteredCfl = x.Sum(y => y.TotalNetFeeEntered),
                    TotalCasesLeftCfl = x.Sum(y => y.TotalCasesLeft),
                    TotalNetFeeLeftCfl = x.Sum(y => y.TotalNetFeeLeft)
                 })
                 .OrderBy(x => x.CaseFileLocationId)
                 .ToList();

     var finalList = summaryForQuarter
        .Select(x => new StagSummaryByCflHistoricalItem()
           {
              CaseFileLocationId = x.CaseFileLocationId,
              Description = x.Description,
              QuarterTotalCasesEnteredCfl = x.TotalCasesEnteredCfl,
              QuarterTotalCasesLeftCfl = x.TotalCasesLeftCfl,
              QuarterTotalNetFeeEnteredCfl = x.TotalNetFeeEnteredCfl,
              QuarterTotalNetFeeLeftCfl = x.TotalNetFeeLeftCfl
           })
           .OrderBy(x => x.CaseFileLocationId)
           .ToList();

     foreach (var qrt in finalList)
     {

        var mnthData = summaryForMonth.FirstOrDefault(x => x.CaseFileLocationId == qrt.CaseFileLocationId);

        if (mnthData != null)
        {
           qrt.MonthTotalCasesEnteredCfl = mnthData.TotalCasesEnteredCfl;
           qrt.MonthTotalCasesLeftCfl = mnthData.TotalCasesLeftCfl;
           qrt.MonthTotalNetFeeEnteredCfl = mnthData.TotalNetFeeEnteredCfl;
           qrt.MonthTotalNetFeeLeftCfl = mnthData.TotalNetFeeLeftCfl;
        }

        var weekData = summaryForWeek.FirstOrDefault(x => x.CaseFileLocationId == qrt.CaseFileLocationId);
        if (weekData == null)
        {
           continue;
        }
        qrt.WeekTotalCasesEnteredCfl = weekData.TotalCasesEnteredCfl;
        qrt.WeekTotalCasesLeftCfl = weekData.TotalCasesLeftCfl;
        qrt.WeekTotalNetFeeEnteredCfl = weekData.TotalNetFeeEnteredCfl;
        qrt.WeekTotalNetFeeLeftCfl = weekData.TotalNetFeeLeftCfl;
     }

     return finalList;
  }

注意:我有意首先将整个季度的数据作为列表获取，然后对其进行操作以计算月度和季度总计，但这主要是因为我无法理解从组合的 LINQ 中获得最终结果的方法IQuerable.

我正在使用 NHibernate、LINQ 方法语法、存储库模式和 SQL Server 2008

Answer 1

如果我没看错你的代码，你就是在将你的结果投影到具有相同结果成员的匿名类型，这使得将你的结果连接在一起变得非常容易。我最近在 Union 做了类似的事情。这是我刚刚编写的一个简化示例来演示:

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;

namespace ConsoleApplication1 {
    public class Month {
        public int MonthID { get; set; }
        public string MonthName { get; set; }
        public int NoDays { get; set; }
    }

    internal class Program {
        private static void Main(string[] args) {
            // Build up months
            var months = new List<Month>();
            for (var i = 1; i <= 12; i++) {
                months.Add(new Month {
                    MonthID = i,
                    MonthName = DateTimeFormatInfo.CurrentInfo.GetMonthName(i),
                    NoDays = DateTime.DaysInMonth(2012, i)
                });
            }

            var w = months.Select(m => new {
                m.MonthName
            });

            var x = months.Select(m => new {
                m.MonthName
            });

            var y = months.Select(m => new {
                m.MonthName
            });

            var z = w.Union(x).Union(y);

            foreach (var m in z) {
                Console.WriteLine(m.MonthName);
            }
            Console.Read();
        }
    }
}

请记住，“联合”(如 SQL UNION 子句)将从您的列表中删除任何重复项。如果您不想删除重复项(即执行“联合所有”)，请按如下方式使用“Concat”:

var z = w.Concat(x).Concat(y);