Combine datetime from separate date and time part (miliseconds, bigint) using Linq to Entity (EF Cor-CodePudding

I want to check two dates overlapping but instead of datetime the DB table uses **date **and bigint so I need to combine them into datetime in C# code:

Table columns: StartDate: date (null) EndDate: date (null) AvailableFromMs: bigint (null) AvailableToMs: bigint (null) table-columns

TEntity:

        [Column(TypeName = "date")]
        public DateTime? StartDate { get; set; }
        [Column(TypeName = "date")]
        public DateTime? EndDate { get; set; }
        public TimeSpan? AvailableFromMs { get; set; }
        public TimeSpan? AvailableToMs { get; set; }

db-data

Querying:

IQueryable<TEntity> query = <repository_to_query_from>...

DateTime routeStart = DateTime.Now;

 List<TEntity> overlapping = query
                 .Where(r => routeStart <= ((DateTime)(object)r.EndDate.Value).AddSeconds(r.AvailableToMs == null ? 0.0 : r.AvailableToMs.Value.Milliseconds / 1000.0))
                  .ToList();

(Note: I know that this is just part of the condition, I will update that when EF generates correct SQL) SQL generated by EF Core looks like this:

WHERE...
  (@__routeStart_1 <= DATEADD(second, CAST(CASE
 WHEN [r].[AvailableToMs] IS NULL THEN 0.0E0
 ELSE CAST(DATEPART(millisecond, [r].[AvailableToMs]) AS float) / 1000.0E0
 END AS int), CAST([r].[EndDate] AS datetime2)))

But running this query throws: Arithmetic overflow error converting expression to data type datetime.

This is because of the AvailableToMs type: C# code divides it first by 1000 but EF generates that /1000.0E0 after the CAST() is performed and I want to do the division first, then do casting.

How can I force EF to do that?

CodePudding user response：

Thanks for all comments and ideas.

I ended with removing that TimeSpan dependency completely and just use long value to remove the DATEPART() from generated SQL:

.Where(r => routeStart <= ((DateTime)(object)r.EndDate.Value)
   .AddMilliseconds(r.AvailableToMs == null ? 0 : (long)(object)(r.AvailableToMs.Value))

Generated SQL is:

...WHERE 
  ((@__routeStart_1 <= DATEADD(millisecond, CAST(CAST(CASE
    WHEN [r].[AvailableToMs] IS NULL THEN CAST(0 AS bigint)
    ELSE CAST([r].[AvailableToMs] AS bigint)
END AS float) AS int), CAST([r].[EndDate] AS datetime2)))

Some comments:

These (object) castings look suspicious, but they are necessary:

EndDate is [db]date that does not have a time part. Without time calling AddMilliseconds() fails as LinqToEntity cannot translate the expression correctly (requires [db]datetime not [db]date)

AvailableToMs is [db]bigint and cannot be used directly (as TimeSpan) because the expression is translated as DATEPART(millisecond, [r].[AvailableToMs]) AS bigint) As DATEPART() is unable to handle bigint causing 'Arithmetic overflow error converting expression to data type datetime.' SQL error (not an exception)