CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-6
Jonas Sourlier
Jonas Sourlier

Reputation: 14445

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

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?

Upvotes: 1

Views: 1178

Answers (1)

Roy
Roy

Reputation: 51

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

Upvotes: 1

Related Questions