Merge Statement does not work on Views (Only 3+ items)

[Vaccano]

When my App inserts 1-2 items, the SQL generated uses normal INSERT statements (and everything works fine). For 3+ it uses a merge statement and the OUTPUT clause.

The generated merge statement does not work for queries that run on a View with an INSTEAD OF INSERT trigger. It gives the following error:

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

It would be nice to have a way to not use the Merge Statement.

Steps to reproduce

1. Setup some db objects like this:

CREATE SCHEMA sales
GO

-- Create the base table
CREATE TABLE sales.Widget_OLD(
	WIDGET_ID int NOT NULL,
	WIDGET_COST money NOT NULL
	CONSTRAINT PK_Widget PRIMARY KEY CLUSTERED (WIDGET_ID ASC) 
) 
GO

-- Create the overlay view
CREATE VIEW sales.Widget AS
	SELECT  widg.WIDGET_ID AS WidgetId, widg.WIDGET_COST AS WidgetCost
	FROM	sales.Widget_OLD widg
GO

-- create the instead of insert trigger

CREATE TRIGGER sales.InsertWidget ON sales.Widget
INSTEAD OF INSERT AS
BEGIN
	INSERT INTO sales.Widget_OLD (WIDGET_ID, WIDGET_COST)
	SELECT  Inserted.WidgetId, inserted.WidgetCost
	FROM    Inserted
END
GO

2. Create a basic EF Core Web API application to insert records into the tables.
3. Do a SaveChanges where you are inserting a bunch of records (has to be 3 or more)

Result: The following Error:
The column reference "inserted.WidgetId" is not allowed because it refers to a base table that is not being modified in this statement.

The following query also reproduces the error (when run after the above SQL):

DECLARE @inserted TABLE (WidgetId varchar(11) NOT null);

MERGE sales.Widget 
USING (
	VALUES ('19668651', 4.75))
	AS widg (WidgetId, WidgetCost) 
	ON 1=0
WHEN NOT MATCHED THEN
	INSERT (WidgetId, WidgetCost)
	VALUES (widg.WidgetId, widg.WidgetCost)
OUTPUT INSERTED.WidgetId
	INTO @inserted;
GO

Full Exception

Microsoft.EntityFrameworkCore.DbUpdateException
HResult=0x80131500
Message=An error occurred while updating the entries. See the inner exception for details.
Source=Microsoft.EntityFrameworkCore.Relational
StackTrace:
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple2 parameters) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList1 entries) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList1 entriesToSave)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
at ShipmentTrackingDal.ShipmentTrackingEntities.SaveChanges() in C:\src\shipment-tracking\shipment-tracking-service\ShipmentTrackingDal\ShipmentTrackingEntites.cs:line 50
at Breeze.Persistence.EFCore.EFPersistenceManager`1.SaveChangesCore(SaveWorkState saveWorkState)

Inner Exception 1:
SqlException: The column reference "inserted.WidgetId" is not allowed because it refers to a base table that is not being modified in this statement.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)"

Further technical details

EF Core version: 2.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.3.10

[ajcvickers]

Duplicate of #12064

[Vaccano]

For others that find their way here. This is a work around to this issue:

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

It forces EF Core to treat each row to be inserted as a batch. That causes it to only use insert statements (instead of Merge).

NOTE: This means that all inserts for your EF Core application will be inserts for each row. Not really performant for a large amount of inserts.