基于Entity Framework实现SQL Server高效UPSERT批量操作问询

Hey there! I’ve dealt with exactly this kind of bulk UPSERT scenario using EF Core with SQL Server before, so let’s break down solutions to both your questions clearly:

1. Efficient Bulk UPSERT Without Per-Record SQL Statements #

The key here is to leverage SQL Server’s native MERGE statement combined with Table-Valued Parameters (TVP)—this lets you send all 1000 records in one go and execute a single SQL command. Here’s a step-by-step implementation:

Step 1: Create a User-Defined Table Type in SQL Server

First, define a table type that matches your tuple structure (run this in SSMS):

CREATE TYPE dbo.IdDateValueTuple AS TABLE
(
    Id INT,
    [Date] DATE,
    Value DECIMAL(18,2) -- Adjust the data type to match your actual column
);

Step 2: Execute MERGE from EF Core

Convert your list of tuples to a DataTable, then pass it as a TVP to a MERGE command:

// Your existing list of tuples
var dataList = new List<(int Id, DateTime Date, decimal Value)>
{
    // Populate with your 1000 records
};

// Convert to DataTable for TVP
var tvpTable = new DataTable();
tvpTable.Columns.Add("Id", typeof(int));
tvpTable.Columns.Add("Date", typeof(DateTime));
tvpTable.Columns.Add("Value", typeof(decimal));

foreach (var item in dataList)
{
    tvpTable.Rows.Add(item.Id, item.Date, item.Value);
}

// Define the MERGE logic
var mergeQuery = @"
MERGE INTO YourTargetTable t
USING @SourceData s 
    ON t.Id = s.Id AND t.[Date] = s.[Date]
WHEN MATCHED THEN
    UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN
    INSERT (Id, [Date], Value)
    VALUES (s.Id, s.[Date], s.Value);";

// Execute the command
await yourDbContext.Database.ExecuteSqlRawAsync(mergeQuery,
    new SqlParameter("@SourceData", SqlDbType.Structured)
    {
        TypeName = "dbo.IdDateValueTuple",
        Value = tvpTable
    });

This runs a single SQL command, making it way more efficient than 1000 individual queries.

2. General UPSERT Patterns in EF Core #

The approach depends on whether you’re working with single records or batches:

For Single Records (Small Volume)

EF Core doesn’t have a built-in Upsert method, but you can easily create a reusable extension method that checks for existence first:

public static async Task UpsertSingleAsync<TEntity>(this DbContext context, TEntity entity, Expression<Func<TEntity, bool>> matchCondition)
    where TEntity : class
{
    var existing = await context.Set<TEntity>().FirstOrDefaultAsync(matchCondition);
    if (existing == null)
    {
        context.Set<TEntity>().Add(entity);
    }
    else
    {
        context.Entry(existing).CurrentValues.SetValues(entity);
    }
    await context.SaveChangesAsync();
}

Use it like this:

var record = new YourEntity { Id = 1, Date = DateTime.Today, Value = 150.5m };
await yourDbContext.UpsertSingleAsync(record, e => e.Id == record.Id && e.Date == record.Date);

For Batches (Like Your 1000-Record Scenario)

Besides the MERGE+TVP approach above, you can use a popular third-party library called EFCore.BulkExtensions if you prefer to avoid writing raw SQL. It handles bulk operations with minimal code:

// Install the NuGet package first: Install-Package EFCore.BulkExtensions

var entities = dataList.Select(t => new YourEntity
{
    Id = t.Id,
    Date = t.Date,
    Value = t.Value
});

await yourDbContext.BulkInsertOrUpdateAsync(entities, options =>
{
    // Specify the composite match key (Id + Date)
    options.ColumnPrimaryKeyExpression = e => new { e.Id, e.Date };
});

This library generates optimized SQL under the hood, so you get the performance benefits without manual SQL writing.

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