Issues with DateTime.AddMinutes() in Dynamic LINQ Query

Question

I'm currently running into an issue using LINQ->Entites w/ Entity Framework v 4.2 and a T4-generated object context. I am relatively informed on how LINQ queries are converted into store expressions before being executed against the database, but am by no means an expert. From what I understand, the .NET functions used are converted to their equivalents in T-SQL, and those that do not have equivalents (for example, String.Format()) will throw a System.NotSupportedException at run time.

In my dynamic LINQ query, I'm using DateTime.Now.AddMinutes() to filter out my result set:

public void ExecuteSearch()
{
   var timeElapsedTresholdInMinutes = Convert.ToInt32( ConfigurationManager.AppSettings.Get("TimeElapsedThresholdInMinutes") );

   var resultSet = ( from entity in _dbContext.EntityCollection
                     where /*non-date-related where clause stuff*/
                           && 
                           entity.DateAdded <= DateTime.Now.AddMinutes(-1 * timeElapsedThresholdInMinutes)
                     select entity);

   /* do something with resultSet */
}

At run time, I am getting System.NotSupportedException: LINQ to Entities does not recognize the method DateTime.Now.AddMinutes() method and this method cannot be translated into a store expression.

Say for example, my timeElapsedThreshold has a value of -30 after being evaluated. Does anyone know why this wouldn't map to DATEADD(MINUTE,-30,GETDATE());? Is there something I'm missing here?

Granted, I can turn my code into:

public void ExecuteSearch()
{
   var maxDateTimeThreshold = DateTime.Now.AddMinutes( -1 * Convert.ToInt32(ConfigurationManager.AppSettings.Get("TimeElapsedThresholdInMinutes"));

   var resultSet = ( from entity in _dbContext.EntityCollection
                     where /*non-date-related where clause stuff*/
                           && 
                           entity.DateAdded <= maxDateTimeThreshold
                     select entity);
}

and overcome the issue of my code breaking, but I'd really like to understand why LINQ->Entities considers DateTime.Now.AddMinutes() as a .NET method that has no T-SQL equivalent. Any help / feedback is much appreciated!

Answer 1

LINQ Expression translates into under laying data source language, in your case its SQL, and in LINQ to Entities DateTime.AddMinutes doesn't have any implementation which would translate the query to corresponding equivalent in SQL. That is why .Net framework provided.

System.Data.Objects.EntityFunctions.AddMinutes

You should see: Date and Time Canonical Functions

You can have your query as:

   var resultSet = ( from entity in _dbContext.EntityCollection
                     where /*non-date-related where clause stuff*/
                           && 
                           entity.DateAdded <= 
                          System.Data.Objects.EntityFunctions.AddMinutes(
                                 DateTime.Now, -1 * timeElapsedThresholdInMinutes)
                     select entity);

But in your case its better if you can calculate the value before hand, like in your second code sample, since that will avoid the conversion for every record.