Wednesday, July 20, 2022

Entity Framework Core 7 Preview 6: Performance Improvements

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
A couple of interesting questions here. Do we need a transaction here that initiates 2 roundtrips to the database? Since there is only one atomic operation with a single statement, we really don't need it. It's either going to pass or fail and not going to cause any data integrity issue.

Now let's run the same code using EF Core 7.0.

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

Saturday, July 16, 2022

Visual Studio 2022 Version 17.3 Preview 3: IEnumerable Debugger Visualizer Improvements

In this post, let's have a look at one of the nicest features in Visual Studio 2022 Preview that got improved with Visual Studio 2022 version 17.3 Preview 3

Visual Studio 2022 version 17.3 Preview 2 introduced IEnumerable Debugger Visualizer that can be used to view .NET collections in a separate Window. The IEnumerable visualizer was displaying collections of objects in a table with a row per object and sortable columns for each field and property. And we were able to export the collection to an Excel file.

Visual Studio 2022 version 17.3 Preview 3 has some nice improvements to IEnumerable Debugger Visualizer, now we can search the collection within the Visualizer. And also we can export the collection to a CSV file as well.

Following is a quick tour of the newest feature of IEnumerable Debugger Visualizer. (please click on the image in order to have good visibility of the image)
IEnumerable Debugger Visualizer
Start using Visual Studio 2022 version 17.3 Preview 3 today.

Happy Coding.

Regards,
Jaliya

Wednesday, July 6, 2022

.NET 7 Preview 5: Using [AsParameters] Attribute for Parameter Binding in Minimal APIs

In this post let's go through this new attribute [AsParameters] that got introduced in .NET 7 Preview 5. This attribute is especially for Minimal APIs in ASP.NET Core.

Let's consider the following endpoint using Minimal APIs.

app.MapGet("ToDos"async (ToDoDbContext dbContextint offsetint limit, ILogger<Program> logger) =>
{
    logger.LogInformation("Getting ToDos with Offset: '{Offset}', Limit: '{Limit}'",
        offset,
        limit);
 
    List<ToDo> todos = await dbContext.ToDos
        .OrderBy(x => x.Id)
        .Skip(offset)
        .Take(limit)
        .ToListAsync();
 
    return TypedResults.Ok(todos);
});

With the new [AsParameters] attribute, we can move all the parameters into a POCO type.

app.MapGet("ToDos/WithRequest"async ([AsParameters] GetRequest request) =>
{
    request.Logger.LogInformation("Getting ToDos with Offset: '{Offset}', Limit: '{Limit}'",
        request.Offset,
        request.Limit);
 
    List<ToDo> todos = await request.DbContext.ToDos
        .OrderBy(x => x.Id)
        .Skip(request.Offset)
        .Take(request.Limit)
        .ToListAsync();
 
    return TypedResults.Ok(todos);
});
 
record struct GetRequest(ToDoDbContext DbContext    int Offset    int Limit, 
    ILogger<GetRequest> Logger);

Here I have used a record struct, but classes are also supported. The recommendation is to use record struct to avoid additional memory allocation.

And on top of that, we can apply other binding attributes (FromHeaderFromQuery, FromServices, etc.), something like below.

record struct GetRequest(ToDoDbContext DbContext,
    int Offset,
    int Limit,
    [FromHeader(Name = "X-OrganizationId")] string? OrganizationId,
    ILogger<GetRequest> Logger);

Here the OrganizationId will get picked from HTTP Request Header (if present). 

The following rules are applied during the parameter binding.

Structs

  • A declared public parameterless constructor will always be used if present.
  • A public parameterized constructor will be use if a single constructor is present and all arguments have a matching (case-insensitive) public property.
    • If a constructor parameter does not match with a property, InvalidOperationException will be thrown if binding is attempted.
  • Since struct always has a default constructor, the default constructor will be used if it is the only one present or more than one parameterized constructor is present.
  • When binding using a parameterless constructor all public settable properties will be bound.
Classes
  • A public parameterless constructor will be used if present.
  • A public parameterized constructor will be used if a single constructor is present and all arguments have a matching (case-insensitive) public property.
    • If a constructor parameter does not match with a property, InvalidOperationException will be thrown if binding is attempted.
  • Throw InvalidOperationException when more than one parameter is declared and the parameterless constructor is not present.
  • Throw InvalidOperationException if a suitable constructor cannot be found.

Hope this helps.

Happy Coding.

Regards,
Jaliya

Tuesday, July 5, 2022

Received Microsoft MVP Award in Developer Technologies

I am humbled and honored once again to receive the precious Microsoft Most Valuable Professional (MVP) Award for the ninth consecutive year.

This year, the announcement of new MVPs took a bit longer (usually it's on 07/01), but finally got the email.

As always looking forward to another great year on top of Microsoft Development Stack.
Microsoft Most Valuable Professional (MVP)
Thank you Microsoft for your appreciation and Thank you everyone for your continuous support.

Happy Coding.

Regards,
Jaliya