11

I am having the following error after typing update-database:

Cannot create more than one clustered index on table 'dbo.AppUsers'. Drop the existing clustered index 'PK_dbo.AppUsers' before creating another.

I am working on an Azure mobile service.

I have three data models:

public class AppUser : EntityData
{
    public string Username { get; set; }
    public virtual ICollection<RatingItem> userRatings { get; set; }
}

public class PlaceItem : EntityData
{
    public string PlaceName { get; set; }
    public int Group { get; set; }
    public string XCoordinate { get; set; }
    public string YCoordinate { get; set; }
}

public class RatingItem : EntityData
{
    public int Ratings { get; set; }
    public string PlaceId { get; set; }
    public AppUser user { get; set; }
}

It has to do with migration because :

  • The initial create is in the _MigrationHistory table, but isn't in the migration folder in the solution explorer.
  • When I add-migration AddAll, I don't get any errors, and AddAll appears in the migration folder, but not in the table.

In the context file:

public class ICbackendContext : DbContext
{
        public DbSet<AppUser> AppUsers { get; set; }
        public DbSet<PlaceItem> PlaceItems { get; set; }
        public DbSet<RatingItem> RatingItems { get; set; }

}

Elie Saad
  • 302
  • 4
  • 16

2 Answers2

29

Generally, this error message is caused by not running the Mobile Apps/Mobile Services DB generator. Entity Framework does not have an annotation for creating a clustered index that is not a primary key, so the mobile server SDK manually creates the right SQL statements to set CreatedAt as a non-primary key clustered index.

To resolve this, run the database generator before migrations are applied. In the Migrations\Configuration.cs file, include the following:

public Configuration()
{
   AutomaticMigrationsEnabled = false;
   SetSqlGenerator("System.Data.SqlClient", new EntityTableSqlGenerator());
}

To learn more, see How to make data model changes to a .NET backend mobile service. The topic applies to both Mobile Services and Mobile Apps, though some namespaces are different in Mobile Apps.

Stefan Wexel
  • 1,154
  • 1
  • 8
  • 14
lindydonna
  • 3,874
  • 17
  • 26
  • Should I only use it once? I think I forgot to run Database.SetInitializer the first time, I directly commented out and created a DBMigrator. – Elie Saad Mar 26 '16 at 08:48
  • This should be in your configuration file, so it will be run every time you do a migration. – lindydonna Mar 29 '16 at 00:32
  • 1
    Whoa, this is black magic. (In my case, I was trying to add Azure Mobile Apps to an existing EF database) – tofutim Oct 24 '16 at 17:09
  • 1
    @lindydonna-msft I have exactly the same line in Configuration file, but I'm still getting this error. Do you have any idea what might be wrong? – user963935 Apr 18 '17 at 09:09
  • @user963935 When generating code first migration, make sure to have `modelBuilder.Conventions.Add( new AttributeToColumnAnnotationConvention( "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));` in your `protected override void OnModelCreating(DbModelBuilder modelBuilder)` method on the `DbContext` config class. This will generate the correct migration code. It worked for me. – gorillapower May 15 '17 at 10:19
  • Actually my final solution was to use OfflineSync in a fresh solution - all the errors went away. – user963935 May 16 '17 at 13:47
2

As stated by @gorillapower in comments, this piece of code is also very important.

modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>( "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));

inside your 

protected override void OnModelCreating(DbModelBuilder modelBuilder)

in the DbContext config class. Do not forget to regenerate migrations.

Gabriel Robert
  • 3,012
  • 2
  • 18
  • 36