Indexes are a common concept across many data stores, and data is stored in the form of records. Every record has a key field, which helps it to be recognized uniquely.
By convention, an index is created in each property (or set of properties) that are used as a foreign key.
Indexes cannot be created using data annotations, instead, you can use the Fluent API to specify an index on one or more columns.
You can also specify an index on a single column:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasIndex(a => a.Name);
}
You can also specify an index over more than one column:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasIndex(a => new { a.FirstName, a.LastName });
}
By default, indexes aren't unique: multiple rows are allowed to have the same value(s) for the index's column set. You can make an index unique as follows:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasIndex(a => a.Name)
.IsUnique();
}
Attempting to insert more than one entity with the same values for the index's column set will cause an exception to be thrown.
By convention, indexes created in a relational database are named IX_
You can use the Fluent API to set the name of the index created in the database:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasIndex(a => a.Name)
.HasName("Index_Name");
}
In some relational databases, you can specify a filtered or partial index to index only a subset of a column's values, reducing the index's size and improving both performance and disk space usage.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasIndex(a => a.Name)
.HasFilter("[Url] IS NOT NULL");
}
In SQL Server provider EF adds an 'IS NOT NULL' filter for all nullable columns that are part of a unique index. To override this convention you can supply a null value.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Author>()
.HasIndex(a => a.Name)
.IsUnique()
.HasFilter(null);
}
In some relational databases, you can configure a set of columns to include in the index, but aren't part of its "key". When all columns in the query are included in the index can significantly improve query performance.
In this example, the Url
column is part of the index key, so any query filtering on Url
can use the index. But also, queries accessing only the Title
and PublishedOn
columns will not need to access the table and will run more efficiently:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Book>()
.HasIndex(b => b.Url)
.IncludeProperties(b => new
{
b.Title,
b.PublishedOn
});
}