⚔️ The Mighty MERGE: Using SQL Merge Statements Safely with EF Core

[Sascha]

When you’re working with bulk data operations, upserts, or syncing external sources, MERGE is one of the most powerful tools SQL Server offers. But what happens when you need to use it alongside Entity Framework Core?

In this article, we’ll cover:

What MERGE is and why it matters

How to use it with EF Core (with and without transactions)

Common pitfalls and best practices

Alternatives like BulkExtensions and PostgreSQL upserts

When to use MERGE — and when not to

What Is SQL MERGE?

MERGE is a T-SQL command that allows you to INSERT, UPDATE, or DELETE in a single statement based on conditions between a source and a target.

Simple Example:


MERGE INTO Products AS Target
USING (VALUES (1, 'Apple'), (2, 'Banana')) AS Source(Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
UPDATE SET Name = Source.Name
WHEN NOT MATCHED THEN
INSERT (Id, Name) VALUES (Source.Id, Source.Name);




The power of MERGE lies in combining multiple operations in one atomic transaction — perfect for syncing external sources, staging tables, or upserting data without multiple round-trips.

Using MERGE with EF Core
EF Core does not expose MERGE as part of its LINQ API. But you can still run raw SQL safely using:

Option 1: ExecuteSqlRaw


await dbContext.Database.ExecuteSqlRawAsync(@"
MERGE INTO Products AS Target
USING (VALUES (1, 'Apple'), (2, 'Banana')) AS Source(Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
UPDATE SET Name = Source.Name
WHEN NOT MATCHED THEN
INSERT (Id, Name) VALUES (Source.Id, Source.Name);");




Option 2: Call a Stored Procedure


CREATE PROCEDURE MergeProducts AS
BEGIN
MERGE INTO Products ...
END




await dbContext.Database.ExecuteSqlRawAsync("EXEC MergeProducts");




Option 3: Use EF Core Bulk Libraries

Libraries like

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

simulate efficient MERGE behavior:


await dbContext.BulkInsertOrUpdateAsync(products);





csharp

Can I Use MERGE with EF Core Transactions?

— and you should if you’re performing other EF operations in the same workflow. EF Core supports shared transactions across both raw SQL and EF operations.

Full Example:


using var transaction = await dbContext.Database.BeginTransactionAsync();

try
{
// 1. Execute MERGE
await dbContext.Database.ExecuteSqlRawAsync(@"
MERGE INTO Products AS Target
USING (VALUES (1, 'Apple'), (2, 'Banana')) AS Source(Id, Name)
ON Target.Id = Source.Id
WHEN MATCHED THEN
UPDATE SET Name = Source.Name
WHEN NOT MATCHED THEN
INSERT (Id, Name) VALUES (Source.Id, Source.Name);");

dbContext.SyncLogs.Add(new SyncLog { Table = "Products", Timestamp = DateTime.UtcNow });
await dbContext.SaveChangesAsync();

await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}




This ensures the MERGE and the SyncLogs insert are part of the same database transaction, protecting you from partial updates.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Warnings About SQL Server MERGE

Microsoft has acknowledged

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

s with MERGE in SQL Server — including:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Unexpected behavior with triggers

Incorrect OUTPUT clauses

Problems with concurrent updates

Safer Patterns

Use UPDATE ... and INSERT WHERE NOT EXISTS if possible

Avoid complex MERGE statements with multiple joins

Always test MERGE inside a transaction in production-like data scenarios

When Should I Use MERGE?

Use MERGE if:

You need to sync two tables (e.g., staging import)

You want to upsert large volumes in one atomic step

You’re doing data warehousing or ETL pipelines

Avoid MERGE if:

You're only updating/inserting a few rows

You rely on entity validation logic in your app

You need maximum concurrency safety without side effects

Bonus: Reusable MERGE Helper

You can build a helper that accepts any raw SQL and executes it within a transaction:


public static async Task ExecuteMergeAsync(this DbContext db, string sql)
{
using var tx = await db.Database.BeginTransactionAsync();
try
{
await db.Database.ExecuteSqlRawAsync(sql);
await tx.CommitAsync();
}
catch
{
await tx.RollbackAsync();
throw;
}

}




Conclusion

SQL MERGE is a mighty tool for any developer working with bulk updates, external data, or data sync pipelines.

EF Core doesn’t have built-in support but raw SQL, stored procs, and bulk extensions let you use it safely and effectively.

Wrap it in transactions, know when it shines, and watch out for its quirks and you’ll get the best of both SQL Server and EF Core worlds.

Further Reading
Want to go deeper into advanced EF Core concepts? Check out these related articles I’ve written:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

It’s a deep dive into tapping into EF Core’s lifecycle events using clean, testable patterns.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

Improve performance and avoid cartesian explosion with this EF Core 5+ feature.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

(with Table Hints)
Understand how to choose the right concurrency model — and how to implement it in SQL Server with precision.

Источник:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.