如何用EF Core/LINQ实现指定办公点时段的有效公休日查询

实体类定义（注意将Removed改为可空类型，匹配SQL的NULL逻辑） #

// 存储如“复活节星期一”“耶稣受难日”等条目
public class PublicHoliday
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public DateTime? Removed { get; set; }
}

// 存储公休日的具体日期，例如复活节星期一2024年为4月1日，2025年为4月21日
public class PublicHolidayDate
{
    public Guid Id { get; set; }
    public Guid PublicHolidayId { get; set; } // 关联PublicHoliday表的外键
    public DateOnly Date { get; set; }
    public DateTime? Removed { get; set; }
}

// 若某办公点禁用某公休日（即当日正常营业），则在此表存记录
public class OfficePublicHoliday
{
    public Guid Id { get; set; }
    public Guid OfficeId { get; set; }
    public Guid PublicHolidayId { get; set; } // 关联PublicHoliday表的外键
    public DateTime? Removed { get; set; }
}

实现方式一：对应SQL的NOT IN写法 #

先查询出目标办公点禁用的公休日ID列表，再在主查询中排除这些ID：

// 替换为实际的办公点ID、时间范围
var targetOfficeId = Guid.Parse("目标办公点ID");
var startDate = new DateOnly(2024, 1, 1);
var endDate = new DateOnly(2024, 12, 31);

// 获取该办公点禁用的所有有效公休日ID
var disabledHolidayIds = _dbContext.OfficePublicHoliday
    .Where(op => op.OfficeId == targetOfficeId && op.Removed == null)
    .Select(op => op.PublicHolidayId);

// 主查询：关联公休日和日期表，过滤有效记录并排除禁用项
var validHolidays = _dbContext.PublicHolidayDate
    .Join(_dbContext.PublicHoliday,
        phd => phd.PublicHolidayId,
        ph => ph.Id,
        (phd, ph) => new { PublicHolidayDate = phd, PublicHoliday = ph })
    .Where(joinResult => joinResult.PublicHoliday.Removed == null
                && joinResult.PublicHolidayDate.Removed == null
                && joinResult.PublicHolidayDate.Date >= startDate
                && joinResult.PublicHolidayDate.Date <= endDate
                // 核心：排除禁用的公休日ID，对应SQL的NOT IN
                && !disabledHolidayIds.Contains(joinResult.PublicHolidayDate.PublicHolidayId))
    .Select(result => new {
        HolidayId = result.PublicHoliday.Id,
        HolidayName = result.PublicHoliday.Name,
        HolidayDate = result.PublicHolidayDate.Date
    })
    .ToList();

实现方式二：用!Any()替代NOT IN（EF Core推荐写法） #

直接在主查询的条件中判断不存在对应的禁用记录，EF Core会自动优化生成高效的SQL：

var targetOfficeId = Guid.Parse("目标办公点ID");
var startDate = new DateOnly(2024, 1, 1);
var endDate = new DateOnly(2024, 12, 31);

var validHolidays = _dbContext.PublicHoliday
    .Where(ph => ph.Removed == null)
    .Join(_dbContext.PublicHolidayDate.Where(phd => 
                phd.Removed == null 
                && phd.Date >= startDate 
                && phd.Date <= endDate),
        ph => ph.Id,
        phd => phd.PublicHolidayId,
        (ph, phd) => new { PublicHoliday = ph, PublicHolidayDate = phd })
    .Where(j => !_dbContext.OfficePublicHoliday.Any(op => 
                    op.OfficeId == targetOfficeId 
                    && op.Removed == null 
                    && op.PublicHolidayId == j.PublicHoliday.Id))
    .Select(j => new {
        HolidayId = j.PublicHoliday.Id,
        HolidayName = j.PublicHoliday.Name,
        HolidayDate = j.PublicHolidayDate.Date
    })
    .ToList();

说明 #

• 两种写法都能实现目标SQL逻辑，第二种用!Any()的方式更符合EF Core的查询优化习惯，生成的SQL通常会用NOT EXISTS替代NOT IN，性能更优。
• 实体类中Removed字段必须定义为可空类型（DateTime?），否则无法匹配SQL中Removed IS NULL的判断逻辑。

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