📌 Indexes in EF Core

Indexes are one of the most powerful tools for query performance tuning.

In Entity Framework Core (EF Core), indexes help:

⚡ Speed up lookups, filtering, and sorting
🔒 Enforce uniqueness
🏢 Support multi-tenant and domain-specific rules

This guide walks through everything you need to know — from the basics to advanced scenarios — with real-world code samples and best practices.

✅ What is an Index in EF Core?

An index is a database object that allows the database to locate rows faster.

Use .HasIndex() → to create indexes via Fluent API Use .IsUnique() → to enforce uniqueness EF Core automatically creates indexes for foreign key columns

✅ Defining Indexes in EF Core

TypeDescription
Basic Index Creates a non-unique index on the Name column.
Unique Index Ensures no two rows share the same Sku (enforced at the DB level).
Composite Index (Multi-Column) Used when queries filter on multiple fields together.
Example: WHERE Name='Laptop' AND Category='Electronics'
Shadow Property Index Indexes a property not defined in the C# model.
Custom Index Name Useful when aligning with existing database conventions.

✅ Filtered Indexes (Manual SQL via Migrations)

EF Core does not support filtered indexes via Fluent API. Use raw SQL in migrations.

Common Use Cases:

  • Enforce uniqueness only for active rows (IsActive = 1)
  • Allow multiple NULLs, enforce uniqueness when NOT NULL:
CREATE UNIQUE INDEX IX_Unique_Email ON Users(Email) WHERE Email IS NOT NULL;

✅ Data Integrity with Indexes

MethodPurpose
.HasIndex().IsUnique() Creates a unique index (good for queries)
.HasAlternateKey() Creates a unique constraint (FK principal)

👉 Use .HasAlternateKey() when the column is involved in relationships.
👉 Use .HasIndex().IsUnique() for query optimization + uniqueness.

✅ Performance Considerations

✔️ Benefits❌ Downsides
  • Improves read/query speed
  • Crucial for large datasets or frequent filters
  • Slows down writes (INSERT, UPDATE, DELETE)
  • Consumes extra disk space
  • Too many indexes → may confuse the optimizer

👉 Best Practice: Only index columns you frequently filter, sort, or join on.

✅ Common Scenarios & EF Core Solutions

ScenarioEF Core Code
Single property index.HasIndex(p => p.Name)
Unique value.HasIndex(p => p.Sku).IsUnique()
Composite index.HasIndex(p => new { p.Name, p.Category })
Unique + nullable columnmigrationBuilder.Sql(...) (filtered index)
Shadow property index.HasIndex("ShadowPropertyName")
Only one active record per usermigrationBuilder.Sql(... WHERE IsActive=1)
Custom index name.HasDatabaseName("IX_Custom_Name")

✅ Frequently Asked Questions (FAQs)

QuestionAnswer
Can EF Core create multiple indexes on the same column?✅ Yes, but duplicates are prevented.
Does EF Core support index hints?❌ No. Use FromSqlRaw() for raw SQL with index hints.

Difference between .HasIndex().IsUnique() and .HasAlternateKey():

Feature.HasIndex().IsUnique().HasAlternateKey()
TypeUnique IndexUnique Constraint
Used for FKs?❌ No✅ Yes
Performance✅ Optimized for queries❌ Slightly heavier
Identity/Relationships❌ Not entity identity✅ Used for identity & FKs

🔄 Advanced Indexing Scenarios

FeatureSupport & Example
Clustered / Non-clustered✅ SQL Server (via migration SQL)
Covering Index (INCLUDE)✅ Use raw SQL (migrationBuilder.Sql(...))
Partial Index✅ PostgreSQL via raw SQL
JSON Column Indexing✅ Provider-specific (Postgres, SQL Server)
Descending Index (EF 7+).IsDescending(true, false)

📊 Quick Reference – Index Types

