我是新来的LINQ to SQL和我想知道如何实现这样的事情在LINQ：

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.

推荐答案

我不太清楚哪里的 Criteria.Period 的价值来自于您的示例查询。

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

不过，我想你想读这两个员工和终止所有可用个月（然后你就可以轻松地将其过滤）。您的查询可能出问题，如果第一个表（终止）没有包括某些特定月份的所有记录（说这可能）。那么选择子句将不会被调用，五一为所有的参数，即使你不得不在第二个表中的一些数据（重新presenting聘用） ，那么你将无法找到它。

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.

这可以优雅地使用 concat方法解决（请参阅MSDN样本）。可以按月份选择所有termniations和所有员工（成某种类型的数据结构），然后组内的所有数据：

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

当在code现在看，我是pretty的肯定有一些更短的方式来写这个。另一方面，此code应该相当可读，这是一个优势。还注意到，它并不重要，我们分裂code成几个子查询。由于LINQ的懒惰evalutation到SQL，这应该被执行作为一个单一的查询。

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.