none
LINQ to Entities does not recognize the method 'System.DateTime Add(System.TimeSpan)' method, and this method cannot be translated into a store expression. RRS feed

  • Question

  • I want to select all the records that are older than given time span.

    but im getting the follwoing exception

    LINQ to Entities does not recognize the method 'System.DateTime Add(System.TimeSpan)' method, and this method cannot be translated into a store expression.

         var timeSpan = TimeSpan.Parse(_configManager.AppSettings["TimeSpan"]);            
                var now = DateTime.Now;
                var requests = _repository
                    .GetQuery<Documents>()
                    .Where(x => x.CreatedDateTime.Value.Add(timeSpan) < now)                    
                        .ToList();


    • Edited by lax4u Monday, March 10, 2014 3:47 PM
    Monday, March 10, 2014 3:38 PM

Answers

  • this works...thanks

         var nowMinusTimeSpan = DateTime.Now.Subtract(timeSpan);
                var requests = _repository
                    .GetQuery<Documents>()
                    .Where(x => 
                        x.CreatedDateTime.HasValue &&
                        x.CreatedDateTime.Value < nowMinusTimeSpan)
    
                        .ToList();


    Monday, March 10, 2014 4:28 PM

All replies

  • The timespan would only work out before the query.

    DateTime now = DateTime.Now; DateTime nowPlus60Days = now.AddDays(60);

     .Where(x => x.CreatedDateTime < now)        

    Monday, March 10, 2014 3:48 PM
  • LINQ 2 Entities can't translate that expression to TSQL. Also it's bad practice to wrap a database column in an expression in a comparison when you don't need to.

    EG: use:

    var nowMinusTimespan = now.Subtract(timespan);

    Then compare the database column to that value.

    If you want to push the date arithmetic down to SQL Server, use SqlFunctions.DateAdd() for EF 5 and before here, and for EF 6 and later here.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, March 10, 2014 3:55 PM
  • This will fix the error but, it will return invalid result. For example

    Lets say timespan is 2 days. Document is created on 03/09/2014. Today is 03/10/2014. The above logic will return that document. 

    It should only return documents that are 2 or more days older

    Monday, March 10, 2014 4:05 PM
  • this works...thanks

         var nowMinusTimeSpan = DateTime.Now.Subtract(timeSpan);
                var requests = _repository
                    .GetQuery<Documents>()
                    .Where(x => 
                        x.CreatedDateTime.HasValue &&
                        x.CreatedDateTime.Value < nowMinusTimeSpan)
    
                        .ToList();


    Monday, March 10, 2014 4:28 PM