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

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);

csharp

🔒 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.

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

👉 Mastering EF Core Interceptors – Hook Into the Pipeline with the Decorator Pattern

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

⚡ Optimize EF Core Queries with AsSplitQuery
Improve performance and avoid cartesian explosion with this EF Core 5+ feature.

🔒 Optimistic vs Pessimistic Concurrency in EF Core (with Table Hints)
Understand how to choose the right concurrency model — and how to implement it in SQL Server with precision.