How to create index in Entity Framework 6.2 with code first

ef-code-first entity-framework entity-framework-6

Question

Is there a way to create an index on a property/column using code-first, instead of using the new IndexAttribute ?

1
112
3/25/2018 12:22:53 PM

Accepted Answer

Well 26.10.2017 Entity Framework 6.2 was officially released. It includes a possibility to define indexes with ease via Fluent API. Ho it is to use was already announced in the beta of 6.2.

Now you can use the HasIndex() method, followed by IsUnique() if it should be an unique index.

Just a small comparison (before/after) example:

// before 
modelBuilder.Entity<Person>()
        .Property(e => e.Name)
        .HasColumnAnnotation(
            IndexAnnotation.AnnotationName, 
            new IndexAnnotation(new IndexAttribute { IsUnique = true }));

// after
modelBuilder.Entity<Person>()
    .HasIndex(p => p.Name)
    .IsUnique();

// multi column index
modelBuilder.Entity<Person>()
    .HasIndex(p => new { p.Name, p.Firstname })
    .IsUnique();

It is also possible to mark the index as clustered with .IsClustered().


EDIT #1

Added an example for multi column index and additional information how to mark an index as clustered.


EDIT #2

As additional information, in EF Core 2.1 it is exactly the same like in EF 6.2 now.
Here is the MS Doc artcile as reference.

84
12/10/2018 7:39:20 AM

Popular Answer

Currently there is no "first class support" for creating a index via the fluent API, but what you can do is via the fluent API you can mark properties as having attributes from the Annotation API. This will allow you to add the Index attribute via a fluent interface.

Here are some examples from the work item from Issues site for EF.

Create a index on a single column:

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(new IndexAttribute()));

Multiple indexes on a single column:

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(new[]
            {
                new IndexAttribute("Index1"),
                new IndexAttribute("Index2") { IsUnique = true }
            }));

Multi-Column indexes:

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty1)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName,
        new IndexAnnotation(new IndexAttribute("MyIndex", 1)));

modelBuilder.Entity<MyEntity>()
    .Property(e => e.MyProperty2)
    .HasColumnAnnotation(
        IndexAnnotation.AnnotationName, 
        new IndexAnnotation(new IndexAttribute("MyIndex", 2)));

Using the above techniques will cause .CreateIndex() calls to be automatically created for you in your Up() function when you scaffold your next migration (or be automatically created in the database if you are not using migrations).



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow