LINQ to Entities 4: Query with calculation in where clause.

Question

Hello. I have a table with a DateTime "TimeStamp" column and an int "TimeoutSeconds" column. I want to retrieve all records from this table where DateTime.Now - TimeStamp > TimeoutSeconds. In a stored proc this was a no brainer using GetDate():

select * from Schema.TableName mp
where (GetDate() - mp.[Timestamp]) > mp.Timeout

However, with Entity Framework using either LINQ syntax or Lambda I cannot do this because it seems the Lambda input variable (mp) cannot be used as part of a calculation only as part of a predicate, so this does not compile:

var records = context.TableName.Where(mp => (DateTime.Now - mp.TimeStamp) > mp.Timeout);

This does not compile.

I don't want to retrieve the whole table then do my filtering in memory and would rather not use a stored proc or Entity SQL. What are my options here?

Answer 1

This does not compile because you are comparing (DateTime.Now - mp.TimeStamp) which has return type System.TimeSpan to int. The first solution that comes to mind is to do

Where(mp => (DateTime.Now - mp.TimeStamp) > new TimeSpan(0, 0, 0, mp.Timeout))

Unfortunately this doesn't seem to work in EF, so if you have MS SQL Server as the DB, you can use SqlFunctions in EF4:

var records = context.
              TableName.
              Where(mp => System.Data.Objects.SqlClient.SqlFunctions.
                          DateDiff("s", mp.TimeStamp, DateTime.Now) > mp.Timeout);

http://msdn.microsoft.com/en-us/library/dd487052.aspx