How do I translate this specifik SQL statement to LINQ

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 ?

Answer

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 = mc.Sum(c => c.count),
        today = mc.counters.Where(c =>
            c.time_stamp >= DateTime.UtcNow.Date &&
            c.time_stamp < DateTime.UtcNow.Date.AddDays(1)
          ).Sum(c => c.count),
        current_hour = mc.counters.Where(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)
          ).Sum(c => c.count),
    }
).ToList();