CODEWITHSUNDEEP
c#linq-to-sql
Brian Triplett
Brian Triplett

Reputation: 3532

Get one beyond timestamp cutoff in Linq 2 SQL

I have a "large" (hundreds of thousands of rows) table (call it dataPoints). Another table of "events" which has a one-to-many relationship to the datapoints. The events table has a TimeStamp column of type DateTimeOffset. I want to query all the datapoints in a certain time range, with one important caveat: I need to interpolate to an input cutoff time.

For example: Let's say I have events at randomly distributed times in the past (-1 hr, -5 hr, -8 hr, -11 hr and -13 hr). Each of these events created some dataPoints and I want to query all the datapoints in the last 12 hours. I would need to get the datapoints associated with ALL these points (including the -13 hr point). The reason for this is because I need data at the cutoff time (-12 hours), therefore I need both the -11 hr point AND the -13 hr point so I can interpolate to -12 hr. I can't just simply say where dataPoint.Event.Time > currentTime - 12, I need to get one more point beyond that.

Is there any way to express this in LINQ to SQL? I've only come up with 2 ideas:

1) use some heuristic (multiple the cutoff by 2) to make sure I have enough points. This is not robust and can be wasteful.

2) Query all the events first, sort them, determine which event is the last one I want by just rolling over manually, then query all the datapoints with a timestamp less that that calculated event. This one requires a lot of code and is also wasteful.

Any other ideas?

Upvotes: 1

Views: 108

Answers (1)

usr
usr

Reputation: 171178

I'd do it like this:

  1. Query all events up to including the cutoff datetime
  2. Query the first event earlier than the last event pulled in step 1

If there is an index on the datetime column both will execute quickly. The only problem is an additional round-trip.

This is similar to your idea 2 but you only need to query at most one event in step 2. I don't think this requires a lot of code.

Query 2 goes like this:

(from e in db.Events
where e.DateTime < lastDateTime
order by e.DateTime
select e).Take(1)

This causes an index seek returning one row. Very efficient.

Upvotes: 1

Related Questions