question

50411600 avatar image
0 Votes"
50411600 asked TimonYang-MSFT answered

SqlToLinq转换问题

 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;


dotnet-csharpsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@50411600
Microsoft Q&A is an English forum, so please post in English when posting questions in the future so that more members can participate.
You can click the gear button in the upper right corner of the post to modify the post, and then you can post a short comment to remind us that you have made the changes.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

 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( );
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.