如何在SQL/Entity Framework中查找日期范围内缺失的TimeEntries记录

SQL 实现方案 #

核心思路是先为每个Job生成指定日期范围内所有应存在的周结束日，再与现有TimeEntries记录做左连接，筛选出无匹配的缺失记录。

前提条件 #

Jobs表需包含JobId（主键）和WeekEndDay字段（存储该Job的周结束星期标识，比如SQL Server中用1=周日、5=周五，需对应数据库的星期计算规则）。

代码示例（SQL Server） #

DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-31';

-- 递归生成每个Job在指定范围内的所有预期周结束日
WITH ExpectedWeekEndings AS (
    SELECT 
        j.JobId,
        -- 计算起始日期后第一个符合该Job周结束日的日期
        DATEADD(DAY, 
            (j.WeekEndDay - DATEPART(WEEKDAY, @StartDate) + 7) % 7, 
            @StartDate) AS ExpectedDate
    FROM Jobs j
    UNION ALL
    SELECT 
        JobId,
        DATEADD(WEEK, 1, ExpectedDate) AS ExpectedDate
    FROM ExpectedWeekEndings
    WHERE ExpectedDate < @EndDate
),
-- 获取指定范围内已存在的TimeEntries记录
ExistingEntries AS (
    SELECT JobId, WeekEnding
    FROM TimeEntries
    WHERE WeekEnding BETWEEN @StartDate AND @EndDate
)
-- 筛选缺失记录
SELECT 
    ewe.JobId,
    ewe.ExpectedDate AS MissingWeekEnding
FROM ExpectedWeekEndings ewe
LEFT JOIN ExistingEntries ee 
    ON ewe.JobId = ee.JobId AND ewe.ExpectedDate = ee.WeekEnding
WHERE ee.JobId IS NULL
ORDER BY ewe.JobId, ewe.ExpectedDate;

适配其他数据库说明 #

• MySQL：将DATEPART(WEEKDAY, ...)替换为WEEKDAY()（注意MySQL中WEEKDAY返回0=周一、6=周日，需调整WeekEndDay的数值对应关系）。
• PostgreSQL：用EXTRACT(DOW FROM ...)（0=周日、6=周六），计算逻辑需对应调整。

Entity Framework 实现方案 #

实体类定义 #

public class Job
{
    public int JobId { get; set; }
    // 存储周结束日对应的DayOfWeek枚举值（0=周日，6=周六）
    public int WeekEndDay { get; set; }
    // 其他业务字段...
}

public class TimeEntry
{
    public int TimeEntryId { get; set; }
    public int JobId { get; set; }
    public DateTime WeekEnding { get; set; }
    // 其他业务字段...
}

基础实现（循环校验） #

适合小数据量场景，逐个生成每个Job的预期周结束日并校验是否存在：

public List<(int JobId, DateTime MissingWeekEnding)> GetMissingTimeEntries(DateTime startDate, DateTime endDate)
{
    var jobs = _context.Jobs.ToList();
    var missingEntries = new List<(int, DateTime)>();

    foreach (var job in jobs)
    {
        // 计算起始日期后第一个符合该Job周结束日的日期
        var daysToAdd = (job.WeekEndDay - (int)startDate.DayOfWeek + 7) % 7;
        var currentWeekEnd = startDate.AddDays(daysToAdd);

        // 若计算出的日期早于起始日期，直接加一周
        if (currentWeekEnd < startDate)
        {
            currentWeekEnd = currentWeekEnd.AddDays(7);
        }

        while (currentWeekEnd <= endDate)
        {
            var exists = _context.TimeEntries.Any(te => 
                te.JobId == job.JobId && te.WeekEnding.Date == currentWeekEnd.Date);
            
            if (!exists)
            {
                missingEntries.Add((job.JobId, currentWeekEnd));
            }

            currentWeekEnd = currentWeekEnd.AddDays(7);
        }
    }

    return missingEntries.OrderBy(x => x.JobId).ThenBy(x => x.MissingWeekEnding).ToList();
}

优化实现（内存批量对比） #

大数据量下推荐此方案，先拉取所有现有记录到内存，再批量校验，减少数据库查询次数：

public List<(int JobId, DateTime MissingWeekEnding)> GetMissingTimeEntriesOptimized(DateTime startDate, DateTime endDate)
{
    var jobs = _context.Jobs.ToList();
    // 将现有记录按Job分组，存储为哈希集合提升校验效率
    var existingWeeksDict = _context.TimeEntries
        .Where(te => te.WeekEnding >= startDate && te.WeekEnding <= endDate)
        .Select(te => new { te.JobId, te.WeekEnding })
        .ToList()
        .GroupBy(x => x.JobId)
        .ToDictionary(
            g => g.Key, 
            g => g.Select(x => x.WeekEnding.Date).ToHashSet()
        );

    var missingEntries = new List<(int, DateTime)>();

    foreach (var job in jobs)
    {
        var daysToAdd = (job.WeekEndDay - (int)startDate.DayOfWeek + 7) % 7;
        var currentWeekEnd = startDate.AddDays(daysToAdd);
        
        if (currentWeekEnd < startDate)
        {
            currentWeekEnd = currentWeekEnd.AddDays(7);
        }

        while (currentWeekEnd <= endDate)
        {
            if (!existingWeeksDict.TryGetValue(job.JobId, out var existingWeeks) || !existingWeeks.Contains(currentWeekEnd.Date))
            {
                missingEntries.Add((job.JobId, currentWeekEnd));
            }

            currentWeekEnd = currentWeekEnd.AddDays(7);
        }
    }

    return missingEntries.OrderBy(x => x.JobId).ThenBy(x => x.MissingWeekEnding).ToList();
}

关键注意事项 #

1. 星期标识统一：确保Jobs表的WeekEndDay数值与数据库/代码中的星期计算规则一致（比如SQL Server默认周日为1，而C#的DayOfWeek周日为0，需提前转换）。
2. 日期边界处理：若指定的startDate恰好是某Job的周结束日，需确保包含该日期；endDate若为周中，只处理到之前的最后一个周结束日。
3. 性能优化：数据量较大时，优先使用SQL方案或EF的内存批量对比方案，避免循环查询数据库。

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