Index Attribute
Index Attribute
Entity Framework 6 provides the
Index attribute to create an index on a particular column in the database.1
public class Book
2
{
3
public int Id { get; set; }
4
[Index]
5
public string Title { get; set; }
6
public string Category { get; set; }
7
public int AuthorId { get; set; }
8
public Author Author { get; set; }
9
}
Copied!
But in EF Core, indexes cannot be created using data annotations. You have to use the Fluent API to specify an index on a column as shown below.
1
protected override void OnModelCreating(ModelBuilder modelBuilder)
2
{
3
modelBuilder.Entity<Book>()
4
.HasIndex(b => b.Title);
5
}
Copied!
Now in EF Core, the new
Index attribute can be placed on an entity type to specify an index for one or more columns.1
[Index(nameof(Title), IsUnique = true)]
2
public class Book
3
{
4
public int Id { get; set; }
5
public string Title { get; set; }
6
public string Category { get; set; }
7
public int AuthorId { get; set; }
8
public Author Author { get; set; }
9
}
Copied!
Now when you add a migration, you will see that the index is created for the
Title column.1
CREATE TABLE [dbo].[Books] (
2
[Id] INT IDENTITY (1, 1) NOT NULL,
3
[Title] NVARCHAR (450) NULL,
4
[Category] NVARCHAR (MAX) NULL,
5
[AuthorId] INT NOT NULL,
6
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED ([Id] ASC),
7
CONSTRAINT [FK_Books_Authors_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [dbo].[Authors] ([Id]) ON DELETE CASCADE
8
);
9
10
GO
11
CREATE NONCLUSTERED INDEX [IX_Books_AuthorId]
12
ON [dbo].[Books]([AuthorId] ASC);
13
14
GO
15
CREATE UNIQUE NONCLUSTERED INDEX [IX_Books_Title]
16
ON [dbo].[Books]([Title] ASC) WHERE ([Title] IS NOT NULL);
Copied!
You can also use the
Index attribute to specify an index spanning multiple columns.1
[Index(nameof(FirstName), nameof(LastName), IsUnique = true)]
2
public class Author
3
{
4
public int Id { get; set; }
5
public string FirstName { get; set; }
6
public string LastName { get; set; }
7
public ICollection<Book> Books { get; set; }
8
}
Copied!
For SQL Server, Migrations will then generate the following SQL.
1
CREATE TABLE [dbo].[Authors] (
2
[Id] INT IDENTITY (1, 1) NOT NULL,
3
[FirstName] NVARCHAR (450) NULL,
4
[LastName] NVARCHAR (450) NULL,
5
CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED ([Id] ASC)
6
);
7
8
GO
9
CREATE UNIQUE NONCLUSTERED INDEX [IX_Authors_FirstName_LastName]
10
ON [dbo].[Authors]([FirstName] ASC, [LastName] ASC)
11
WHERE ([FirstName] IS NOT NULL AND [LastName] IS NOT NULL);
Copied!
Last modified 10mo ago
Copy link
Contents
Index Attribute