40

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])
Gapipro
  • 1,913
  • 2
  • 22
  • 34

1 Answers1

48

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"

Gary Pendlebury
  • 336
  • 5
  • 8
Colin
  • 22,328
  • 17
  • 103
  • 197
  • 8
    Yes IndexAttribute is what I was aiming for. But looks like we still can't have nice things =) – Gapipro Feb 26 '14 at 10:46
  • 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. – AakashM Mar 07 '19 at 12:09