DbFunctions throwing error System.Nullable on DiffMinutes()

Markl

I have a query that runs just fine until I call:
System.Data.Entity.DbFunctions.DiffMinutes(visitclock, v.START_TIME) > 0

Then I get:
'NotSupportedException: Method 'System.Nullable`1[System.Int32] DiffMinutes(System.Nullable`1[System.DateTime], System.Nullable`1[System.DateTime])' has no supported translation to SQL.'

The field is of DateTime Not Nullable, there is another field of type DateTime in another table (vv.CheckInTIme) that is Nullable, but I don't see why this would or should be an issue. If I run the query without this call it executes just fine. So why am I getting this error?

I have the NuGet EntityFramework of the latest version attached to the query. Need help asap to get a project moving that I've been fighting for a couple of days.

Thanks in advance.

nescafe

For a explanation you can take a look at this answer:

http://stackoverflow.com/a/332513/1105812

up vote
Linq-to-Sql can't translate arbitrary .net functions into SQL. Some DateTime functions can be translated however, and a full list is available here:-

http://msdn.microsoft.com/en-us/library/bb882657.aspx

In your example if you calculate the time offset outside of the projection you can add the offset to the retrieved "CreatedOn" DateTime using the AddMinutes method which has a supported translation into SQL.

SQL-wise a comparison with a constant will perform better than a DATEDIFF() calculation on each row, so you could try something like:

v.START_TIME >= visitclock.AddMinutes(1)

nescafe

As per the linked MSDN link, this should work (I'd still prefer the direct comparison mentioned above):

System.Data.Linq.SqlClient.SqlMethods.DateDiffMinute(visitclock, v.START_TIME) > 0

Markl

Thanks for the help. So reading documentation, I must have missed that. Stared at it too long. Both solutions offered work.