Code First Automatic Migrations in SQL Azure - Tables without a clustered index are not supported



    I can't seem to get my Code-First Migration to successfully create tables in my SQL Azure database.

    It keeps complaining about SQL Azure's lack of support for tables without clustered indexes.

    public partial class IUnityDbContext : DbContext
            public IUnityDbContext()
                : base("Name=IUnityDbContext")
                Database.SetInitializer(new MigrateDatabaseToLatestVersion<IUnityDbContext, Configuration>());
            public DbSet<User> Users { get; set; }
            public virtual void Commit()
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
                modelBuilder.Configurations.AddFromAssembly(typeof (IUnityDbContext).Assembly);

    In Package Manager I "Enable-Migrations -EnableAutomaticMigrations" then "Add-Migration Initial". If I test on non Azure database the database and tables are successfully created and/or migrated when my application instantiate my DbContext. However, when I change the connection string to my Azure db I get


         Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

    The weird part is my migration DOES create a Primary Key clustered Index. So when I try to add a second one:

                    c => new
                            StrategyId = c.Int(nullable: false, identity: true),
                            StrategyName = c.String(maxLength: 450)
                    .PrimaryKey(s => s.StrategyId, clustered: true)
                    .Index(s => s.StrategyId, unique: true, clustered: true);

    It says a table cannot have 2 clustered indexed. Then if I set the Primary Key to "clustered: false" (and leave the other clustered Index configuration), it again complains about the table not having one!

    Even more so, when I look at the created non-Azure database from my pre-test, the Index is indeed labeled (Clustered) in SSMS under the "Index" folder . So why is Azure complaining???

    Please advise

    • Edited by tecktalk Thursday, March 14, 2013 12:27 AM
    Wednesday, March 13, 2013 1:59 AM


  • Ok I found the problem after checking out the generated Sql using Update-Database -Verbose. Turns out to be a bug in EF6- Alpha 3 where the __MigrationHistory table is created with a NONCLUSTERED primary key. I guess I shouldve mentioned I was using an alpha, it just slipped my mind.

    The workaround can be found here :
    Thursday, March 14, 2013 1:06 AM