基于Entity Framework实现父表MERGE+子表INSERT的最优方案问询

一、符合约束的EF Core解决方案 #

1. 单次数据库往返的参数化SQL结合EF Core原生API #

如果要实现和原生T-SQL一样的单次往返效果，可以通过EF Core的ExecuteSqlRawAsync执行参数化的复合脚本，既避免全量硬编码，又不用加载父表实体到内存：

var parentBusinessKey = "目标父记录业务标识"; // 父表唯一业务键，如Name/Code
var childRecord = new { /* 子表字段数据 */ };

await context.Database.ExecuteSqlRawAsync(@"
DECLARE @ParentId INT;
-- MERGE确保父记录存在
MERGE INTO ParentTable AS Target
USING (SELECT @ParentKey AS ParentKey) AS Source
ON Target.ParentKey = Source.ParentKey
WHEN NOT MATCHED THEN INSERT (ParentKey) VALUES (Source.ParentKey);
-- 获取父记录Id
SELECT @ParentId = Id FROM ParentTable WHERE ParentKey = @ParentKey;
-- 插入子表
INSERT INTO ChildTable (ParentId, ChildField1, ChildField2) 
VALUES (@ParentId, @ChildVal1, @ChildVal2);",
new SqlParameter("@ParentKey", parentBusinessKey),
new SqlParameter("@ChildVal1", childRecord.Field1),
new SqlParameter("@ChildVal2", childRecord.Field2));

该方案仅硬编码通用逻辑，所有业务数据通过参数传入，避免SQL注入，且仅需一次数据库往返。

2. 分步骤EF原生API实现（无批量场景） #

如果不需要严格单次往返，可拆分逻辑用EF原生API实现，完全避免硬编码业务逻辑：

• 第一步：用EF的ExecuteSqlRawAsync执行参数化MERGE，确保父记录存在
• 第二步：通过EF查询仅获取父记录Id（不加载整个实体）
• 第三步：用EF的Add+SaveChangesAsync插入子表

// 确保父记录存在
await context.Database.ExecuteSqlRawAsync(
    @"MERGE INTO ParentTable AS Target
      USING (SELECT @ParentKey AS ParentKey) AS Source
      ON Target.ParentKey = Source.ParentKey
      WHEN NOT MATCHED THEN INSERT (ParentKey) VALUES (Source.ParentKey);",
    new SqlParameter("@ParentKey", parentBusinessKey));

// 获取父Id（仅查询单个字段，不加载实体）
var parentId = await context.ParentTable
    .Where(p => p.ParentKey == parentBusinessKey)
    .Select(p => p.Id)
    .SingleAsync();

// 插入子表
context.ChildTable.Add(new ChildTable 
{ 
    ParentId = parentId, 
    ChildField1 = childRecord.Field1,
    ChildField2 = childRecord.Field2
});
await context.SaveChangesAsync();

3. 批量场景：无键实体映射临时表 #

针对批量数据，可通过EF无键实体映射临时表，实现批量MERGE+子表插入：

• 定义无键实体映射临时表结构
• 批量写入临时表数据
• 执行MERGE和子表插入的复合脚本

// 定义无键实体
[Keyless]
public class TempParentChildData
{
    public string ParentKey { get; set; }
    public string ChildField1 { get; set; }
    public int ChildField2 { get; set; }
}

// 在OnModelCreating中映射临时表
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TempParentChildData>()
        .ToView("TempParentChild", "dbo")
        .HasNoKey();
}

// 执行批量逻辑
var batchData = new List<TempParentChildData> { /* 批量数据 */ };
// 写入临时表（参数化批量插入，避免SQL注入）
var insertParams = batchData.Select((d, i) => new SqlParameter($"@Key{i}", d.ParentKey) 
    { SqlDbType = SqlDbType.NVarChar });
// 拼接参数化SQL并执行
await context.Database.ExecuteSqlRawAsync(@"
CREATE TABLE #TempBatch (ParentKey NVARCHAR(50), ChildField1 NVARCHAR(50), ChildField2 INT);
INSERT INTO #TempBatch VALUES {0};
-- MERGE父表
MERGE INTO ParentTable AS Target
USING #TempBatch AS Source
ON Target.ParentKey = Source.ParentKey
WHEN NOT MATCHED THEN INSERT (ParentKey) VALUES (Source.ParentKey);
-- 批量插入子表
INSERT INTO ChildTable (ParentId, ChildField1, ChildField2)
SELECT p.Id, t.ChildField1, t.ChildField2
FROM #TempBatch t
JOIN ParentTable p ON t.ParentKey = p.ParentKey;
DROP TABLE #TempBatch;",
string.Join(", ", batchData.Select((d, i) => $"(@Key{i}, @Val1{i}, @Val2{i})")));

二、EFCore实现比Inline SQL更快的原因解析 #

出现这种反预期结果，通常是以下因素导致：

• 查询计划缓存差异：EF Core默认使用参数化查询，SQL Server会缓存参数化查询的执行计划，重复执行时直接复用；若你的Inline SQL是字符串拼接而非参数化，每次执行都会重新编译执行计划，开销更高。
• 连接池复用优化：EF Core上下文会自动复用数据库连接池中的连接，避免重复创建/销毁连接的开销；若Inline SQL手动管理连接时未复用连接池，会产生额外性能损耗。
• 基准测试场景偏差：若测试数据量过小，EF Core的初始化开销被分摊；或Inline SQL实现存在冗余操作（如重复打开关闭连接、未启用事务批量提交），导致性能落后。
• EF Core的隐式优化：EF Core会对查询做细微优化（如自动调整参数类型、避免不必要的字段查询），而手动编写的Inline SQL可能存在未优化的细节。

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