Linq expression not supported

Question

I have the following LINQ to Entities expression:

var allItemsOver64 =
  _inventoryContext.Items.Where(
  i => DateTimeHelper.CalculateAgeInYears(i.PrimaryInsured.DoB, now) >= 65);

The problem is that when I use allItemsOver64 it says that this expression is not supported. I have a feeling that this error is happening because of the call to the CalculateAgeInYears method. Why is this happening and how can I fix it?

Thanks,

Sachin

Edit:

Even after changing the code to use IEnumerables I still get the same error. Here is my code now:

DateTime now = DateTime.UtcNow;
            var allItemsOver64 =
                _inventoryContext.Items.Where(
                    i => DateTimeHelper.CalculateAgeInYears(i.PrimaryInsured.DoB, now) >= 65).AsEnumerable();
            IEnumerable<Item> items65To69 = allItemsOver64.Where(
                i =>
                DateTimeHelper.CalculateAgeInYears(i.PrimaryInsured.DoB, now) >= 65 &&
                DateTimeHelper.CalculateAgeInYears(i.PrimaryInsured.DoB, now) <= 69).AsEnumerable();

Answer 1

You can't call C# methods in EF queries, because EF doesn't know how to turn the method into SQL.

Instead, call .AsEnumerable() first to force the Where() to run locally.

Answer 2

If I infer correctly from your functions name, I think this may be what you're looking for:

using System.Data.Objects.SqlClient;

var allItemsOver64 = _inventoryContext
                     .Items
                     .Where(i => (SqlFunctions
                                  .DateDiff("dd", i.PrimaryInsured.DoB, now) / 365.0) 
                                  >= 65.0);