linq aggregation

Question

Say I have classes like:

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

I want to return data in for format:

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

Amount invoiced is a total of all the (NetPrices + VatAmounts) + (LabourNet + LabourVat) net a for a month and money received is a total of all where IsPaid = true. I'm having trouble getting the linq query correct for this report. I'm not sure where to have my grouping and when to perform the Sum.

The linq query that I have at the moment is as follows, but I'm not getting anything near what I want at the moment. Anyone with some linqy goodness?

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

OK, I've had to break this down into a few sub queries to get it working. There very may well be a better and more efficient way of doing this, but I have a linq solution for the moment. Not including the code for getting the Money Received as it's very similar to the other code except filtering on IsPaid = true.

It's slightly different in my environment as I'm using LLBL Gen Pro, but you can get the basic gist from this code:

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