T-SQL to Linq: converting the having datediff in a group by

Question

I have the following data set in SQL and I need to convert it from TSQL to LINQ in C#.

create table #sampledata 
(
    name nvarchar(50),
    sampletime datetime,
    samplevalue decimal, 
    block int
);

insert into #sampledata (name, sampletime, samplevalue, block) values
('ABC1235', cast('2016/01/01 10:00:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:05:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:10:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:15:00 AM' as datetime), null, 3),
('ABC1235', cast('2016/01/01 10:20:00 AM' as datetime), null, 3),
('ABC1235', cast('2016/01/01 10:25:00 AM' as datetime), null, 3),
('ABC1235', cast('2016/01/01 10:30:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:35:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:40:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:45:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:50:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 10:55:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:00:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:05:00 AM' as datetime), null, 10),
('ABC1235', cast('2016/01/01 11:10:00 AM' as datetime), null, 10),
('ABC1235', cast('2016/01/01 11:15:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:20:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:25:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:30:00 AM' as datetime), null, 13),
('ABC1235', cast('2016/01/01 11:35:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:40:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:45:00 AM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 11:50:00 AM' as datetime), null, 16),
('ABC1235', cast('2016/01/01 11:55:00 AM' as datetime), null, 16),
('ABC1235', cast('2016/01/01 12:00:00 PM' as datetime), 50.00, 0),
('ABC1235', cast('2016/01/01 12:05:00 PM' as datetime), null, 17),
('ABC1235', cast('2016/01/01 12:10:00 PM' as datetime), null, 17),
('ABC1235', cast('2016/01/01 12:15:00 PM' as datetime), null, 17),
('ABC1235', cast('2016/01/01 12:20:00 PM' as datetime), null, 17),
('ABC1235', cast('2016/01/01 12:25:00 PM' as datetime), null, 17),
('ABC1235', cast('2016/01/01 12:30:00 PM' as datetime), null, 17),
('ZYA4567', cast('2016/01/01 10:00:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:05:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:10:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:15:00 AM' as datetime), null, 3),
('ZYA4567', cast('2016/01/01 10:20:00 AM' as datetime), null, 3),
('ZYA4567', cast('2016/01/01 10:25:00 AM' as datetime), null, 3),
('ZYA4567', cast('2016/01/01 10:30:00 AM' as datetime), null, 3),
('ZYA4567', cast('2016/01/01 10:35:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:40:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:45:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:50:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 10:55:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:00:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:05:00 AM' as datetime), null, 9),
('ZYA4567', cast('2016/01/01 11:10:00 AM' as datetime), null, 9),
('ZYA4567', cast('2016/01/01 11:15:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:20:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:25:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:30:00 AM' as datetime), null, 12),
('ZYA4567', cast('2016/01/01 11:35:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:40:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:45:00 AM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 11:50:00 AM' as datetime), null, 15),
('ZYA4567', cast('2016/01/01 11:55:00 AM' as datetime), null, 15),
('ZYA4567', cast('2016/01/01 12:00:00 PM' as datetime), 50.00, 0),
('ZYA4567', cast('2016/01/01 12:05:00 PM' as datetime), null, 16),
('ZYA4567', cast('2016/01/01 12:10:00 PM' as datetime), null, 16),
('ZYA4567', cast('2016/01/01 12:15:00 PM' as datetime), null, 16),
('ZYA4567', cast('2016/01/01 12:20:00 PM' as datetime), null, 16),
('ZYA4567', cast('2016/01/01 12:25:00 PM' as datetime), 40.00, 0),
('ZYA4567', cast('2016/01/01 12:30:00 PM' as datetime), 50.00, 0)

This the the T-SQL I am using to run against this data set

select 
    name,
    min(sampletime) as startdate, 
    max(sampletime) as enddate
from #sampledata where samplevalue is null
group by
    name, block
having datediff(minute, min(sampletime), max(sampletime)) >= 5
order by name, startdate

The grouping was quite easy to convert however I am not sure how to tackle the following the having clause and more specifically how to deal with datediff in c# using aggregates

having datediff(minute, min(sampletime), max(sampletime)) >= 5

This is what I have so far:

var query = (from s in sampledata
                         where s.block.HasValue == false
                         group b by new { Name = s.Name, Block = b.Block     } into g
                         select new
                         {
                             Name = g.Name,
                             StartDate = g.Min(a => a.SampleTime),
                             EndDate = g.Max(a => a.SampleTime)

                         });

Answer 1

Here's how you could write that query:

from x in Sampledata
where x.Samplevalue == null
group x by new { x.Name, x.Block } into g
let name = g.Key.Name
let startdate = g.Min(x => x.Sampletime)
let enddate = g.Max(x => x.Sampletime)
where SqlMethods.DateDiffMinute(startdate.Value, enddate.Value) >= 5
orderby name, startdate
select new { name, startdate, enddate }

Just note that you likely won't get exactly the same query, but it should be close enough.

But if you're unable to use SqlMethods, the next closest thing you could do is this:

from x in Sampledata
where x.Samplevalue == null
group x by new { x.Name, x.Block } into g
let name = g.Key.Name
let startdate = g.Min(x => x.Sampletime)
let enddate = g.Max(x => x.Sampletime)
where (startdate.Value - enddate.Value).Minutes >= 5
orderby name, startdate
select new { name, startdate, enddate }