c# - 分组 2 个表，计算值，然后将结果保存在字典中

Question

所以我有一个 SQL 语句看起来像这样

SELECT T1.NAME, COUNT(T2.VALUE) AS numInstances
FROM TABLE2 T2 LEFT OUTER JOIN
     TABLE1 T1 on T2.NAME_ID = T1.NAME_ID
WHERE (T2.DATE BETWEEN to_date('01-Aug-2011', 'dd-mon-yyyy') 
      AND to_date('31-Aug-2011' , 'dd-mon-yyyy')) AND T2.VALUE = 1))
GROUP BY T1.NAME

此语句查找 2 个表中何时匹配的名称，然后查找 8 月份的所有“1”值(这些与病假、工作、休息日等相关)，然后计算有多少每个我都有。此 SQL 语句效果很好，但我在 C# 中使用 MVC .NET，需要将其作为生成字典的 LINQ 语句。

所以我希望字典看起来像，

NAME   VALUECOUNT
John   8
Joe    1
Eric   0

我试过了

Dictionary<string,int> results =
(from t2 in db.table2.Where(t2 => m.Value == 1)
 from t1 in db.table1
 where(t2.DATE >= new DateTime(2011,8,1) && t2.DATE <= new DateTme(2011,8,31)
 orderby t1.NAME
 group new{T2, T1} by new {t2.VALUE, t1.NAME} into g
 select new { 
   new KeyValuePair<string,int>(
      g.Key.NAME,
     (int)g.sum(g => g.Key.Value))
}).AsEnumerable().ToDictionary();

想法？

Answer 1

using(DbEntities db = new DbEntities())
{
  var fromDate = new DateTime(2011,8,1);
  var toDate = new DateTime(2011,8,31);
  var dictionary =
            (from t1 in db.TABLE1
            join t2 in db.TABLE2.Where(x => x.VALUE == 1 && x.DATE >= fromDate && x.DATE <= toDate)
            on t1.NAME_ID equals t2.NAME_ID into t2_j
            from t2s in t2_j.DefaultIfEmpty()
            group t2s by t1.NAME into grouped
            select new { Name = grouped.Key, Count = grouped.Sum(x => x.VALUE) }).
            Where(x => x.Count.HasValue).
            ToDictionary(o => o.Name,o => o.Count);
   return dictionary;

}