Entity Framework 6.1 - Create Index with Include Statement

Entity Framework 6.1 - Create index with INCLUDE statement

Strictly speaking it has been always possible in Code First Migrations because you can run sql in a migration:

   public partial class AddIndexes : DbMigration
    {
        private const string IndexName = "IX_LogSamples";

        public override void Up()
        {
            Sql(String.Format(@"CREATE NONCLUSTERED INDEX [{0}]
                               ON [dbo].[Logs] ([SampleId],[Date])
                               INCLUDE ([Value])", IndexName));

        }

        public override void Down()
        {
            DropIndex("dbo.Logs", IndexName);
        }
    }

But I realise that you are probably actually asking if you can create an index using the IndexAttribute introduced in 6.1, but with an Include column - the answer to that is "No"

In Entity Framework 6.1 (not Core), how can I use the IndexAttribute to define a clustered index?

There can only be one clustered index on a table and by default Entity Framework/Sql Server puts it on the primary key.

So what use is the IsClustered attribute on an index that is not the primary key? Good question! (+1)

This class:

public class Blog
{
    [Key()]
    public int Id { get; set; }

    [MaxLength(256)]//Need to limit size of column for clustered indexes
    public string Title { get; set; }

    [Index("IdAndRating", IsClustered = true)]
    public int Rating { get; set; }

}

will generate this migration:

    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Title = c.String(maxLength: 256),
                    Rating = c.Int(nullable: false),
                });
            .PrimaryKey(t => t.Id)
            .Index(t => t.Rating, clustered: true, name: "IdAndRating");
    }

Alter the migration to this:

    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Title = c.String(maxLength: 256),
                    Rating = c.Int(nullable: false),
                });

        CreateIndex("dbo.Blogs", 
                    new[] { "Rating", "Title" }, 
                    clustered: true, 
                    name: "IdAndRating");

    }

And that should create your table without a primary key but with the clustered index on the other columns

EDIT
In your scenario where you don't need to insert, update or delete data, you don't need a full blown entity, you could use raw sql queries to populate the classes. You would need to add your own sql to the migration to create the table because EF won't automate it, but that means you can create the table and index just as you want it.

How to create index in Entity Framework 6.2 with fluent configuration

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.

Creating Unique Index with Entity Framework 6.1 fluent API

NOTE: Relevant to EF 6

You can use IndexAttribute as mentioned but with Fluent API instead of DataAnnotations which will do the trick:

modelBuilder 
    .Entity<Person>() 
    .Property(t => t.Name) 
    .HasColumnAnnotation( 
        "Index",  
        new IndexAnnotation(new IndexAttribute("IX_Name") { IsUnique = true }));

Unfortunately there is no other way to create unique indexes using Fluent API. There is an open issue regarding this feature: Unique Constraints (Unique Indexes)

---

UPDATE: Entity Framework Core
In the latest EF Core release you can rely on Fluent API to specify indexes without additional tricks.

HasIndex allows to define it:

modelBuilder 
    .Entity<Person>()
    .HasIndex(x => x.Name);

Hence it returs IndexBuilder object you can use it for further index configurations (i.e uniqueness):

modelBuilder 
    .Entity<Person>()
    .HasIndex(x => x.Name)
    .IsUnique();

How to create spatial index using EF 6.1 fluent API

Short answer- No, it is not. I have seen this tangentially referenced throughout blogs and have found no concrete examples of implementation. It seems to be related to the fact that spatial indexes are filtered indexes, which are not supported in Entity Framework.

As support for my answer I constructed a POC console app with the most recent version of Entity Framework (6.1). I took the following steps

1. Created a model that had a property of the type DbGeography
2. Enabled automatic migrations

3. Ran Update-Database -verbose insuring migration with the addition of an index was run. The index used the following:

   modelBuilder.Entity<LocationEntity>().Property(t => t.Coordinates).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("ix_locationentity_coordinates")));

No indexes were created, but neither did the app crash. I could try permutations on this, but my example seems to follow the convention of entity framework: Official Fluent Documentation

Entity Framework Filter Index

In EF 6.1, the working way to make the this work with Code First and DbMigrations is to use the Sql method in the DbMigration class:

public partial class AddIndexes : DbMigration
{
    public override void Up()
    {
        Sql(@"CREATE UNIQUE NONCLUSTERED INDEX
             [IX_DefaultLanguageApplicationId] ON [dbo].[Languages]
             (
                [IsDefaultLanguage] ASC,
                [ApplicationId] ASC 
             )
             WHERE ([IsDefaultLanguage]=(1))");

    }

    public override void Down()
    {
        DropIndex("dbo.Languages", "IX_DefaultLanguageApplicationId");
    }
}

But I realise that you are probably asking if you can create an index using the IndexAttribute introduced in 6.1, but with an Filter - the answer to that is "No"

Almost a duplicate of: Entity Framework 6.1 - Create index with INCLUDE statement

---

Related Topics