c# - Querying in advance with linq2sql for data with gaps -

hi have table: values valueid, timestamp , value , belongto. each 15 minutes there insreted new row table new value, current timestamp , specific belongto field. , want find gaps mean values 1 after has timestamp more 15 minutes.

i trying this:

var gaps = p1 in db.t_values                        join p2 in db.t_values on p1.timestamp.addminutes(15) equals p2.timestamp                        grups !grups.any() select new {p1}; 

and works don't know if optimall, think? , don't know how can add p1.belongto == 1. cos query looks data.

jon told

var gaps = p1 in db.t_values            p1.belongto == 1            !db.t_values.any(p2 => p1.timestamp.addminutes(15) == p2.timestamp)            select p1; 

jon last query translated to:

exec sp_executesql n'select [t0].[valueid], [t0].[timestamp], [t0].[value],                     [t0].[belongto], [t0].[type] [dbo].[t_values] [t0] (not (exists( select null [empty] [dbo].[t_values] [t1] dateadd(ms, (convert(bigint,@p0 * 60000)) % 86400000, dateadd(day, (convert(bigint,@p0 * 60000)) / 86400000, [t0].[timestamp])) = [t1].[timestamp] ))) , ([t0].[belongto] = @p1)',n'@p0 float,@p1 int',@p0=15,@p1=1 

and works unless rows have same belongto, when there rows belongto many diferent values i've noticed need add sql:and [t1].belongto = 1 should this

n'select [t0].[valueid], [t0].[timestamp], [t0].[value], [t0].[belongto], [t0].[type] [dbo].[t_values] [t0]    (not (exists( select null [empty] [dbo].[t_values] [t1] dateadd(ms, (convert(bigint,@p0 * 60000)) % 86400000,  dateadd(day, (convert(bigint,@p0 * 60000)) / 86400000, [t0].[timestamp])) = [t1].[timestamp] , [t1].belongto = 1     ))) , ([t0].[belongto] = @p1)',n'@p0 float,@p1 int',@p0=15,@p1=1 

other words:

select  timestamp [dbo].[t_values] [t0] not( (exists (select null [empty] [dbo].[t_values] [t1] dateadd(minute, 15, [t0].[timestamp]) = [t1].[timestamp]))) , ([t0].[belongto] = 1) 

shoud change

select  timestamp [dbo].[t_values] [t0] not( (exists (select null [empty] [dbo].[t_values] [t1] dateadd(minute, 15, [t0].[timestamp]) = [t1].[timestamp] , [t1].belongto=1))) , ([t0].[belongto] = 1) 

but still thinking how can add linkq

well adding where clause easy (and i'll remove pointless anonymous type @ same time):

var gaps = p1 in db.t_values            p1.belongto == 1            join p2 in db.t_values            on p1.timestamp.addminutes(15) equals p2.timestamp            grups            !grups.any()            select p1; 

i'm not sure why you're grouping though... have thought simpler:

var gaps = p1 in db.t_values            p1.belongto == 1            !db.t_values.any(p2 => p1.timestamp.addminutes(15) == p2.timestamp)            select p1; 

as performance - @ generated sql , how looks in sql profiler.

edit: if need belongto check in both versions (makes sense) i'd suggest this:

var sequence = db.t_values.where(p => p.belongto == 1);  var gaps = p1 in sequence            !sequence.any(p2 => p1.timestamp.addminutes(15) == p2.timestamp)            select p1;