date difference in EF4

Question

i need to get a difference of two dates ,one date from a table and one the current date, and the difference should be less than 9 0days. i need to use this as filter in where clause of the linq i tried doing this

var list = from p in context.persons where ((p.CreateDT).Subtract(DateTime.Now).Days < 90) select p;

i get this excpetion :

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

I did research other articles but nothing helped..Any ideas

Answer 1

Update

It seems that EF doesn't support subtracting dates and returning a TimeSpan. Here's one way to solve the problem:

DateTime oldestDate = DateTime.Now.AddDays(-90); 
var list = from p in context.persons where (p.CreateDT >= oldestDate) select p;

See this thread on Stackoverflow.

Answer 2

Try doing simply (p.CreateDate - DateTime.Now).Days < 90. Instead of calling DateTime.Subtract(). In some cases the operator overloads are implemented for Entity Framework even when the corresponding named methods are not.

If that doesn't work you could instead use ESQL or a stored procedure. As a final, dirty solution, you could call context.persons.ToList() and then call the DateTime.Subtract().

Answer 3

Trick here is that it can't translate all your fancy oo hoo-ha to plain old sql. Trick is to flip it on it's head:

var before = DateTime.Now.AddDays(-90);
var persons = context.Persons.Where(x => x.CreateDT > before);

EXPLANATION

Remember that everything in the WHERE bit of your LINQ statement must be translated from C# to SQL by the EF. It is very, very capable out of the box and handles most basic tasks, but it has no idea how to understand the most rudimentary method calls, such as DateTime.Subtract(). So the idea here is to let it do what it does best by precalculating a value and then passing that to the data tier.

The first line subtracts 90 days from the current time by adding negative 90 days. The second line passes it off to the database server.

The second line should translate to the SQL WHERE CreateDT > @BEFORETHIS