LINQ group by month question

Question

I am new to LINQ to SQL, and I would like to know how to achieve something similar in LINQ:

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

I still have it, and I think that something is wrong with him, 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 the value Criteria.Periodappears in your sample request.

, ( ). , (Termination) (, ). select "" , ( Hires), .

Concat (. MSDN). ( ), :

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

, , . , , . , , . evalutation LINQ to SQL .

Answer 2

, , , , . , SQL-. , ..

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