DbFunctions for DateTime/TimeSpan addition (datetime / time(7) in SQL Server)

Question

I have the following properties in my EF6 code-first model:

public DateTime StartTime { get; set; }
public TimeSpan Duration { get; set; }

EF translates this to the SQL Server columns

StartTime DATETIME
Duration TIME(7)

This works great so far.

Now I want to load all entries which have their EndDate = StartDate + Duration after a specific DateTime value. First, I tried it like this:

db.Entries
    .Where(x => x.StartTime + x.Duration >= begin)
    .ToList();

This does not work, EF does not know how to translate the DateTime + TimeSpan addition operator.

So I changed it to this:

db.Entries
    .Where(x => DbFunctions.AddMilliseconds(x.StartTime, (int)x.Duration.TotalMilliseconds)
                >= begin)
    .ToList();

Here, it theoretically knows how to perform the DateTime + TimeSpan addition, but it does not know the TotalMilliseconds function:

System.NotSupportedException: Additional information: The specified type member 'TotalMilliseconds' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Any hints?

Yeah LINQ is trying to build an sql from your Expression. you are trying to get it to understand something that can't be compiled to sql — Callum Linington, Jul 17 '15 at 15:59
I thought `TIME` and `DATETIME` can be added by `DATEADD` in SQL Server? — Jonas Sourlier, Jul 17 '15 at 18:18
http://stackoverflow.com/questions/36522221/code-example-for-add-datetime-and-timespan-in-ef-query — Hardik, May 18 '17 at 10:41

score 1 · Answer 1 · answered Oct 14 '15 at 15:14

1

Instead of saving the Duration as a TimeSpane, you can save the milliseconds component as long or int. In this case you will not have to convert it to milliseconds

answered Oct 14 '15 at 15:14

Roy

51
1
10

DbFunctions for DateTime/TimeSpan addition (datetime / time(7) in SQL Server)

1 Answers1