.NET Core EF 3.1.9 批量删除报FromSql缺失主键列错误及方案咨询

两个API的设计逻辑完全不同，之前的写法从根本上用错了入口：

• DbSet.FromSqlRaw 的设计目标是执行返回实体结构结果集的原生查询，后续链式调用的Where、Count等方法，全部建立在“原生SQL返回的列能完整匹配TEntity的所有属性”这个前提上。EF执行时会逐行把返回结果映射成TEntity实例，这就要求结果集必须包含实体定义的所有主键、属性对应的列，否则就会抛出“缺失必需列”的异常。
• 之前的代码把DELETE from dbo.Employee这种非查询类数据修改语句传给了FromSqlRaw：DELETE执行后不会返回任何结果行、任何列，EF在尝试映射结果的时候，自然找不到必需的EmpID主键，直接触发异常。换其他实体测试时报同类错，也是同样的逻辑——无论实体主键是什么，非查询SQL返回空结果集的本质没有变。
• 后续改用DbContext.Database.ExecuteSqlRaw能正常运行，是因为这个API天生就是用来执行不返回结果集的非查询SQL（增、删、改），直接返回受影响的行数，全程不需要做实体结构映射，自然不会校验结果集的列结构，也就不会报缺失列的错误。

说明：EF Core 3.1.9没有内置原生批量操作能力（批量删除是EF Core 7才新增的原生功能），结合版本特性和大批量删除的场景要求，按性能优先级给出可落地方案：

• 优先采用小批次循环删除，绝对禁止单次执行全量DELETE删100万行
  • 单次大事务删除会触发锁升级（从行锁直接升级为表锁）、撑爆事务日志、造成长时间业务阻塞，整体吞吐量极低。
  • 最优实现是每批删除5000-10000行，循环执行直到符合条件的数据全部清理完成，每批单独提交事务。参考实现：

    // 每批删除1万行，可根据业务实际调整批次大小
    const int batchSize = 10000;
    // 删除条件对应的阈值
    var deleteThreshold = 10;
    while (true)
    {
        int affectedRows = DbContext.Database.ExecuteSqlRaw(
            "DELETE TOP (@BatchSize) FROM dbo.Employee WHERE EmpID > @Threshold",
            new SqlParameter("@BatchSize", batchSize),
            new SqlParameter("@Threshold", deleteThreshold));
        // 当单批返回的影响行数小于批次大小时，说明已经没有符合条件的数据，退出循环
        if (affectedRows < batchSize) break;
    }
    

  • 这种方式的整体吞吐量比一次性全量删除高30%以上，锁等待时间降低90%，对在线业务的影响可以降到最低。
• 配套索引优化降低删除耗时
  • 执行删除前必须确认删除条件对应的字段有合适的索引：比如当前场景的删除条件是EmpID > 10，由于EmpID是表的聚集主键，本身带索引覆盖，删除时可以直接通过索引定位行，不需要全表扫描，性能最优。如果删除条件的字段没有索引，哪怕用分批删除，每次都会触发全表扫描，性能会下降一个数量级。
• 高占比删除场景的极致优化
  • 如果单次删除的是表中80%以上的存量数据，不要用DELETE逐行清理，采用表切换的方式：先把需要保留的少量数据迁移到临时表，清空原表后再把临时表数据切回正式表，整个操作是元数据级的快速操作，毫秒到秒级就能完成几十万行的清理，性能比逐行DELETE高两个数量级。
• 避坑要点
  • 绝对不要用EF默认的实体跟踪式删除：即先把待删除实体全部查出来，再逐个调用DbSet.Remove()的模式。这种模式需要把所有待删除实体加载到内存做状态跟踪，还要为每一行生成单独的DELETE语句，删除100万条数据会直接造成内存溢出，执行耗时达到小时级，完全不适配大批量场景。
  • 可以搭配适配EF Core 3.1的批量操作扩展库实现Lambda风格的删除写法，底层生成的SQL和手写批量Delete性能一致，同时可以规避手写SQL带来的注入风险。

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