How do I translate this specifik SQL statement to LINQ-CodePudding

SQL:

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# Objects:

Monitor:

class Monitor {

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

And

Counter:

public class Counter{

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

And the objects that I need to turn it into is:

AllStatistics:

[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;}
}

I don't have much experience with LINQ so the most I've achieved with it is somethig like:

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

My question is, how would I compose an LINQ statement with the same functionality of the above SQL query ?

CodePudding user response：

One way would be to use keyless entities instead:

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
        });

// ...

So something along these lines:

[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
    });

CodePudding user response：

It looks like you need something like this

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 = counters.Count(),
        today = counters.Count(c =>
            c.time_stamp >= DateTime.UtcNow.Date &&
            c.time_stamp < DateTime.UtcNow.Date.AddDays(1)
          ),
        current_hour = 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();