RickyTamma RickyTamma - 4 months ago 25
SQL Question

Handling TimeSpan in LINQ where TimeSpan is first referenced in query

I understand that LINQ cannot use TimeSpan, however I want to do a where condition on a DateTime with an added TimeSpan. My Issue is however that the TimeSpan is first referenced within the query. I have tried doing it in memory but that also causes issues.

entiteis is of type:

System.Data.Entity.IDbSet<CharterRequestDTO>


And biddingToCloseIn is defined as:

[NotMapped]
public TimeSpan BiddingToCloseIn
{
get { return TimeSpan.FromTicks(BiddingToCloseInTicks); }
set { BiddingToCloseInTicks = value.Ticks; }
}


Normal:

var charterRequestDtoIds =
(from e in entities
where e.ClientId == clientId
&& e.Status != TrackingState.Void
&& DateTime.Now < e.CreatedAt.AddDays(30).Add(e.BiddingToCloseIn)
select e.Id);


In Memory:

var charterRequestDtoIds =
from e in entities.Where( e => e.ClientId == clientId
&& e.Status != TrackingState.Void
&& DateTime.Now < e.CreatedAt.AddDays(30).Add(e.BiddingToCloseIn) )
select e.Id;


Error for both:


System.NotSupportedException: LINQ to Entities does not recognize the
method 'System.DateTime Add(System.TimeSpan)' method, and this method
cannot be translated into a store expression.

Answer

Instead of adding a TimeSpan, add milliseconds (or seconds, minutes, according to the precision you need.)

AddDays(30).AddMilliseconds(e.BiddingToCloseIn.TotalMilliseconds)
Comments