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 EFCore.BulkExtensions simulate efficient MERGE behavior:
await dbContext.BulkInsertOrUpdateAsync(products);
Can I Use MERGE with EF Core Transactions?
Yes — 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 multiple bugs with MERGE in SQL Server — including:
https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16#remarks
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.
