将两个表分组，计算值，然后将结果保存在字典中

本文关键字：然后 结果 保存 字典 存在 计算 两个 | 更新日期: 2023-09-27 18:10:50

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

该语句在两个表中查找要匹配的名称，然后找到8月份的所有'1'值(这些值与病假、工作、休假等有关)，然后计算我拥有的每个值的数量。这个SQL语句工作得很好，但我在c#中使用MVC . net，需要这是一个LINQ语句，生成一个字典。

所以我想让Dictionary看起来像

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

想法?

将两个表分组，计算值，然后将结果保存在字典中

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;
}
相关文章：