已实现Member批量增改，如何处理其关联Services和Types的批量增改？

Member类包含其Services和Types集合：

public record Member 
{
   public int id { get; set; }
   public string name { get; set; }
   public ICollection<Service>? Services { get; set; }
   public ICollection<Type>? Types { get; set; }
}

通过Dapper调用存储过程：

public async Task<List<Member>> BulkSave(List<Member> items)
{
    var dt = ToDataTable(items);
    string sp = "SP_Member_BulkSave";

    using SqlConnection conn = new SqlConnection(_context.ConnectionString);
    using SqlCommand command = new SqlCommand(sp, conn);

    try
    {
        conn.Open();
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@tbl", dt);

        command.ExecuteNonQuery();
       
        return items;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        conn.Close();
    }
}

通过SQL的MERGE语句进行插入/更新，并将List<Member>作为DataTable传入存储过程：

CREATE PROCEDURE [dbo].[SP_Member_BulkSave]
    @tbl udtMember READONLY
AS
BEGIN
    BEGIN TRANSACTION
    
    BEGIN TRY
        MERGE dbo.[Member] AS TARGET 
        USING @tbl source ON LOWER(source.name) = LOWER(TARGET.name) 

        WHEN NOT MATCHED BY TARGET 
            THEN 
                INSERT (NAME) VALUES (source.name)
               
        WHEN MATCHED 
            THEN
                UPDATE 
                SET TARGET.name = source.name;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH
END

实现关联数据（Services/Types）的批量插入更新 #

1. 定义关联数据的用户定义表类型（UDT） #

先为Service和Type创建对应的UDT，包含关联的MemberId及自身唯一标识字段：

-- Service的用户定义表类型
CREATE TYPE udtService AS TABLE
(
    MemberId INT,
    ServiceName NVARCHAR(100),
    -- 其他Service业务字段
)

-- Type的用户定义表类型
CREATE TYPE udtType AS TABLE
(
    MemberId INT,
    TypeName NVARCHAR(100),
    -- 其他Type业务字段
)

2. 修改C#代码，传递关联数据的DataTable #

更新BulkSave方法，提取Member关联的Services和Types转为DataTable，作为参数传入存储过程：

public async Task<List<Member>> BulkSave(List<Member> items)
{
    var memberDt = ToDataTable(items);
    // 提取所有关联的Service数据并转为DataTable
    var serviceDt = ToDataTable(items.SelectMany(m => m.Services ?? Enumerable.Empty<Service>())
                                     .Select(s => new { s.MemberId, s.ServiceName /* 其他字段 */ }));
    // 提取所有关联的Type数据并转为DataTable
    var typeDt = ToDataTable(items.SelectMany(m => m.Types ?? Enumerable.Empty<Type>())
                                 .Select(t => new { t.MemberId, t.TypeName /* 其他字段 */ }));

    string sp = "SP_Member_BulkSave";

    using SqlConnection conn = new SqlConnection(_context.ConnectionString);
    using SqlCommand command = new SqlCommand(sp, conn);

    try
    {
        conn.Open();
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@tblMember", memberDt);
        command.Parameters.AddWithValue("@tblService", serviceDt);
        command.Parameters.AddWithValue("@tblType", typeDt);

        command.ExecuteNonQuery();
       
        return items;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        conn.Close();
    }
}

3. 修改存储过程，处理关联数据的MERGE操作 #

在同一个事务中依次处理Member、Services、Types的批量操作：

CREATE PROCEDURE [dbo].[SP_Member_BulkSave]
    @tblMember udtMember READONLY,
    @tblService udtService READONLY,
    @tblType udtType READONLY
AS
BEGIN
    BEGIN TRANSACTION
    
    BEGIN TRY
        -- 第一步：处理Member的插入/更新
        MERGE dbo.[Member] AS TARGET 
        USING @tblMember source ON LOWER(source.name) = LOWER(TARGET.name) 

        WHEN NOT MATCHED BY TARGET 
            THEN INSERT (NAME) VALUES (source.name)
               
        WHEN MATCHED 
            THEN UPDATE SET TARGET.name = source.name;

        -- 第二步：处理Service的插入/更新
        MERGE dbo.[Service] AS TARGET
        USING @tblService source 
            ON TARGET.MemberId = source.MemberId 
            AND LOWER(TARGET.ServiceName) = LOWER(source.ServiceName)

        WHEN NOT MATCHED BY TARGET
            THEN INSERT (MemberId, ServiceName /* 其他字段 */)
                 VALUES (source.MemberId, source.ServiceName /* 其他字段 */)

        WHEN MATCHED
            THEN UPDATE SET TARGET.ServiceName = source.ServiceName; -- 更新其他业务字段

        -- 第三步：处理Type的插入/更新
        MERGE dbo.[Type] AS TARGET
        USING @tblType source 
            ON TARGET.MemberId = source.MemberId 
            AND LOWER(TARGET.TypeName) = LOWER(source.TypeName)

        WHEN NOT MATCHED BY TARGET
            THEN INSERT (MemberId, TypeName /* 其他字段 */)
                 VALUES (source.MemberId, source.TypeName /* 其他字段 */)

        WHEN MATCHED
            THEN UPDATE SET TARGET.TypeName = source.TypeName; -- 更新其他业务字段

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END

4. 关键注意事项 #

• 确保Service、Type表的外键约束正确关联Member表的Id字段。
• 若Member是新插入的无Id数据，需通过OUTPUT子句获取插入后的MemberId，再关联到Services和Types：

  DECLARE @InsertedMembers TABLE (Id INT, Name NVARCHAR(100));
  MERGE dbo.[Member] AS TARGET 
  USING @tblMember source ON LOWER(source.name) = LOWER(TARGET.name) 
  WHEN NOT MATCHED BY TARGET 
      THEN INSERT (NAME) VALUES (source.name)
      OUTPUT inserted.Id, inserted.Name INTO @InsertedMembers;
  -- 后续可通过@InsertedMembers中的Id关联新Member的关联数据
  

• 若需要删除传入集合中不存在的关联数据，可在MERGE语句中添加WHEN NOT MATCHED BY SOURCE THEN DELETE（根据业务需求决定）。

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