SqlServer: Translate TimeSpan members

[aleksandar-manukov]

Hi,

At work, we use a query which uses TimeSpan members Hours, Minutes and Seconds, but EFCore doesn't translate these members to sql.

We fix the problem creating SqlServerTimeSpanMemberTranslator like the SqlServerDateTimeMemberTranslator and we override the SqlServerMemberTranslatorProvider to include also our SqlServerTimeSpanMemberTranslator.

I think SqlServerTimeSpanMemberTranslator can be useful also to other .NET developers, so if you want I can make a PR to include it to EFCore.SqlServer project.

[ajcvickers]

@aleksandar-manukov Yes, we would be interested in a PR for this. We're curious how it deals with time spans that cover more than 24 hours, where the naive mapping to T-SQL time doesn't work.

[aleksandar-manukov]

Hi @ajcvickers

Never needed to deal with TimeSpan which is more than 24 hours. Our use case is that we have a time column for execution schedules and we need to compare it to current hours and minutes and to compare its time parts to other datetime column time parts which is holding execution schedules history. So we use the following query

// This is selecting all schedulers with scheduled time equal or bigger to the current time (hours and minutes) and for which there is no record in the StepScheduleHistory for today with time bigger than the scheduled.
return await _dbContext.StepSchedules
    .Where(ss => ss.StepId == stepId &&
        (ss.ScheduledTime.Hours > DateTime.Now.Hour ||
            (ss.ScheduledTime.Hours == DateTime.Now.Hour &&
                ss.ScheduledTime.Minutes >= DateTime.Now.Minute)) &&
        !_dbContext.StepScheduleHistories
            .Any(ssh => ssh.StepScheduleId == ss.Id &&
                ssh.ExecutionDate.Day == DateTime.Today.Day &&
                ssh.ExecutionDate.Month == DateTime.Today.Month &&
                ssh.ExecutionDate.Year == DateTime.Today.Year &&
                (ssh.ExecutionDate.Hour > ss.ScheduledTime.Hours ||
                    (ssh.ExecutionDate.Hour == ss.ScheduledTime.Hours &&
                        (ssh.ExecutionDate.Minute > ss.ScheduledTime.Minutes ||
                            (ssh.ExecutionDate.Minute == ss.ScheduledTime.Minutes &&
                                ssh.ExecutionDate.Second >= ss.ScheduledTime.Seconds))))))
    .MapTo<StepScheduleViewModel>()
    .OrderBy(ss => ss.ScheduledTime)
    .ToListAsync();

This query works with EntityFramework 6 but in EntityFramework Core ss.ScheduledTime.Hours, ss.ScheduledTime.Minutes and ss.ScheduledTime.Seconds cannot be translated to SQL. That's why we make SqlServerTimeSpanMemberTranslator like the SqlServerDateTimeMemberTranslator which is translating for example ss.ScheduledTime.Hours to datepart(hour, [ScheduledTime]).

Do you want me to make a PR and to continue discussion there or you need something more advanced to handle TimeSpan members translation?

[ajcvickers]

@aleksandar-manukov Thanks for the additional info. We will discuss.

[bricelam]

.NET	Transact-SQL
timeSpan.Hours	DATEPART(hour, @TimeSpan)
timeSpan.Minutes	DATEDIFF(minute, @TimeSpan)
timeSpan.Seconds	DATEDIFF(second, @TimeSpan)
timeSpan.Milliseconds	DATEDIFF(millisecond, @TimeSpan)

[bricelam]

The DATEDIFF and DATEADD functions don't really have .NET equivalents.

[aleksandar-manukov]

Hi @bricelam

I have never meant to translate TimeSpan members with datediff and dateadd functions. What we use (as you can see in my previous comment) is to translate TimeSpan.Hours -> datepart(hour, @TimeSpan) which is currently missing in EF Core. If you want I can make a PR to add SqlServerTimeSpanMemberTranslator to translate TimeSpan members to appropriate datepart function.

[bricelam]

A PR would be awesome!

I have never meant to translate TimeSpan members with datediff and dateadd functions.

Oh I was just doing my due diligence to see what other members we could translate. I only found Milliseconds.

[bricelam]

(You may have already seen it, but I have work-in-progress PR out for TimeSpan translations on SQLite)

[bricelam]

@cowmanjoe What SQL were you proposing for the Total* members in PR #19643?

[aleksandar-manukov]

@bricelam thanks. I will check your PR especially to see what tests I need to do and I will try to make a PR soon.

[cowmanjoe]

@bricelam, initially tried to do a datediff (this works with time sql value), but I don't think this is possible to do with better precision than milliseconds. I ended up using datepart to get each component separately, then multiplying those values by the correct ratio to get the total nanoseconds. Then I would divide that value by the appropriate ratio for the time units requested. This ended up being quite a big piece of SQL, but I don't think it would be that slow since it is essentially just extracting a few values from the time data and then multiplying and dividing a constant amount of times.

[roji]

FYI here's the Npgsql support for these members: npgsql/efcore.pg@c4b353d

Npgsql also translates TimeSpan.Days since TimeSpan maps to a real interval PG type, not time. @bricelam relevant also for Sqlite (i.e. consider adding tests for Days)?

[bricelam]

Already added in my PR

[roji]

Cool, will be looking forward to seeing Where_TimeSpan_Days pass on Npgsql!

[angusbreno]

This is related to the fact EF set TimeSpan as Time and not as long in SqlServer?

TimeSpan.FromDays(5) -> Exception

[SoftCircuits]

@bricelam Did this ever get any further?

I'm trying to do g.Average(t => (t.Departure!.Value - t.Arrival).TotalMinutes) in .NET 6.0 and it's failing.

[bricelam]

@SoftCircuits You have to write it like this for now: g.Average(t => EF.Functions.DateDiffMinute(t.Departure!.Value, t.Arrival))