linq聚合

Question

假设我有这样的课程：

    public class ServiceCall 
    { 
        public int ServiceCallID {get; set;}
        public DateTime ReportedTimestamp {get; set;}
        public bool IsPaid {get; set;}
        public decimal LabourNet { get; set;}
        public decimal LabourVat {get; set;}
    }
    public class UsedPart
    {
        public int UsedPartID { get; set; }
        public decimal NetPrice { get; set; }
        public decimal VatAmount {get; set;}
    }

我想返回以下格式的数据：

Month    #CallsReceived   AmountInvoiced    MoneyReceived
Jan 2009 202              €32050.20         €29200.00
Feb 2009 213              €35050.20         €34200.00

发票金额是一个月内所有 (NetPrices + VatAmounts) + (LabourNet + LabourVat) net a 的总和，收到的钱是所有 IsPaid = 的总和真的。 我无法正确获取此报告的 linq 查询。我不确定在哪里进行分组以及何时执行求和。

我目前的 linq 查询如下，但目前我没有得到任何接近我想要的结果。有人有一些 linqy 善良吗？

var q = from s in ServiceCalls
 join up in UsedParts on s.ServiceCallID equals up.ServiceCallID into tmp
 from nullablePart in tmp.DefaultIfEmpty()
 group s by s.ReportedTimestamp.Month
 into grp
 select new 
  { 
   Month = grp.Key,
   CallsReceived = grp.Count(),
   AmountInvoiced = grp.Sum(s => s.UsedParts.Sum(p => p.NetPrice)),
  };

Answer 1

好的，我必须将其分解为几个子查询才能使其正常工作。很可能有更好、更有效的方法来做到这一点，但我目前有一个 linq 解决方案。不包括获取收到款项的代码，因为它与其他代码非常相似，除了 IsPaid = true 上的过滤之外。

由于我使用的是 LLBL Gen Pro，因此在我的环境中略有不同，但您可以从此代码中获取基本要点：

// Get the number of calls by month
var callsCount = from s in ServiceCalls group s by new DateTime(s.ReportedTimestamp.Year, s.ReportedTimestamp.Month, 1) into grp select new { ReportDate = grp.Key, Count = grp.Count()};
//callsCount.Dump();

// Get the labour on all calls by month
var serviceCallLabour = from s in ServiceCalls 
    select new 
    { 
        ReportDate = new DateTime(s.ReportedTimestamp.Year, s.ReportedTimestamp.Month, 1), 
        IsPaid = s.IsPaid, 
        GrossLabour = s.LabourChargeNet + s.LabourChargeVat 
    } 
    into labour
    group labour by labour.ReportDate into grp
    select new  {ReportDate = grp.Key, IsPaid = grp.Select(l => l.IsPaid).First(), GrossLabour = grp.Sum(l => l.GrossLabour) };
//serviceCallLabour.Dump();

// Get the value of parts used on all calls by month
var serviceCallParts = from s in ServiceCalls 
    join up in UsedParts on s.ServiceCallID equals up.ServiceCallID into tmp
    from np in tmp.DefaultIfEmpty()
    select new 
    { 
        ReportDate = new DateTime(s.ReportedTimestamp.Year, s.ReportedTimestamp.Month, 1),
        GrossPart = np != null ? (np.NetPrice + np.VatAmount) : 0
    }
    into callParts
    group callParts by callParts.ReportDate into grp
    select new { ReportDate = grp.Key, GrossPart = grp.Sum(p => p.GrossPart) };
//serviceCallParts.Dump();

var results = from l in serviceCallLabour
                join p in serviceCallParts on l.ReportDate equals p.ReportDate
                join c in callsCount on l.ReportDate equals c.ReportDate                
                select new { ReportDate = l.ReportDate, CallsReceived = c.Count, AmountInvoiced = l.GrossLabour + p.GrossPart} into callAmounts
                group callAmounts by callAmounts.ReportDate into grp
                select new {Month = grp.Key, CallsReceived = grp.Select(c => c.CallsReceived).First(), AmountInvoiced = grp.Sum(c => c.AmountInvoiced)};

results.Dump();

Answer 2

我知道这个问题现在已经成为一个古老的历史，但这是我的答案。

var byMonth =
    from sc in ServiceCalls
    join up in UsedParts
        on sc.ServiceCallID equals up.ServiceCallID
        into gups
    let tsd = sc.ReportedTimestamp.Date
    let month = tsd.AddDays(1 - tsd.Day)
    group new
    {
        Calls = 1,
        sc.IsPaid,
        ChargeNet = sc.LabourNet + gups.Sum(gup => gup.NetPrice),
        ChargeVat = sc.LabourVat + gups.Sum(gup => gup.VatAmount),
    } by month into gscs
    select new
    {
        Month = gscs.Key,
        CallsReceived = gscs.Sum(gsc => gsc.Calls),
        AmountInvoiced
            = gscs.Sum(gsc => gsc.ChargeNet)
            + gscs.Sum(gsc => gsc.ChargeVat),
        MoneyReceived
            = gscs.Sum(gsc => gsc.IsPaid ? gsc.ChargeNet : 0m)
            + gscs.Sum(gsc => gsc.IsPaid ? gsc.ChargeVat : 0m),
    };

享受！