LINQ to Entities for subtracting 2 dates

Question

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))

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

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.

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.

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