如何在Entity Framework Core中高效删除指定日期前的非最新库存记录

嘿，这个需求我之前也碰到过——既要清理旧数据，又得保证每个库存代码留好最新的那条，还要兼顾大数量下的性能对吧？别担心，用EF Core 7+的批量删除API就能搞定，完全不用把所有数据加载到内存里折腾。

核心思路 #

咱们的目标很明确：删除所有记录日期早于指定 cutoff 日期的库存记录，但每个StockCode的最新记录（哪怕它也在cutoff日期之前）必须保留。如果某个StockCode有记录在cutoff日期及之后，那这些记录全部保留，cutoff之前的同代码旧记录直接删掉就行。

实现方案（EF Core 7+） #

首先得确认你用的是EF Core 7.0或更高版本——这个版本引入了ExecuteDeleteAsync，它会直接在数据库层面生成DELETE SQL，完全不会把实体加载到内存，这对大数量数据来说性能提升巨大，绝对比RemoveRange靠谱太多。

方案1：用子查询排除最新记录

假设你的库存实体叫StockInventory，包含Id（自增主键，用来判断最新记录）、StockCode和RecordDate字段：

var cutoffDate = new DateTime(2024, 1, 1); // 替换成你的指定日期

// 先获取每个StockCode的最新记录ID
var latestStockIds = from stock in _context.StockInventory
                     group stock by stock.StockCode into codeGroup
                     select codeGroup.OrderByDescending(s => s.Id).First().Id;

// 批量删除符合条件的记录
await _context.StockInventory
    .Where(s => s.RecordDate < cutoffDate && !latestStockIds.Contains(s.Id))
    .ExecuteDeleteAsync();

EF Core会把这段LINQ转换成类似CTE的SQL，直接在数据库执行，性能拉满。

方案2：用JOIN方式筛选要删除的记录

如果你觉得子查询不够直观，也可以用JOIN来关联每个StockCode的最新记录信息：

var cutoffDate = new DateTime(2024, 1, 1);

// 构建查询：找出所有需要删除的记录
var recordsToDelete = from s in _context.StockInventory
                      join latestInfo in (
                          from stock in _context.StockInventory
                          group stock by stock.StockCode into codeGroup
                          select new {
                              StockCode = codeGroup.Key,
                              LatestId = codeGroup.Max(s => s.Id)
                          }
                      ) on s.StockCode equals latestInfo.StockCode
                      where s.RecordDate < cutoffDate && s.Id != latestInfo.LatestId
                      select s;

// 执行批量删除
await recordsToDelete.ExecuteDeleteAsync();

性能优化技巧 #

如果你的库存数据量特别大（比如百万级以上），这些技巧能帮你避免锁表和内存问题：

• 添加索引：给StockCode和RecordDate创建联合索引IX_StockInventory_StockCode_RecordDate，再给Id单独加主键索引，这样分组和筛选的速度会快很多。
• 分批次删除：单次删除大量数据可能会导致数据库锁表时间过长，影响其他业务。可以循环分批次删除，比如每次删1000条：

  var cutoffDate = new DateTime(2024, 1, 1);
  int deletedCount;
  do
  {
      var latestStockIds = from stock in _context.StockInventory
                           group stock by stock.StockCode into codeGroup
                           select codeGroup.OrderByDescending(s => s.Id).First().Id;
  
      deletedCount = await _context.StockInventory
          .Where(s => s.RecordDate < cutoffDate && !latestStockIds.Contains(s.Id))
          .Take(1000)
          .ExecuteDeleteAsync();
  } while (deletedCount > 0);
  

• 先测试再删除：执行删除前，可以把ExecuteDeleteAsync()换成ToListAsync()，先看看要删除的记录是不是符合预期，避免误删。

注意事项 #

• 如果你的“最新记录”是按RecordDate而不是Id判断的，把代码里的OrderByDescending(s => s.Id)换成OrderByDescending(s => s.RecordDate)即可，但要注意如果同个StockCode有多个记录日期相同的情况，这种方式会保留所有同日期的最新记录——如果只想留一条，还是用Id更靠谱（假设Id是自增的）。
• 如果你用的是EF Core 6或更低版本，那没办法用ExecuteDeleteAsync，这时候可以考虑直接执行原生SQL（用_context.Database.ExecuteSqlRawAsync()），就是你已经写好的那条SQL，这也是个不错的选择，性能同样很好：

  var cutoffDate = new DateTime(2024, 1, 1);
  var sql = @"
      WITH LatestStockRecords AS (
          SELECT StockCode, MAX(Id) AS LatestId
          FROM StockInventory
          GROUP BY StockCode
      )
      DELETE FROM StockInventory
      WHERE RecordDate < @CutoffDate
      AND Id NOT IN (SELECT LatestId FROM LatestStockRecords)
  ";
  await _context.Database.ExecuteSqlRawAsync(sql, new SqlParameter("@CutoffDate", cutoffDate));
  

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