Why doesn't Entity Framework 6 only select scope_identity () after insertion?

Question

When you save an object using EF 6.1, the following SQL code is created 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 insertion / selection is done to immediately get the CustomerId Column value after saving. As far as I know, scope_identity () returns a value, so why not something like

 SELECT scope_identity() 

instead of all that requires physical reading?

Cheers, Manuel

Answer 1

Right. scope_identity() exists to get the generated CustomerId value, so EF can use it as an entity key. I think, but I have to guess, because it is not documented, SELECT is performed in the Customer table to make sure that the extracted scope_identity() actually associated with CustomerId . There may be times when INSERT triggers more inserts, so scope_identity() is allocated to another record.

The second reason for querying the Customer table is that this query is generated by a single method, which can also add computed columns to the SELECT clause. It would probably be more convenient to always query the entity table.

The WHERE @@ROWCOUNT > 0 added to ensure that the expected number of rows depends on the INSERT . There is a comment in the EF source code:

Note that we are filtering on rowcount to ensure that no rows will be returned if no rows have been changed.