Querying in advance with linq2sql for data with different signs

Let's say I have a table with id, value and timestam开发者_如何转开发p. Timestamp is when the value was inserted into the table. Value can be positive or negative.

How can I implement a smart LINQ query that will order this data by timestamp and take only those which one after another has different sign.

For example if I have values:

1 2011-03-20 5
2 2011-03-21 6
3 2011-03-22 -3
4 2011-03-23 -4
5 2011-03-24 2

I want to get only those with id:2,3 because they are different signs and 4,5

I could get them all and foreach them locally but I have more than 1 million such a values.

Having continuous set of dates makes it much easier. Here is a query:

 IQueryable<YourType> dates = ...;

 var result = dates
                .Join(dates, d => d.TimeStamp.AddDays(1), 
                             d => d.TimeStamp, 
                             (d1, d2) => new {d1, d2})
                .Where(p => p.d1.Value * p.d2.Value < 0);

I've tested it locally and it works. Here is a query which is sent to SQL server:

exec sp_executesql N'SELECT [t0].[id], [t0].[TimeStamp], [t0].[Value], [t1].[id] AS [id2], [t1].[TimeStamp] AS [TimeStamp2], [t1].[Value] AS [Value2]
FROM [dbo].[Dates] AS [t0]
INNER JOIN [dbo].[Dates] AS [t1] ON DATEADD(ms, (CONVERT(BigInt,@p0 * 86400000)) % 86400000, DATEADD(day, (CONVERT(BigInt,@p0 * 86400000)) / 86400000, [t0].[TimeStamp])) = [t1].[TimeStamp]
WHERE ([t0].[Value] * [t1].[Value]) < @p1',N'@p0 float,@p1 int',@p0=1,@p1=0