如何將此特定SQL陳述句轉換為LINQ

查詢陳述句：

SELECT
  monitors.monitor_name,
  alltime       = ISNULL(SUM(count), 0),
  today         = ISNULL(SUM(CASE WHEN time_stamp >= CAST(CAST(GETUTCDATE() AS date) AS datetime)
                                   AND time_stamp < CAST(DATEADD(day, 1, CAST(GETUTCDATE() AS date)) AS datetime)
                                  THEN count END), 0),
  current_hour  = ISNULL(SUM(CASE WHEN time_stamp >= DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE()), '20000101')
                                   AND time_stamp <  DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE())   1, '20000101')
                                  THEN count END), 0)
FROM CollectorOptions monitors
JOIN CollectorCounters counters ON counters.monitor_name= monitors.monitor_name
WHERE monitors.is_active = 1
GROUP BY
  monitors.monitor_name;

C# 物件：

監視器：

class Monitor {

public string monitor_name{ get; set;}
public bool is_active {get;set;}
}

和

柜臺：

public class Counter{

public string monitor_name { get; set;}
public DateTimeOffset time_stamp { get; set;}
public int count { get; set;}
}

我需要把它變成的物件是：

所有統計：

[Keyless]
public class AllStatistics{

 public string monitor_name {get;set;}
 public int alltime {get;set;}
 public int today {get;set;}
 public int current_hour {get;set;}
}

我對 LINQ 沒有太多經驗，所以我用它取得的最大成就是：

var list = (from MyDbContext.Monitors
            select monitor_name).Where(monitor => monitor.is_active != 0).ToList();

我的問題是，我將如何撰寫具有與上述 SQL 查詢相同功能的 LINQ 陳述句？

uj5u.com熱心網友回復：

看起來你需要這樣的東西

var list = (
    from monitor in MyDbContext.Monitors
    where monitor.is_active != 0
    select new {
        monitor.MonitorName,
        counters = MyDbContext.CollectorCounters.Where(counter => monitor.monitor_name  == counter.monitor_name)
    } into mc
    select new {
        mc.MonitorName,
        alltime = mc.counters.Count(),
        today = mc.counters.Count(c =>
            c.time_stamp >= DateTime.UtcNow.Date &&
            c.time_stamp < DateTime.UtcNow.Date.AddDays(1)
          ),
        current_hour = mc.counters.Count(c =>
            c.time_stamp >=
             new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, 0, 0) &&
            c.time_stamp < 
             new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, 0, 0).AddHours(1)
          ),
    }
).ToList();

uj5u.com熱心網友回復：

一種方法是使用無鑰匙物體：

https://docs.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations

MyKeylessEntity.cs

[Keyless]
public class MyKeylessEntity{ ... } 

MyDbContext.cs

public DbSet<MyKeylessEntity> MyKeylessEntity{ get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<MyKeylessEntity>(e =>
    {
            e.HasNoKey()
                .ToSqlQuery(@"

                            ")
                .ToView(null); // Workaround for EF core migrations b.u.g => fixed in net core 5
        });

// ...

所以沿著這些路線：

[Keyless]
public class AllStatistics
{
 public string monitor_name {get;set;}
 public int alltime {get;set;}
 public int today {get;set;}
 public int current_hour {get;set;}
}

public DbSet<AllStatistics> AllStatistics{ get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<AllStatistics>(e =>
    {
        e.HasNoKey()
            .ToSqlQuery(@"
                        SELECT
                            monitors.monitor_name,
                            alltime       = ISNULL(SUM(count), 0),
                            today         = ISNULL(SUM(CASE WHEN time_stamp >= CAST(CAST(GETUTCDATE() AS date) AS datetime)
                                                    AND time_stamp < CAST(DATEADD(day, 1, CAST(GETUTCDATE() AS date)) AS datetime)
                                                    THEN count END), 0),
                            current_hour  = ISNULL(SUM(CASE WHEN time_stamp >= DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE()), '20000101')
                                                    AND time_stamp <  DATEADD(hour, DATEDIFF(hour, '20000101', GETUTCDATE())   1, '20000101')
                                                    THEN count END), 0)
                        FROM 
                            CollectorOptions monitors
                        JOIN CollectorCounters counters ON counters.monitor_name= monitors.monitor_name
                        WHERE monitors.is_active = 1
                        GROUP BY monitors.monitor_name;
                        ")
            .ToView(null); // Workaround for EF core migrations b.u.g => fixed in net core 5
    });

標籤：C＃ sql 。网 林克

上一篇：如何將預發布NuGet包的最新版本還原為AzureDevOps構建的一部分

下一篇：System.IO.Path.Combine()的奇怪行為