CODEWITHSUNDEEP
entity-frameworkentity-framework-6ef-code-first
Valo
Valo

Reputation: 2048

How to create index in Entity Framework 6.2 with fluent configuration

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

Upvotes: 135

Views: 129592

Answers (11)

Nigrimmist
Nigrimmist

Reputation: 12378

Currently, on .net core it will be :

[Index(nameof(AdvId), IsUnique = true)]
public class tblAdvertisement
{
    public int Id { get; set; }
    public int AdvId { get; set; }
    public DateTime Created { get; set; }
}

So, attribute set is on class, not on member. Output migration looks like this after :

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateIndex(
        name: "IX_Advertisements_AdvId",
        table: "Advertisements",
        column: "AdvId",
        unique: true);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropIndex(
        name: "IX_Advertisements_AdvId",
        table: "Advertisements");

}

Upvotes: 0

ChW
ChW

Reputation: 3348

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.

Upvotes: 114

Nayas Subramanian
Nayas Subramanian

Reputation: 2379

You can use one of this

// Indexes

 this.HasIndex(e => e.IsActive)
            .HasName("IX_IsActive");

or 

  this.Property(e => e.IsActive).HasColumnAnnotation(
            "Index",
            new IndexAnnotation(new IndexAttribute("IX_IsActive")));

Upvotes: 2

Darie Dorlus
Darie Dorlus

Reputation: 397

From EF 6.1 onward the attribute [Index] is supported.
Use [Index(IsUnique = true)] for unique index.
Here is the link from Microsoft

public class User 
{ 
    public int UserId { get; set; } 

    [Index(IsUnique = true)] 
    [StringLength(200)] 
    public string Username { get; set; } 

    public string DisplayName { get; set; } 
}

Upvotes: 27

Scott Chamberlain
Scott Chamberlain

Reputation: 127563

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).

Upvotes: 88

Omid.Hanjani
Omid.Hanjani

Reputation: 1522

I write an extension method for use in fluent EF to avoid extra code:

public static PrimitivePropertyConfiguration HasIndexAnnotation(
    this PrimitivePropertyConfiguration primitivePropertyConfiguration, 
    IndexAttribute indexAttribute = null
    )
{
    indexAttribute = indexAttribute ?? new IndexAttribute();

    return primitivePropertyConfiguration
        .HasColumnAnnotation(
            IndexAnnotation.AnnotationName, 
            new IndexAnnotation(indexAttribute)
        );
}

then use it like this:

Property(t => t.CardNo)
    .HasIndexAnnotation();

or like this if index needs some configs:

Property(t => t.CardNo)
    .HasIndexAnnotation(new IndexAttribute("IX_Account") { IsUnique = true });

Upvotes: 1

Hugo Hil&#225;rio
Hugo Hil&#225;rio

Reputation: 2908

Without an explicit name:

[Index]
public int Rating { get; set; }

With a specific name:

[Index("PostRatingIndex")] 
public int Rating { get; set; }

Upvotes: 26

Guilherme Ferreira
Guilherme Ferreira

Reputation: 1613

Entity Framework 6

Property(c => c.MyColumn)
        .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_MyIndex")));

And add using:

using System.Data.Entity.Infrastructure.Annotations;
using System.ComponentModel.DataAnnotations.Schema;

Upvotes: 8

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241525

I've created a some extension methods and wrapped them in a nuget package to make this much easier.

Install the EntityFramework.IndexingExtensions nuget package.

Then you can do the following:

public class MyDataContext : DbContext
{
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Customer>()
        .HasIndex("IX_Customers_Name",          // Provide the index name.
            e => e.Property(x => x.LastName),   // Specify at least one column.
            e => e.Property(x => x.FirstName))  // Multiple columns as desired.

        .HasIndex("IX_Customers_EmailAddress",  // Supports fluent chaining for more indexes.
            IndexOptions.Unique,                // Supports flags for unique and clustered.
            e => e.Property(x => x.EmailAddress)); 
  }
}

The project and source code are here. Enjoy!

Upvotes: 36

Emre
Emre

Reputation: 388

You can use the INDEX data annotaion Code First Data Annotations

Upvotes: 7

Mert Akcakaya
Mert Akcakaya

Reputation: 3139

If you don't want to use attributes on your POCO's, then you can always do it like the following:

context.Database.ExecuteSqlCommand("CREATE INDEX IX_NAME ON ...");

You can execute this statement in your custom DbInitializer derived class. I don't know any Fluent API way of doing this though.

Upvotes: 3

Related Questions