尝试在 Lambda-LinQ 中重写 SQL

Question

我正在将一些SQL脚本迁移到Lambda中，但我还不知道如何翻译这个SQL

SELECT
    COUNT([ProcessID]) AS [Count],
    [ProcessType],
    AVG(DATEDIFF(s, [StartDate], [EndDate])) AS [AverageDuration]
FROM
    [ProcessTable]
WHERE
    [InstanceID] = @InstanceID
    AND [ProcessType] = @ProcessType
    AND [ProcessStatus] = 3 -- Finished
GROUP BY
    [ProcessType]

编辑：我尝试了以下内容，但它并不完全了解平均的工作原理

var result = (from p in ctx.ProcessTable
              where p.ProcessInstanceID.Equals(processInstanceID)
                 && p.ProcessType.Equals(processType)
                 && p.ProcesoStatusEquals(ProcessStatusEnum.FINISHED)
              group p by p.ProcessType into tp
              select new 
              {
                  Count = tp.Count(tp.ProcessID),
                  AverageDuration = tp.Average(p => p.EndDate - p.StarDate)
                  ProcesType = tp.ProcesType
              });

任何帮助将不胜感激。提前致谢

Answer 1

经过一番尝试，我想通了。我回答自己，以防有人感兴趣...

class ProcessResult
{
    public int Count { get; set; }
    public double AverageDuration { get; set; }
    public string ProcessType { get; set; }
}
var result = (from p in ctx.ProcessTable
              where p.ProcessInstanceID.Equals(processInstanceID)
                 && p.ProcessType.Equals(processType)
                 && p.ProcesoStatusEquals(ProcessStatusEnum.FINISHED)
              group p by p.ProcessType into tp
              select new ProcessResult
              {
                  Count = tp.Count(),
                  AverageDuration = tp.Average(p => DbFunctions.DiffSeconds(p.StartDate, p.EndDate)),
                  ProcessType = tp.Key
              }).SingleOrDefault();