Date and time between a date range in dotnet-CodePudding

I am facing the problem to know if DateTime is between a date range in the dotnet.

For example if the x.SchedulerStart value is 2022-11-02 06:46:30 and x.SchedulerEnd value is 2022-11-02 23:26:30. I want check this DateTime.Today value is inside the date range, but below coding is doesn't work. I have look at this StackOverflow question still cannot work How to know if a DateTime is between a DateRange in C#

Below is my coding:

x.SchedulerStart.Date >= DateTime.Today && x.SchedulerEnd.Date <= DateTime.Today

Whole code:

List<SAASMsgSchedulerForQueueList> msgSchedulerList = await _saasdbContext.SaMsgScheduler.AsNoTracking().Where(x => (x.Enabled == true && x.SchedulerStart.Date >= DateTime.Today && x.SchedulerEnd.Date <= DateTime.Today) &&
    ((x.SchedulerRecurring == "Daily" && x.RecurringTime == currentTime) || (x.SchedulerRecurring == "Weekly" && x.RecurringWeekday == weekDayNumber && x.RecurringTime == currentTime) ||
    (x.SchedulerRecurring == "Monthly" && x.RecurringDay == currentDay && x.RecurringTime == currentTime) || (x.SchedulerRecurring == "Yearly" && x.RecurringMonth == currentMonth && x.RecurringTime == currentTime)))
    .Join(_saasdbContext.TnMsgTemplate.AsNoTracking(),
    schedule => schedule.TemplateId,
    template => template.Id,
    (schedule, template) => new { schedule, template })
    .Join(_saasdbContext.SaMsgQuery.AsNoTracking(),
    schedule => schedule.template.QueryId,
    query => query.Id,
    (schedule, query) => new SAASMsgSchedulerForQueueList()
    {
        ID = schedule.schedule.Id,
        BranchID = schedule.schedule.BranchId,
        TemplateID = schedule.schedule.TemplateId,
        TemplateContent = schedule.template.TemplateContent,
        Query = query.QuerySql,
        MessageType = schedule.schedule.MessageType,
        RecurringDatetime = schedule.schedule.RecurringDatetime,
    }).ToListAsync();

Hope some one can guide me on how to solve this problems. Thanks.

CodePudding user response：

You need to reverse the condition. Right now you're looking for something that started after today and ended before today.

It's better to write the query in a form that reflects what you want, ie Today is between the start and end dates :

x.SchedulerStart.Date <= DateTime.Today && DateTime.Today <= x.SchedulerEnd.Date

Better, as in after 20 years I still mix things up if I put the field on the left side of such a query. One shouldn't have to translate an expression to understand what it does

Another improvement is to avoid .Date. This results in a cast(ScheduleStart as date) in SQL Server. Normally such a cast would prevent the use of indexes. SQL Server is smart enough to convert this into a range query but can't use any indexes gathered for the ScheduleStart column and can still end up with an inefficient execution plan.

.Date can simply be removed from DateTime.Today <= x.SchedulerEnd.Date. If the end date is today, DateTime.Today <= x.SchedulerEnd holds no matter the time.

To eliminate .Date from the opening date, compare it to the next day, ie x.SchedulerStart < DateTime.Today.AddDays(1). If the start day is today, that will hold for every time. If SchedulerStart is on the next day, the condition will still be false.

A correct and efficient condition will be :

x.SchedulerStart < DateTime.Today.AddDays(1) 
&& DateTime.Today <= x.SchedulerEnd

The query is targeting MySQL using Oracle's official EF Core provider, MySQL.EntityFrameworkCore, which has several known problems, which are fixed on Oracle's own schedule. That's why almost everyone uses the truly open source Pomelo.EntityFrameworkCore.MySql. Everyone as in 29.3M downloads for Pomelo vs 1.7M downloads for Oracle's provider.

In this case, Oracle's provider fails to treat DateTime.Today.AddDays(1) as a constant and tries to convert it to a SQL expression.

To avoid this problem, calculate the dates before the query, eg:

var today    = DateTime.Today;
var tomorrow = today.AddDays(1);

...
x.SchedulerStart < tomorrow && today <= x.SchedulerEnd