[C#] How to Create Column Indexes in Entity Framework Core to Speed Up Searches

2026年1月12日

Overview

Properly creating indexes is essential for database performance tuning. In Entity Framework Core (EF Core), you can use the Fluent API to create indexes on specific columns or apply unique constraints. This article explains how to add index definitions during model building to improve search speed and maintain data integrity.

Specifications

Input: Specific properties of an entity class.
Process: Call HasIndex within the OnModelCreating method of the DbContext.
Output: Indexes are created on the specified columns when generating the database or applying migrations.

Basic Usage

Override the OnModelCreating method in your DbContext class and use the HasIndex method for the target entity.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Barcode) // Create an index
        .IsUnique();              // Apply unique constraint (no duplicates)
}

Full Code Example

The following code is a complete console application. It sets a unique index on the “Email” column and a named index on the “PunchTime” column for attendance logs. These indexes are reflected in the SQLite database when EnsureCreated() is called.

Note: Requires the Microsoft.EntityFrameworkCore.Sqlite package. dotnet add package Microsoft.EntityFrameworkCore.Sqlite

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace IndexSample
{
    // 1. Entity class definitions
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty; // Target for unique constraint
    }

    public class AttendanceLog
    {
        public int Id { get; set; }
        public int EmployeeId { get; set; }
        public DateTime PunchTime { get; set; } // Target for search index
        public string Note { get; set; } = string.Empty;
    }

    // 2. DbContext definition
    public class CompanyContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
        public DbSet<AttendanceLog> Logs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Use a SQLite file for testing
            optionsBuilder.UseSqlite("Data Source=company.db");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configuration for the Employee entity
            modelBuilder.Entity<Employee>()
                .HasIndex(e => e.Email) // Create an index on the Email column
                .IsUnique();            // Set unique constraint (errors on duplicates)

            // Configuration for the AttendanceLog entity
            modelBuilder.Entity<AttendanceLog>()
                .HasIndex(l => l.PunchTime)               // Create an index on the PunchTime column
                .HasDatabaseName("IX_Attendance_Time");   // Explicitly specify the index name
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new CompanyContext())
            {
                // Recreate the database to reflect schema changes
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
                Console.WriteLine("Database and indexes created.");

                try
                {
                    // Register valid data
                    var emp1 = new Employee { Name = "Alice", Email = "alice@example.com" };
                    var emp2 = new Employee { Name = "Bob", Email = "bob@example.com" };
                    context.Employees.AddRange(emp1, emp2);
                    
                    // Register log data
                    context.Logs.Add(new AttendanceLog 
                    { 
                        EmployeeId = 1, 
                        PunchTime = DateTime.Now, 
                        Note = "Arrived" 
                    });

                    context.SaveChanges();
                    Console.WriteLine("Initial data saved.");

                    // Test unique constraint violation
                    Console.WriteLine("Attempting to register a duplicate email address...");
                    var duplicateEmp = new Employee { Name = "Alice2", Email = "alice@example.com" };
                    context.Employees.Add(duplicateEmp);
                    context.SaveChanges(); // This will throw an exception
                }
                catch (DbUpdateException ex)
                {
                    Console.WriteLine($"[Expected Error] Failed to save data: {ex.InnerException?.Message}");
                    Console.WriteLine("The unique index prevented duplicate data.");
                }
            }
        }
    }
}

Execution Results Example

Database and indexes created.
Initial data saved.
Attempting to register a duplicate email address...
[Expected Error] Failed to save data: SQLite Error 19: 'UNIQUE constraint failed: Employees.Email'.
The unique index prevented duplicate data.

Customization Points

Composite Index: Used to speed up searches that combine multiple columns. modelBuilder.Entity<User>().HasIndex(u => new { u.FirstName, u.LastName });
Filtered Index: Creates an index only for rows meeting a specific condition, which reduces size and increases efficiency (supported by specific databases like SQL Server). modelBuilder.Entity<Product>().HasIndex(p => p.Url).HasFilter("[Url] IS NOT NULL");

Important Notes

Impact on Write Performance: While indexes speed up SELECT queries, they slow down INSERT, UPDATE, and DELETE operations because the index must also be updated. Do not create unnecessary indexes.
Consistency with Existing Data: If you set .IsUnique() on a column that already has duplicate data, an error will occur during migration. You must clean the data first.
Database Engine Limits: Maximum index key lengths and supported types vary by database (SQL Server, MySQL, PostgreSQL, SQLite). Be careful when indexing long strings.

Variations

Using Attributes

In EF Core 5.0 and later, you can define indexes directly in your entity class using the [Index] attribute. This keeps your code concise.

using Microsoft.EntityFrameworkCore;

// Specify indexes in the class definition
[Index(nameof(Email), IsUnique = true)]
[Index(nameof(Username))]
public class Account
{
    public int Id { get; set; }
    
    public string Username { get; set; }
    
    public string Email { get; set; }
}