CODEWITHSUNDEEP
c#sqllinqdatetimeentity-framework-4
Eric Stallcup
Eric Stallcup

Reputation: 389

Issues with DateTime.AddMinutes() in Dynamic LINQ Query

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!

Upvotes: 3

Views: 8471

Answers (4)

st_stefanov
st_stefanov

Reputation: 1186

I've been using the DbFunction for long time, until today I realized...why do I even need to bother with that DbFunction at all.

And here is what came to my mind:

var dateLimit = DateTime.Now.AddMinutes(-20);
var result = db.TableName.Where(x => x.MyDateTime > dateLimit);

I just took the 'AddMinutes' operation out of the linq expression.

Upvotes: 1

UnreliableWitness
UnreliableWitness

Reputation: 496

EntityFunctions is obsolete (EF 6.x).

Please use DbFunctions class for DateTime manipulations.

e.g from cmd in context.Commands where cmd.ExecutedOn > DbFunctions.AddMilliseconds(baseDate, millisecondsToAdd) select cmd

Upvotes: 5

Thomas Levesque
Thomas Levesque

Reputation: 292475

I'd really like to understand why LINQ->Entities considers DateTime.Now.AddMinutes() as a .NET method that has no T-SQL equivalent

Because every mapping from .NET to T-SQL has to be manually implemented, and they just didn't implement this one...

Upvotes: 0

Habib
Habib

Reputation: 223287

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.

Upvotes: 13

Related Questions