Get data from database between specified date and time range using linq-sql

Question

I have a table with two separate columns for date and time (SQL Server 2008).

I am trying to get the data between 1 minute before current time and 1 minute after current current time in my asp.net MVC (C#) application.

I have tried to use the where condition as:

ce.Start_Date.Add(ce.Start_Time) >= _currDateTime.AddMinutes(-1) && 
ce.Start_Date.Add(ce.Start_Time) <= _currDateTime.AddMinutes(1)

But from the above where condition, it throws an error:

The datepart millisecond is not supported by 
  date function dateadd for data type date.

How can I correct/rectify this error?

Answer 1

Don't add the minutes in the where clause, try something like this instead:

for ce in data
let start = _currDateTime.AddMinutes(-1)
let end = _currDateTime.AddMinutes(1)
where ce.Start_Date.Add(ce.Start_Time) >= start && 
ce.Start_Date.Add(ce.Start_Time) <= end
select ce

LINQ to SQL is attempting to translate your where clause into valid T-SQL (using T-SQL's dateadd function) and it is having trouble doing so.

Answer 2

Simplest way to add "update time" field in table and compare current time with this field.

Answer 3

The problem seems to be with server DATE type which does not have proper SQL translation.
Try to make LINQ convert Date to DateTime, something like this:

 Convert.ToDateTime(ce.Start_Date).Add(ce.Start_Time)

Answer 4

Converted to .AsEnumerable() and manipulate the date and time and return as .AsQueryable();