In this article, I’ll show how to add indexes using EF Core. If you’re not sure about why you would need indexes, take a look at this article explaining how indexes greatly improve query performance.
Table of Contents
Add an index with a single column
The simplest way to add an index is to by adding the [Index] attribute on the model class and specifying which columns should be included in the index.
Here’s an example of adding an index with a single column:
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
[Index(nameof(Name))]
public class Movie
{
[Key]
public int Id { get; set; }
[Required]
[MaxLength(500)]
public string Name { get; set; }
[Required]
public int YearOfRelease { get; set; }
[Required]
[MaxLength(30)]
public string Description { get; set; }
[Required]
[MaxLength(100)]
public string Director { get; set; }
[Required]
public decimal BoxOfficeRevenue { get; set; }
}
Code language: C# (cs)After adding the [Index] attribute, you’ll need to generate a migration:
dotnet ef migrations add Database_v8
Code language: PowerShell (powershell)Take a look at the generated migration source code in <timestamp>_Database_v8.cs:
public partial class Database_v8 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateIndex(
name: "IX_Movies_Name",
table: "Movies",
column: "Name");
}
//Down() not shown
}
Code language: C# (cs)Now apply the migration programmatically or with the dotnet ef tool:
dotnet ef database update
Code language: PowerShell (powershell)After it applies the migration, take a look at the definition of the index it created in the database:
CREATE NONCLUSTERED INDEX [IX_Movies_Name] ON [dbo].[Movies]
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)Add a unique index with multiple columns
You can add multiple columns to the [Index] attribute and specify if this combo of columns should be considered unique:
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
[Index(nameof(Name), nameof(YearOfRelease), IsUnique = true)]
[Index(nameof(Name))]
public class Movie
{
[Key]
public int Id { get; set; }
[Required]
[MaxLength(500)]
public string Name { get; set; }
[Required]
public int YearOfRelease { get; set; }
[Required]
[MaxLength(30)]
public string Description { get; set; }
[Required]
[MaxLength(100)]
public string Director { get; set; }
[Required]
public decimal BoxOfficeRevenue { get; set; }
}
Code language: C# (cs)Note: There have been movies with the same name that came out in the same year (as I’ll show an example of in the next section). So this Name/YearOfRelease couldn’t be a unique index in the real world.
Now generate the migration:
dotnet ef migrations add Database_v9
Code language: PowerShell (powershell)Take a look at the generate migration source code in <timestamp>_Database_v9.cs:
public partial class Database_v9 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateIndex(
name: "IX_Movies_Name_YearOfRelease",
table: "Movies",
columns: new[] { "Name", "YearOfRelease" },
unique: true);
}
//Down() not shown
}
Code language: C# (cs)Then apply the migration:
dotnet ef database update
Code language: PowerShell (powershell)After it applies the migration, take a look at the definition of the index in the database:
CREATE UNIQUE NONCLUSTERED INDEX [IX_Movies_Name_YearOfRelease] ON [dbo].[Movies]
(
[Name] ASC,
[YearOfRelease] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)Notice that it created the index with the UNIQUE property. I’ll show below how this enforces uniqueness on this pair of columns.
Error when creating the unique index
When you try to apply the migration with the unique index, and there are duplicates for the index columns, then you’ll get this error:
Microsoft.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.Movies’ and the index name ‘IX_Movies_Name_YearOfRelease’. The duplicate key value is (Chaos, 2005).
This means you shouldn’t make your index unique.
If you’re in a dev / test environment and you run into this, then you could just clean up the duplicates in the test data. But more likely, the assumption about uniqueness is incorrect and you shouldn’t make the index unique.
Unique index prevents inserting duplicates
The unique index created above enforces uniqueness on the Name/YearOfRelease columns. So when you try to insert a Name/YearOfRelease combo that already exist, you’ll get the following exception:
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
—> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object ‘dbo.Movies’ with unique index ‘IX_Movies_Name_YearOfRelease’. The duplicate key value is (John Wick, 2014).
Add an index with included columns
You can sometimes optimize your queries by adding included columns to an index. You’ll have to try it out in your specific situation to see if it’ll help.
Let’s say you have the following query:
SELECT Name, BoxOfficeRevenue
FROM [dbo].[Movies]
WHERE Name = 'John Wick'
Code language: SQL (Structured Query Language) (sql)You want an index with Name and you want BoxOfficeRevenue to be an included column.
Unfortunately, the [Index] attribute doesn’t support this, so you have to override DbContext.OnModelCreating() and create the index in there.
Earlier in the article, the Name index was created by using an [Index] attribute. So the first step is to remove that:
[Index(nameof(Name), nameof(YearOfRelease), IsUnique = true)]
//[Index(nameof(Name))], delete this, since you need to create it in OnModelCreating instead
public class Movie
Code language: C# (cs)Now override OnModelCreating() in your DbContext class, and add the index with BoxOfficeRevenue as an included column:
public class StreamingServiceContext : DbContext
{
private readonly string ConnectionString;
public StreamingServiceContext(string connectionString)
{
ConnectionString = connectionString;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConnectionString);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Movie>()
.HasIndex(p => p.Name)
.IncludeProperties(nameof(Movie.BoxOfficeRevenue));
}
public DbSet<Movie> Movies { get; set; }
public DbSet<Show> Shows { get; set; }
public DbSet<Episode> Episodes { get; set; }
}
Code language: C# (cs)The IncludeProperties() method is what allows you to add included columns.
Now generate the migration:
dotnet ef migrations add Database_v10
Code language: PowerShell (powershell)Take a look at the generated migration source code in <timestamp>_Database_v10.cs. Notice that it added BoxOfficeRevenue as an included column:
public partial class Database_v10 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropIndex(
name: "IX_Movies_Name",
table: "Movies");
migrationBuilder.CreateIndex(
name: "IX_Movies_Name",
table: "Movies",
column: "Name")
.Annotation("SqlServer:Include", new[] { "BoxOfficeRevenue" });
}
//Down() not shown
}
Code language: C# (cs)Now apply the migration:
dotnet ef database update
Code language: PowerShell (powershell)Take a look at the definition of the index it created:
CREATE NONCLUSTERED INDEX [IX_Movies_Name] ON [dbo].[Movies]
(
[Name] ASC
)
INCLUDE([BoxOfficeRevenue])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)
Comments are closed.