SqlToLinq转换问题

建华 何 21 Reputation points
2021-09-24T07:23:23.943+00:00
public class AlarmRecord : Entity
{
/// <summary>
/// 区域编号
/// </summary>
public virtual string AreaId { get; set; }

    /// <summary>
    /// 报警开始时间
    /// </summary>
    public virtual DateTime BeginTime { get; set; }

    /// <summary>
    /// 报警结束时间
    /// </summary>
    public virtual DateTime? EndTime { get; set; }

}

集合如下

static List<AlarmRecord> GetAlarms()
{
var alarms = new List<AlarmRecord>
{
new AlarmRecord(){BeginTime = DateTime.Now, EndTime = DateTime.Now.AddSeconds(30),AreaId = "FS"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-30), EndTime = DateTime.Now.AddSeconds(30),AreaId = "FS"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-20), EndTime = DateTime.Now.AddSeconds(30),AreaId = "FSV"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-23), EndTime = DateTime.Now.AddSeconds(30),AreaId = "FSV"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-22), EndTime = DateTime.Now.AddSeconds(30),AreaId = "CP"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-10), EndTime = DateTime.Now.AddSeconds(30),AreaId = "GYGC"}
};
return alarms;
}

我需要统计 最近7天各区域报警数量变化,请问下面的sql脚本如果转换成linq或Lamda?我转了几次运行都报错,提示无法翻译linq/lamda语句

SELECT CONVERT(VARCHAR(10), BeginTime, 120) AS DayDate ,
COUNT(CASE WHEN ( AreaID = 'FS' ) THEN 1
END) AS Fs4Count ,
COUNT(CASE WHEN ( AreaID = 'FSV' ) THEN 1
END) AS Fs5Count ,
COUNT(CASE WHEN ( AreaID = 'CA' ) THEN 1
END) AS CpCount ,
COUNT(CASE WHEN ( AreaID = 'Gygc' ) THEN 1
END) AS GCount
FROM dbo.AlarmRecord
WHERE BeginTime >= '2021-07-01 00:00:00'
AND BeginTime < '2021-07-08 00:00'
GROUP BY CONVERT(VARCHAR(10), BeginTime, 120)
ORDER BY DayDate;
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,638 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

Accepted answer
  1. Viorel 114.7K Reputation points
    2021-09-24T08:37:15.123+00:00
    DateTime begin = . . .
    DateTime end = . . .
    
    var result = GetAlarms( )
        .Where( a => a.BeginTime.Date >= begin.Date && a.BeginTime.Date < end.Date )
        .GroupBy( a => a.BeginTime.Date )
        .Select( g =>
            new
            {
                DayDate = g.Key.Date,
                Fs4Count = g.Count( v => v.AreaId == "FS" ),
                Fs5Count = g.Count( v => v.AreaId == "FSV" ),
                CpCount = g.Count( v => v.AreaId == "CA" ),
                GCount = g.Count( v => v.AreaId == "GYGC" )
            } )
        .ToList( );
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Timon Yang-MSFT 9,586 Reputation points
    2021-09-24T08:39:20.62+00:00

    I tested your sql and converted it to equivalent linq code, please try if it can work for you.

                List<AlarmRecord> alarmRecords = GetAlarms();  
      
                var re = (from record in alarmRecords  
                         where record.BeginTime >= DateTime.Parse("2021-07-01")  
                         where record.BeginTime < DateTime.Parse("2021-07-08")  
                         group record by record.BeginTime into gcs  
                         select new  
                         {  
                             DayDate = gcs.Key,  
                             Fs4Count = gcs.Where(r=>r.AreaId == "FS").Count(),  
                             Fs5Count = gcs.Where(r=>r.AreaId == "FSV").Count(),  
                             CpCount = gcs.Where(r=>r.AreaId == "CP").Count(),  
                             GCount = gcs.Where(r=>r.AreaId == "GYGC").Count()  
                         }).OrderBy(r=>r.DayDate);  
    

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments