Between/Timerange LINQ

Question

I'm trying to catch bookings between two datetimes.

There's:

Start(DateTime) and Hours(Duration, TimeSpan) ..

I've googled and found out several others have these problems because LINQ doesn't translate to T-SQL very well ..

This is my LINQ Query and the translated T-SQL: http://pastebin.com/sLrpWLma

I've seen that someone here have created some extension method that should work, but I am not sure where to put this method and which modifiers to use to have it available in my query? LINQ Between Operator

Answer 1

IMO part of the problem here is the schema; the system is always going to struggle if it hasn't got the start and end explicitly; not only will the query be hard to write, but it will have to do the computations per row.

I would address at the table level, for example with a persisted calculated column:

create table Booking (
    Start datetime,
    DurationMinutes int,
    [End] AS (DATEADD(minute,(DurationMinutes),Start)) PERSISTED
)

Now you can just query:

where b.Start >= someTime and b.End < someTime

(as an example) - which should map very cleanly in TSQL.

Because it is a calculated column, you can't ever have an inconsistent End value; and because it is marked as PERSISTED, it will not need to do the calculations per-row (unless you make a mess of the SET options...). You can even index PERSISTED columns, if you so choose.