Entity Framework Core Code-First - Generate Covering Index with Columns Included

In SQL Server or some other relational databases, it is a very common requirement to create covering index with columns included in index pages beside the index key columns. With Entity Framework Core, you can also easily generate covering indexes using purely C# code.

Scenario

For Kontext website, backend database storage is Azure SQL Database. Most recently, Azure portal recommends creating two indexes to improve query performances. The recommended indexes details look like the following screenshots:

Both recommendations are covering indexes. 

Create indexes using T-SQL

Azure portal also allows us to directly apply these indexes to the database using T-SQL.

For example, the following statements are generated by Azure:

CREATE NONCLUSTERED INDEX [nci_wi_FlexContents_673086CD9DE40ABE97C36E561E7D87B6] ON [flex].[FlexContents] ([Blocked], [IsDeleted], [IsPublished], [Tag]) INCLUDE ([SiteID]) WITH (ONLINE = ON)


CREATE NONCLUSTERED INDEX [nci_wi_FlexActions_6E4A2EE58FF9DE15144A33EDD89D277E] ON [flex].[FlexActions] ([IsDeleted], [SiteID], [ContentID]) INCLUDE ([DateCreated], [Type]) WITH (ONLINE = ON)

I can directly apply these indexes into database however since I'm using code-first approach, I will use Entity Framework APIs to implement this changes.

Entity Framework Core Code-First

Microsoft official documentation shows how to use code-first to create indexes with included columns. 

Refer to the following page for more details:

Indexes

warning Once we add database specific implementations into the model, your code will only work with those databases. This is one drawback of this approach. However, you can always define macros to build your project differently. Refer to this page for more details: #if (C# reference).

Constraints

As included column is not implemented in all databases, thus this API (IncludeProperties) is not directly included Entity Framework core libraries and you need to included the related database extension packages into your project references.

For my case, I need to include SQL Server package:

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.5" />

If you use other databases, please change the package reference accordingly.

Implement this change

With the extensions methods added by the database specific package, I can now easily add this packages via the following code:

builder.Entity<FlexContent<TKey>>(entity =>
            {
                // Performance improvement index
                entity.HasIndex(e => new { e.Blocked, e.IsDeleted, e.IsPublished, e.Tag })
                .IncludeProperties(e => e.SiteID);

            });

builder.Entity<FlexAction<TKey>>(entity =>
            {
                // Performance improvement index
                entity.HasIndex(e => new { e.IsDeleted, e.SiteID, e.ContentID })
                .IncludeProperties(e => new { e.DateCreated, e.Type });

            });

Generate migrations using CLI

After the models are updated, I can use entity framework CLI to generate migrations:

dotnet ef migrations add FlexContentActionPerformanceIndex

After this, the website project will have a few migrations classes generated automatically as the following screenshot shows:

Apply changes to database

We can run CLI command to apply the changes to databases in different environment:

 dotnet ef database update

The command line generates the following output:

It executes the two statements to create indexes and then insert a migration record into __EFMigrationsHistory table in the database. 

Summary

As shown in the above code snippets, it is very easy to use Entity Framework Core APIs to create indexes in the data model. With IncludeProperties API implemented some database specific packages, you can also create indexes with columns included.

If you prefer to give it a name for your indexes, you can use HasName API. 

* This page is subject to Site terms.

More from Kontext

local_offer asp.net core local_offer asp.net core 3

visibility 11
thumb_up 0
access_time 25 days ago

Sign-in with social accounts like Google, Microsoft, Twitter and Facebook accounts are very commonly used in websites to allow website users to logon easily without registering an separate account. Issue summary During the implementation of Kontext Google sign-in function, I encoun...

open_in_new ASP.NET Core

local_offer Azure local_offer kontext

visibility 9
thumb_up 0
access_time 3 months ago

In the past few months, Kontext's DNS server was not very stable as it went offline several times accidentally. When the DNS server is down, our domain (kontext.tech) cannot be parsed successfully to the IP address of Azure App Service (the service that hosts this website); thus it became unreach...

open_in_new Kontext Information

Modern Web Application - Azure Blob Storage for Uploaded Files

local_offer Azure local_offer asp.net core local_offer dotnetcore

visibility 275
thumb_up 0
access_time 3 months ago

With cloud platforms like Azure, we can totally separate user content storage from web application storage to decouple components from each other and to make the application easy to scale and deploy. This article provides detailed information with code snippets about how to use Azure server-less product Blob Storage and App Service to enable horizontally scalable web application for users to upload files (BLOBs).

open_in_new Azure

Azure SQL Database Automated Backup Strategy

local_offer Azure local_offer SQL Server

visibility 79
thumb_up 0
access_time 3 months ago

When designing the architecture of Kontext platform, Azure SQL Database is chosen as the storage for relational data. TDE and other advanced security features are always enabled to protect the database. Backup plans are also employed to ensure I can always restore the database for as point of tim...

open_in_new Azure

info About author

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward