Date function problem in SQL and Linq

Question

I issued a SQL on SQL Server 2000:

select * from Employee where LastUpdateDate >=DateAdd(yyyy,-1,Getdate())

It works fine and got some records.

Then I write a Linq for the same purpose:

EntityQuery<Employee> query = from e in ctx.GetEmployeeQuery()
                               where e.DateCreated >= DateTime.Today.AddYears(-1)

but I got null from the result.

How to fix it?

Answer 1

Linq to Entities doesn't support the AddYear method. It does not know how to translate this into SQL. The solution is to precalc the value.

var targetDate = DateTime.Now.AddYears(-1)
EntityQuery<Employee> query = from e in ctx.GetEmployeeQuery()
                               where e.DateCreated >= targetDate