Create index with included columns in (old) Entity Framework 6 code-first configuration

Question

So now that Indexes are available in latest beta version of Entity Framework 6.1 is it even possible to create an index in code first approach that is equal to this SQL statement?

CREATE NONCLUSTERED INDEX [Sample1]
ON [dbo].[Logs] ([SampleId],[Date])
INCLUDE ([Value])

Gary Pendlebury · Accepted Answer · 2015-03-02 11:31:24Z

52

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"

edited Mar 2, 2015 at 11:31

Gary Pendlebury

4156 silver badges11 bronze badges

answered Feb 26, 2014 at 10:40

Colin

22.7k17 gold badges111 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gapipro Over a year ago

Yes IndexAttribute is what I was aiming for. But looks like we still can't have nice things =)

AakashM Over a year ago

An important thing to note is that while this approach works, in that the index does get created, there's no difference in the Model in __MigrationHistory, so Database.CompatibleWithModel will return true both before and after this migration. So you can find yourself unexpectedly running against an unindexed database.

Henrik Høyer · Accepted Answer · 2024-08-07 07:21:59Z

2

This question is regarding .NET Framework. People on .NET Core can use the IncludeProperties function like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasIndex(p => p.Url)
        .IncludeProperties(
            p => new { p.Title, p.PublishedOn });
}

See the Microsoft documentation

edited Aug 7, 2024 at 7:21

answered Jan 11, 2024 at 10:19

Henrik Høyer

1,8191 gold badge26 silver badges31 bronze badges

2 Comments

Gennadiy Over a year ago

The question was about Entity Framework 6 not about Entity Framework Core

raznagul Apr 16 at 13:45

+1 while the question is not strictly about EF core, this question is still one of the first google results when searching for EF core, making this answer very helpful.

Collectives™ on Stack Overflow

Create index with included columns in (old) Entity Framework 6 code-first configuration

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related