LINQ to Entities: Age calculation in a LINQ query causes "Method cannot be translated into a store expression"

.net, linq, linq-to-entities

Solution

Since you are using LINQ to Entities, the preferred solution is to utilize the methods defined in `System.Data.Objects.EntityFunctions` (update for EF version 6: use `DbFunctions` instead) and the query surface will translate them into SQL successfully. In this case you want to take e.g. this:

o.FormDate.AddYears(-agesFrom)
          .CompareTo(o.FormDate.Subtract(o.Person.DateOfBirth))

and convert it to something like

EntityFunctions.DiffMinutes(
    EntityFunctions.AddYears(o.FormDate, -agesFrom),
    EntityFunctions.AddSeconds(
        o.FormDate, 
        EntityFunctions.DiffSeconds(o.FormDate, o.Person.DateOfBirth)
    )
)

I 'm not sure if the above is actually correct as it stands though because it really hurts my eyes.

Problem

Using LINQ to Entities, I need to get records from a PersonTable WHERE Age is between AgesFrom and AgesTo. The problem is, I need to calculate their Age from a DateOfBirth and a specific DateToCalculate, both stored in the database. eg. PersonTable: DateOfBirth=01/04/1972, DateToCalculateAge=25/12/2011 ``` .Where(0 < o.FormDate.AddYears(-agesFrom).CompareTo(o.FormDate.Subtract(o.Person.DateOfBirth)) && 0 > o.FormDate.AddYears(-agesTo).CompareTo(o.FormDate.Subtract(o.Person.DateOfBirth)); // ----------------------- OR ------------------------ .Where(agesFrom <= CalculateAge(o.Person.DateOfBirth.Value, o.FormDate) && agesTo >= CalculateAge(o.Person.DateOfBirth.Value, o.FormDate); ``` For my provided code, I get the following Exception: "LINQ to Entities does not recognize the method 'System.DateTime AddYears(Int32)' method, and this method cannot be translated into a store expression." I understand that once LINQ passes the Query over to the db, the method(s) I were trying to use could not be translated. Is there a work around for this? I could filter after hitting the database, but want to avoid that sort of bottle neck. I also thought of adding a sql function to do this calculation for me, but want to exhaust the LINQ possibilities first. A nice post here details how it is better to do the age calculation before the query: Shortest way to check for age filter in LINQ? However in my situation, I am not calculating age from DateTime.Now, but rather age from a date in the db.

Original source