How to convert DateTime to TimeSpan in Entity Framework query

Question

I have this LINQ query with Entity Framework 6:

var timeCapturesQuery = Context.TimeCaptures
    .Where(t =>
        && t.StartDateTime.TimeOfDay < endTime
        && t.EndDateTime.TimeOfDay > startTime);

EndTime and StartTime are parmeters of type TimeSpan, StartDateTime and EndDateTime are columns on the table of datetime.

Unfortunately I get this error when it is run:

The specified type member 'TimeOfDay' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

How can I get a TimeSpan from a DateTime (i.e. the time from datetime in SQL) in this LINQ query?

Answer 1

Looks like DbFunctions.CreateTime is what you're looking for:

When used as part of a LINQ to Entities query, this method invokes the canonical CreateTime EDM function to create a new TimeSpan object.

So to get results between two times, you can:

var timeCapturesQuery = Context.TimeCaptures.Where(t =>
        DbFunctions.CreateTime(t.StartDateTime.Hour, t.StartDateTime.Minute, t.StartDateTime.Second) < endTime &&
        DbFunctions.CreateTime(t.EndDateTime.Hour, t.EndDateTime.Minute, t.EndDateTime.Second) > startTime);

Answer 2

The property TimeOfDay does not supported in LINQ to Entities so you can try using SqlFunctions.DatePart method instead.

You should probably also convert your TimeSpans into DateTimes .

I think this should work (assuming the TimeSpans is from the begining of the day):

var now = DateTime.Now;
var today = new DateTime(now.Year, now.Month, now.Day); 

var endDateTime = today + endTime;
var startDateTime = today + startTime

var timeCapturesQuery = Context.TimeCaptures.Where(t => 
                            SqlFunctions.DatePart("timeofday", t.StartDateTime) < SqlFunctions.DatePart("timeofday", endDateTime)
                            && SqlFunctions.DatePart("timeofday", t.EndDateTime) > SqlFunctions.DatePart("timeofday", startDateTime));

Edit

As mentioned in the comments the specific property TimeOfTheDay is not supported in DatePart method.

Maybe EntityFunctions.DiffNanoseconds method will work:

var now = DateTime.Now;
var today = new DateTime(now.Year, now.Month, now.Day); 

var endDateTime = today + endTime;
var startDateTime = today + startTime

var timeCapturesQuery = Context.TimeCaptures.Where(t => 
                            EntityFunctions.DiffNanoseconds(t.StartDateTime, endDateTime).Value < 0
                            && EntityFunctions.DiffNanoseconds(t.EndDateTime, startDateTime).Value > 0);

Edit2

Another option which is much simpler and I think will work is just to compare the DateTimes.

We've already converted the TimeSpans into DateTimes and we can create a simple condition using LINQ to Entities and it should work because we are not using any of the DateTimes properties.

var now = DateTime.Now;
var today = new DateTime(now.Year, now.Month, now.Day); 

var endDateTime = today + endTime;
var startDateTime = today + startTime

var timeCapturesQuery = Context.TimeCaptures.Where(t => t.StartDateTime < endDateTime && t.EndDateTime > startDateTime);

Answer 3

It looks like Linq2db supports it.

https://github.com/linq2db/linq2db/blob/1ff760181717c73859ab3a5519f76943241d460f/Source/Linq/Expressions.cs

Of course it is not very good option to use new ORM.
But I think it is most weak part of EF after performance.
So maybe it is good time to think again.

With Linq2db you can provide custom SQL logic (you need to create own expression). It was never necessary for me, but you can read this for more details.