Why doesn't Entity Framework 6 select just scope_identity() after insert?

Question

When you're saving an entity using EF 6.1, the following SQL Code is generated and executed:

exec sp_executesql N'INSERT [dbo].[Customers]([Name], [FirstName])
VALUES (@0, @1)
SELECT [CustomerId]
FROM [dbo].[Customers]
WHERE @@ROWCOUNT > 0 AND [CustomerId] = scope_identity()',N'@0 nvarchar(max) ,@1 nvarchar(max) ',@0=N'Wenk',@1=N'Manuel'

I understand, that the insert/select is done, to retrieve the value of the CustomerId Column immediatley after saving. As far as I know, scope_identity() returns the value, so why isn’t there something like

SELECT scope_identity()

instead of all that stuff that require physical reads?

Cheers, Manuel

Answer 1

Correct. scope_identity() is there to get the generated CustomerId value, so EF can use it as entity key. I think, but I have to guess because it's not documented, the SELECT is done on the Customer table to make sure the retrieved scope_identity() really is related to CustomerId. There may be cases that an INSERT triggers more inserts so scope_identity() is allotted to another record.

A second reason to query the Customer table is that this query is generated in one method that may also add computed columns to the SELECT clause. It was probably more convenient to query the entity table anyway.

The WHERE @@ROWCOUNT > 0 clause is added to make sure that the expected number of rows is affected by the INSERT statement. In EF's source code there is a comment:

Note that we filter on rowcount to ensure no rows are returned if no rows were modified.