c# - 使用 groupby 将 SQL 转换为 lambda

Question

我正在尝试翻译这条sql语句

SELECT row, SUM(value) as VarSum, AVG(value) as VarAve, COUNT(value) as TotalCount 
FROM MDNumeric
WHERE collectionid = 6 and varname in ('C3INEV1', 'C3INEVA2', 'C3INEVA3', 'C3INVA11', 'C3INVA17', 'C3INVA19')
GROUP BY row

进入使用 lambda 表达式的 EF 4 查询，但遗漏了一些东西。

我有:

sumvars = sv.staticvararraylist.Split(',');

var aavresult = _myIFR.MDNumerics
    .Where(r => r.collectionid == _collid)
    .Where(r => sumvars.Contains(r.varname))
    .GroupBy(r1 =>r1.row)
    .Select(rg =>
          new
          {
              Row = rg.Key,
              VarSum = rg.Sum(p => p.value),
              VarAve = rg.Average(p => p.value),
              TotalCount = rg.Count()
          });

其中 staticvararraylist 具有字符串 'C3INEV1'、'C3INEVA2'、'C3INEVA3'、'C3INVA11'、'C3INVA17'、'C3INVA19'(不带单引号)和 _collid 变量 = 6。

虽然我得到了正确的分组，但我的总和、平均值和计数值不正确。

Answer 1

您没有发布错误消息，但我怀疑它与 Contains 有关。我发现 Any 也同样有效。

这应该让你非常接近:

var result =
    from  i in _myIFR.MDNumerics
    where i.collectionid == _collid && sumvars.Any(v => i.varname == v)
    group i by i.row into g
    select new {
        row = g.Key,
        VarSum = g.Sum(p => p.value),
        VarAve = g.Average(p => p.value),
        TotalCount = g.Count()
    };