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?

Sorry but I don't see how that could help. You're converting the DateTime to a string then back to a DateTime then calling the property which throws the error. Also remember that StartDateTime is a column on a table, not a parameter. — James Newton-King, Oct 02 '16 at 23:03
What Paul [said](http://stackoverflow.com/questions/32756662/timeofday-is-not-supported-in-linq-to-entities-find-total-seconds-in-linq-2) might work. — Berkay Yaylacı, Oct 02 '16 at 23:08
What about this [Solution](http://stackoverflow.com/a/21289257/5460854) — CodeMaster, Oct 02 '16 at 23:08

Saeb Amini · Accepted Answer · 2018-03-13T22:58:26.293

9

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

edited Mar 13 '18 at 22:58

answered Oct 03 '16 at 03:53

Saeb Amini

23,054
9
78
76

I guess the downside to this approach is that it lops off sub-second TimeSpan values. – John Zabroski Apr 23 '20 at 19:56

YuvShap · Answer 2 · 2016-10-03T01:19:19.170

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

There is no "timeofday" argument for DATEPART. – James Newton-King Oct 03 '16 at 00:24 — James Newton-King, Oct 03 '16 at 00:24

score 1 · Answer 3 · edited May 23 '17 at 12:00

1

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.

edited May 23 '17 at 12:00

Community

1
1

answered Oct 02 '16 at 23:41

Maxim

13,029
6
30
45

1

Switching away from EF is not an option. – James Newton-King Oct 03 '16 at 00:24

How to convert DateTime to TimeSpan in Entity Framework query

3 Answers3

Linked

Related