CODEWITHSUNDEEP
c#linqentity-framework-4linq-to-entities
Sachin Kainth
Sachin Kainth

Reputation: 46740

Linq expression not supported

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

Upvotes: 2

Views: 3160

Answers (3)

Johnny_D
Johnny_D

Reputation: 4652

Because Linq cannot interpret your helper method to SQL command. You should implement such logic on db side, or select all values and only then make check on client side. Neither of it is pretty for me, that's why I always try to simplify DB, to make as simple queries as it's possible.

Upvotes: 2

Sorax
Sorax

Reputation: 2203

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

Upvotes: 2

SLaks
SLaks

Reputation: 887195

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.

Upvotes: 6

Related Questions