TypeSupported?Example
Single-column Index✅ Yes.HasIndex(p => p.Name)
Composite Index✅ Yes.HasIndex(p => new { p.Name, p.Category })
Unique Index✅ Yes.HasIndex(p => p.Sku).IsUnique()
Shadow Property Index✅ Yes.HasIndex("Sku")
Custom Index Name✅ Yes.HasDatabaseName("IX_Custom")
Filtered Index⚠️ ManualmigrationBuilder.Sql(...)
Covering Index⚠️ ManualmigrationBuilder.Sql(...)
Clustered Index⚠️ ManualSQL Server migration SQL
Descending Index✅ EF 7+.IsDescending(true, false)
Partial Index⚠️ ManualPostgreSQL raw SQL

📌 Key Takeaways

  • Use .HasIndex() → to define indexes
  • Use .IsUnique() → to enforce uniqueness
  • Use .HasAlternateKey() → when property acts as a principal key
  • Use filtered indexes → for conditional uniqueness (nullable fields, active-only rows)
  • Avoid over-indexing → balance read performance vs write efficiency

🔑 Final Thoughts

Indexes are easy to implement but extremely powerful for performance and integrity.

A solid indexing strategy can:

  • ✅ Boost query performance
  • ✅ Enforce domain-specific rules
  • ✅ Scale with your data

👉 Golden Rule: Index what you query — not what you hope you might query.

Comments

Post a Comment

Popular posts from this blog

Factory Method Design Pattern in .NET — Real-Time Finance Example

The Factory Method design pattern is a classic solution for a common software engineering challenge: how to create objects without tightly coupling the client code to their concrete types. Instead of instantiating classes directly using new, Factory Method defers object creation to subclasses or dedicated factory methods. In this blog post, we'll explore the Factory Method pattern in C# with a real-world banking transaction example and also discuss its benefits, drawbacks, and best practices.   💡 What Is the Factory Method Pattern? The Factory Method pattern provides an interface for creating objects in a superclass but allows subclasses or factory methods to alter the type of objects that will be created . This promotes: Loose coupling Polymorphism Improved testability and scalability   🎯 Purpose The key goal of Factory Method is to encapsulate object creation logic , so your business logic doesn’t depend on concrete classes. This becomes especially valuable...
Read more

Logging in .NET Core: Built-in Logging vs Serilog with Full Implementation Guide

Logging in .NET Core: Built-in Logging vs Serilog with Full Implementation Guide Logging is an essential aspect of application development. In .NET Core and beyond (.NET 5/6/7/8), Microsoft provides a built-in logging framework that is simple to use. However, when applications grow and logging needs become more advanced, third-party solutions like Serilog shine. In this blog, we will explore built-in logging, Serilog, their differences, and how to implement both in your .NET Core application.   1. .NET Core Built-in Logging Overview .NET Core uses Microsoft.Extensions.Logging as a base logging interface. This logging abstraction allows logging to multiple destinations, such as: Console Debug EventSource EventLog (Windows only) Azure Application Insights (via extensions) Files (through third-party integrations) Pros: Simple to use Built-in Fully integrated with Dependency Injection (DI) Cons: Limited formatting op...
Read more

Implementing Single Sign-On (SSO) in .NET Core and Angular

Implementing Single Sign-On (SSO) in .NET Core and Angular Introduction Single Sign-On (SSO) is a powerful authentication mechanism that enhances user experience and security by allowing users to log in once and access multiple applications without re-entering credentials. In this blog, we will walk through the implementation of SSO using OAuth 2.0 and OpenID Connect (OIDC) with IdentityServer4 in .NET Core and Angular. Why Use SSO? Improved User Experience : Users authenticate once and access multiple services without repeated logins. Centralized Authentication : Easier user management and security enforcement. Better Security : Reduces password fatigue and encourages strong authentication policies. Scalability : Works well across multiple applications, making it ideal for enterprise solutions. Step 1: Setting Up IdentityServer4 in .NET Core IdentityServer4 is an open-source framework that enables authentication and authorization using OpenID Connect and OAuth2. 1.1 Cr...
Read more