Entity Framework Core 7 (EF7) Preview 6 was released and it contains a bunch of performance improvements by reducing unnecessary roundtrips to the database and by improved queries. Please note: most of the improvements here are tight to Microsoft SQL Server.
First, let's start with a simple example.
Inserting a single record
Let's consider the following code that inserts a single record into the database.
ToDo toDo = new() { Title = "ToDo 1" }; await context.ToDos.AddAsync(toDo); await context.SaveChangesAsync();
Now let's have a look at the SQL statements that are being executed when using both EF Core 6.0 and 7.0.
EF Core 6.0
dbug: 20/07/2022 07:49:14.672 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction) Began transaction with isolation level 'ReadCommitted'. ... info: 20/07/2022 07:49:14.748 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (24ms) [Parameters=[@p0='ToDo 1' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; INSERT INTO [ToDos] ([Title]) VALUES (@p0); SELECT [Id] FROM [ToDos] WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity(); ... dbug: 20/07/2022 07:49:14.779 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction) Committed transaction.
So what's happening here.
- Trip to the database to begin a transaction
- Trip to the database with 2 SQL statements (I am ignoring SET NOCOUNT ON;)
- Insert the record into the database
- Select the Id of the inserted record (that's because when the Primary Key is an integer, by default it's set up as an IDENTITY column and we may require it for application logic)
- Trip to the database to commit the transaction
EF Core 7.0
info: 20/07/2022 07:53:51.959 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (25ms) [Parameters=[@p0='ToDo 1' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; INSERT INTO [ToDos] ([Title]) OUTPUT INSERTED.[Id] VALUES (@p0);
There's clearly a difference between the above SQL statement and the SQL statements generated when using EF Core 6.0.
- A single trip to the database and there's no transaction
- A new statement SET IMPLICIT_TRANSACTIONS OFF is executed. SQL Server has an opt-in “Implicit Transactions” mode, where executing a statement outside of a transaction won’t auto-commit, but instead implicitly start a new transaction. EF is disabling it so that the changes are getting saved.
- Instead of inserting and then selecting the IDENTITY value, the new SQL uses an “OUTPUT clause” to tell SQL Server to send the value directly from the INSERT.
Now, let's have a look at a bit more advanced example. That is what if we are doing multiple inserts?
Inserting multiple records
for (int i = 1; i <= 4; i++) { ToDo toDo = new() { Title = $"ToDo {i}" }; await context.ToDos.AddAsync(toDo); } await context.SaveChangesAsync();
Now again let's compare the SQL Statements from both EF Core 6.0 and 7.0.
EF Core 6.0
dbug: 20/07/2022 09:22:18.194 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction) Began transaction with isolation level 'ReadCommitted'. ... info: 20/07/2022 09:22:18.259 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (27ms) [Parameters=[@p0='ToDo 1' (Size = 4000), @p1='ToDo 2' (Size = 4000), @p2='ToDo 3' (Size = 4000), @p3='ToDo 4' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]); MERGE [ToDos] USING ( VALUES (@p0, 0), (@p1, 1), (@p2, 2), (@p3, 3)) AS i ([Title], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Title]) VALUES (i.[Title]) OUTPUT INSERTED.[Id], i._Position INTO @inserted0; SELECT [i].[Id] FROM @inserted0 i ORDER BY [i].[_Position]; ... dbug: 20/07/2022 09:22:18.318 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction) Committed transaction.
So what's happening here.
- Trip to the database to begin a transaction
- Trip to the database with 3 SQL statements
- Declare a temporary table @inserted0
- Single MERGE statement that uses an “OUTPUT clause” to insert the INSERTED.[Id] to temporary table @inserted0.
- SELECT the Ids from the temporary table @inserted0
- Trip to the database to commit the transaction
EF Core 7.0
info: 20/07/2022 09:25:56.651 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (22ms) [Parameters=[@p0='ToDo 1' (Size = 4000), @p1='ToDo 2' (Size = 4000), @p2='ToDo 3' (Size = 4000), @p3='ToDo 4' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; MERGE [ToDos] USING ( VALUES (@p0, 0), (@p1, 1), (@p2, 2), (@p3, 3)) AS i ([Title], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Title]) VALUES (i.[Title]) OUTPUT INSERTED.[Id], i._Position;
Here again, we don't need a Transaction.
- A single trip to the database and there's no transaction
- A new statement SET IMPLICIT_TRANSACTIONS OFF is executed for the same reason mentioned in the initial scenario.
- No use of temporary tables here. A single MERGE statement that uses an “OUTPUT clause” to tell SQL Server to send back all the INSERTED.[Id].
As you can see, with EF Core 7 unnecessary roundtrips to the database are reduced, and generated SQL statements are improved giving us an improvement in the performance.
Read More,
Announcing Entity Framework Core 7 Preview 6: Performance Edition
Happy Coding.
Regards,
Jaliya