LINQ to Entities 4: query with calculation in where where

Question

I have a table with a DateTime column "TimeStamp" and an int column "TimeoutSeconds". I want to get all the records from this table where DateTime.Now is TimeStamp> TimeoutSeconds. In a saved proc, this was not a problem when using GetDate ():

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

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

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

This does not compile.

I do not want to retrieve the entire table and then do my filtering in memory and prefer not to use stored proc or Entity SQL. What are my options?

Answer 1

This does not compile because you are comparing (DateTime.Now - mp.TimeStamp) with the return type of System.TimeSpan to int . The first decision that comes to mind is to make

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

Unfortunately, this does not work in EF, so if you have MS SQL Server as a database, 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