LINQ group by month question

Question

I'm new to LINQ to SQL and I would like to know how to achieve something like this in LINQ:

        Month   Hires  Terminations   
        Jan      5       7
        Feb      8       8
        Marc     8       5

I've got this so far, and I think there is something wrong with it but I'm not sure:

from term1 in HRSystemDB.Terminations
group term1 by new { term1.TerminationDate.Month, term1.TerminationDate.Year } into grpTerm
select new HiresVsTerminationsQuery
{
  Date = Criteria.Period,
  TerminationsCount = grpTerm.Count(term => term.TerminationDate.Month == Criteria.Period.Value.Month),
  HiresCount = (from emp in HRSystemDB.Persons.OfType<Employee>()
               group emp by new { emp.HireDate.Month, emp.HireDate.Year } into grpEmp
               select grpEmp).Count(e => e.Key.Month == Criteria.Period.Value.Month)
});

Thanks in advance.

Answer 1

I'm not quite sure where does the Criteria.Period value come from in your sample query.

However I think you're trying to read both hires and terminations for all available months (and then you can easily filter it). Your query could go wrong if the first table (Termination) didn't include any records for some specified month (say May). Then the select clause wouldn't be called with "May" as the parameter at all and even if you had some data in the second table (representing Hires), then you wouldn't be able to find it.

This can be elegantly solved using the Concat method (see MSDN samples). You could select all termniations and all hires (into a data structure of some type) and then group all the data by month:

var terms = from t in HRSystemDB.Terminations 
            select new { Month = t.TerminationDate.Month, 
                         Year = term1.TerminationDate.Year,
                         IsHire = false };
var hires = from emp in HRSystemDB.Persons.OfType<Employee>() 
            select new { Month = emp.HireDate.Month, 
                         Year = emp.HireDate.Year 
                         IsHire = true };

// Now we can merge the two inputs into one
var summary = terms.Concat(hires);

// And group the data using month or year
var res = from s in summary 
          group s by new { s.Year, s.Month } into g
          select new { Period = g.Key, 
                       Hires = g.Count(info => info.IsHire),  
                       Terminations = g.Count(info => !info.IsHire) }

When looking at the code now, I'm pretty sure there is some shorter way to write this. On the other hand, this code should be quite readable, which is a benefit. Also note that it doesn't matter that we split the code into a couple of sub-queries. Thanks to lazy evalutation of LINQ to SQL, this should be executed as a single query.

Answer 2

I don't know if it shorter but you can also try this version to see if it works better with your server. I don't know exactly how these two answers turn into SQL statements. One might be better based on your indexs and such.

var terms = 
    from t in Terminations
    group t by new {t.Month, t.Year} into g
    select new {g.Key, Count = g.Count()};

var hires = 
    from p in Persons
    group p by new {p.Month, p.Year} into g
    select new {g.Key, Count = g.Count()};

var summary = 
    from t in terms
    join h in hires on t.Key equals h.Key
    select new {t.Key.Month, t.Key.Year, 
        Hires = h.Count, Terms = t.Count};