Stop EF Core from using Merge to Insert

Question

I am converting my application to .NET Core. In doing so, I am running into issues with EF Core and inserts.

If I insert 1 or 2 rows then EF Core performs a normal SQL INSERT statement.

But when I have 3 or more rows, it switches to a MERGE statement, which then fails with:

The column reference "inserted.MyKeyColumn" is not allowed because it refers to a base table that is not being modified in this statement.

My guess is that this is due to the fact that the query is actually running on a view that has an insert trigger on it to update the actual table(s) under the view.

Like I said, this works just fine when it uses insert statements. But it fails when it tries to insert using a merge.

Is there a way to stop EF Core from using MERGE for inserts?

Answer 1

So some more research showed a way to force the use of single inserts:

 optionsBuilder.UseSqlServer(connectionString, options =>
        {
            options.MaxBatchSize(1);
        });

Warning: This will cause all inserts to be individual insert statements. Not very performant for high volume inserts.

Answer 2

Same error message, but different cause in EF core 7:

The column reference "..." is not allowed because it refers to a base table that is not being modified in this statement.

caused by a breaking change in EF core 7.

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes#sqlserver-tables-with-triggers

By default, EF Core now saves changes via a significantly more efficient technique; unfortunately, this technique is not supported on SQL Server if the target table has database triggers, or certain types of computed columns.

the provided fix can be applied per table

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
                .ToTable(tb => tb.HasTrigger("SomeTrigger"));
}

The "SomeTrigger" can be any string.