Efcore: Use INSERT w/OUTPUT Instead of INSERT+SELECT

[bgribaudo]

Entity Framework Core's SQL Server provider (at least, as of v. 1.1.0) follows each INSERT statement with a SELECT statement that fetches database-generated values and verifies the inserted row count.

Proposal

Eliminate the SELECT statement. Instead:

• Fetch database-generated values using an OUTPUT clause on the INSERT statement.
• Rely on the database provider to throw an exception of the INSERT fails instead of verifying inserted row count (looks like was supposed to have been implemented per #2131).

Examples

Currently (EF Core 1.1.0)

-- Table w/identity column:
SET NOCOUNT ON;
INSERT INTO [TestTable1] ([Data])
VALUES (@p0);
SELECT [Id]
FROM [TestTable1]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

-- Table w/identity and default value (`DEFAULT(GETDATE())`) columns:
SET NOCOUNT ON;
INSERT INTO [TestTable2] ([Data])
VALUES (@p0);
SELECT [Id], [HasDefaultValue]
FROM [TestTable2]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

Proposed Simplification

````
-- Table w/identity column:
SET NOCOUNT ON;
INSERT INTO [TestTable1] ([Data])
OUTPUT INSERTED.[Id]
VALUES (@p0);

-- Table w/identity and default value (DEFAULT(GETDATE())) columns:
SET NOCOUNT ON;
INSERT INTO [TestTable2] ([Data])
OUTPUT INSERTED.[Id], INSERTED.[HasDefaultValue]
VALUES (@p0);
````

Example Code

Example.zip

Further technical details

EF Core version: 1.1.0
Operating system: Windows 10
Visual Studio version: VS 2015

[rowanmiller]

We used to take this approach, but it prevents you from using triggers (see https://github.com/aspnet/EntityFramework/issues/1441 for a complete history). We've profiled many approaches to this, and the performance difference between them all is negligible (a couple of percent either way).

[bgribaudo]

Thanks, @rowanmiller . This makes sense. I forgot about triggers and OUTPUT being mutually incompatible. :-(

[Coder3333]

I am getting heavy deadlocking issues from the 2 step approach, and this is when I am only doing 2 simultaneous inserts into a table. If EF were using the output clause to get the identity, instead of a separate query, I do not think I would have the deadlock issues. Would it make sense to add an option for developers to choose how the insert is handled for folks that are not concerned about triggers?