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

    Question

  •     

    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()
            {
                base.SaveChanges();
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.AddFromAssembly(typeof (IUnityDbContext).Assembly);
    
                base.OnModelCreating(modelBuilder);
            }
        }

    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:

    CreateTable(
                    "dbo.Strategies",
                    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

Answers

  • 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 :

    http://stackoverflow.com/questions/15248037/error-deploying-to-sql-azure-using-ef-6-alpha3-code-first-and-migrations-creatin
    Thursday, March 14, 2013 1:06 AM