.NET中判断DateTime是否在日期范围内的问题求助

你的核心问题在于日期范围判断的逻辑完全写反了。

原代码中的条件：

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

这个逻辑仅当SchedulerStart和SchedulerEnd的日期完全等于DateTime.Today时才会成立，无法覆盖跨天的日期区间（比如SchedulerStart是2022-11-01，SchedulerEnd是2022-11-03，Today是2022-11-02的场景）。

正确的判断逻辑 #

要检查DateTime.Today是否落在SchedulerStart到SchedulerEnd的日期区间内，应该判断：

• SchedulerStart的日期不晚于Today（即调度开始日期≤今天）
• SchedulerEnd的日期不早于Today（即调度结束日期≥今天）

对应的修正后条件：

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

修正后的完整代码 #

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();

额外说明 #

如果你的EF Core版本较低，DateTime.Date属性可能无法被正确转换为SQL查询，此时可以改用EF.Functions.TruncateTime(x.SchedulerStart) <= DateTime.Today和EF.Functions.TruncateTime(x.SchedulerEnd) >= DateTime.Today来替代，确保查询能正确被数据库执行。

内容的提问来源于stack exchange，提问作者Fatt Sky