CODEWITHSUNDEEP
c#linqlinq-to-entitiesentity-framework-4.1
Sachin Kainth
Sachin Kainth

Reputation: 46770

Linq to Entities SqlFunctions.DateDiff not supported

I have the following code

DateTime now = DateTime.UtcNow;

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

IQueryable<Item> items65To69 = allItemsOver64.Where(i =>
   (SqlFunctions.DateDiff("dd", i.PrimaryInsured.DoB, now) / 365.0) >= 65 &&
   (SqlFunctions.DateDiff("dd", i.PrimaryInsured.DoB, now) / 365.0) <= 69);

But when I try and use allItemsOver64 thus Items65To69.Count() I get this error

The expression ((((Convert(DateDiff("dd", [10007].PrimaryInsured.DoB, 26/04/2012 15:03:09)) / 365) >= 65) And ((Convert(DateDiff("dd", [10007].PrimaryInsured.DoB, 26/04/2012 15:03:09)) / 365) >= 65)) And ((Convert(DateDiff("dd", [10007].PrimaryInsured.DoB, 26/04/2012 15:03:09)) / 365) <= 69)) is not supported.

What am I doing wrong?

Upvotes: 5

Views: 15399

Answers (3)

Richard Pursehouse
Richard Pursehouse

Reputation: 1129

I understand the original question related to EF 4.1 but just to note EntityFunctions is now obsolete in EF6 (https://msdn.microsoft.com/en-us/library/system.data.entity.core.objects.entityfunctions%28v=vs.113%29.aspx), so DbFunctions should be used instead if targeting EF6 (https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions%28v=vs.113%29.aspx).

Upvotes: 3

sourcecode
sourcecode

Reputation: 4601

DateDiff function with linq to Entity is following bellow

If you want to

Day difference 
EntityFunctions.DiffDays(startDate , endDate);

Hours diff ->
EntityFunctions.DiffHours(startDate , endDate);

Minuutes diff ->
EntityFunctions.DiffMinutes(startDate , endDate);

Upvotes: 2

Justin Niessner
Justin Niessner

Reputation: 245499

Maybe you could try using EntityFunctions rather than SqlFunctions.

Upvotes: 6

Related Questions