68

I am trying to determine the number of days between 2 dates using LINQ with Entity Framework. It is telling me that it does not recognize Subtract on the System.TimeSpan class

Here is my where portion of the LINQ query.

where ((DateTime.Now.Subtract(vid.CreatedDate).TotalDays < maxAgeInDays))

Here is the error I receive in the VS.NET debugger

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

Am I doing something wrong or is there a better way to get the number of days between 2 DateTimes in the entity framework?

thanks Michael

Justin
  • 84,773
  • 49
  • 224
  • 367
  • 1
    I also tried to avoid timespan by changing the formula a bit to the following - which still doesn't work where (vid.CreatedDate.AddDays(maxAgeInDays) >= DateTime.Now) –  Feb 20 '09 at 19:36

6 Answers6

103

The accepted answer is better in this case, but for reference you can use the EntityFunctions class to perform operations on dates, among other things.

where (vid.CreatedDate >= EntityFunctions.AddDays(DateTime.Now, -maxAgeInDay))
SandRock
  • 5,276
  • 3
  • 30
  • 49
Morten Christiansen
  • 19,002
  • 22
  • 69
  • 94
51

Here is how I got it to work

I defined a datetime variable that represents the oldest date

DateTime oldestDate = DateTime.Now.Subtract(new TimeSpan(maxAgeInDays, 0, 0, 0, 0));
...

then I modified the where portion of the LINQ query

where (vid.CreatedDate >= oldestDate )

worked like a charm - thanks Micah for getting me to think about the expression tree

Justin
  • 84,773
  • 49
  • 224
  • 367
  • 2
    You should have flagged @Micah as answer and either add a comment to his response or update your question with the final answer. – Donald Byrd Sep 02 '10 at 17:47
  • Clever, clever clever. Simple and straight forward. I am still wondering why it did not come to me in the first place. Thanks for sharing – Shreedhar Kotekar Aug 02 '12 at 00:37
23

You can also use System.Data.Objects.EntityFucntions:

currentDate = DateTime.Now;

...
where  EntityFunctions.DiffDays(currentDate, vid.CreatedDate) < maxAgeIdDays 

All functions from EntityFunctions are only for Linq-to-entities and are mapped to SQL functions.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • @Morten: That was type, they work only with Linq-to-entities. – Ladislav Mrnka May 12 '11 at 08:04
  • 1
    Many SQL Server specific functions are available through the `System.Data.Objects.SqlClient.SqlFunctions` class. Of course, the data store has to be a SQL Server for this to work. More info: http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.aspx – bernhof Jun 15 '11 at 13:04
  • 7
    Also worth noting once you upgrade to EF 6+ the System.Data.Objects.EntityFucntions becomes obsolete, instead use: System.Data.Entity.DbFunctions – GarethReid Apr 16 '14 at 06:17
  • 2
    this function is deprecate use DbFunctions.DiffMilliseconds(o.DateReceived, o.DateOrder) istead – Oswaldo Alvarez Feb 11 '15 at 18:01
12

You run into these kind of isses because the predicate needs to be translated to an expression tree. And translation process doesn't recognize the DateTime.Now.Subtract method.

Micah
  • 111,873
  • 86
  • 233
  • 325
  • 1
    is there anyway to do what I am trying to do so that i can be translated into an expression tree? –  Feb 20 '09 at 19:37
2

The fact is that by design, LINQ to Entities needs to translate the whole query to SQL statements. That's where it cannot recognize Subtract method. It will occur whenever you try to use a C#/VB method inside a query. In these cases you have to figure out a way to bring out that part from the query. This post explains a bit more: http://mosesofegypt.net/post/LINQ-to-Entities-what-is-not-supported.aspx

Mahmood Dehghan
  • 7,761
  • 5
  • 54
  • 71
  • This answer is correct, but it's definitely not the answer I wanted. LINQ to SQL supported calling outside methods as much as you liked, so this trade-off is unfortunate. – Chris Moschini May 16 '11 at 02:19
0

You may define new property in your model:

    public DateTime StartDate{ get; set; }
    public DateTime EndDate{ get; set; }
    public TimeSpan CalculateTime{
        get
        {
            return EndDate.Subtract(StartDate);
        }
    }

Now, you may use something like that:

var query = from temp in db.Table
select new MyModel {
    Id = temp.Id,
    Variable1 = temp.Variable1,
    ...
    EndDate = temp.EndDate,
    StartDate = temp.StartDate
}

When you have look at result, you may use return such as:

return query

Now, in query, we have CalculateTime (subtract between EndDate and Startdate).

oknevermind
  • 101
  • 3
  • 17