Column 'Id' in table 'AspNetRoles' is of a type that is invalid for use as a key column in an index

Question

I am using .net5.0 EF

I have a class AppUser and extend it to IdentityUser

public class AppUser : IdentityUser
{
}

I am using command to generate EF migrations dotnet ef migrations add "myMessage" -p Persistence -s API

I have deleted all previous migration files, so this is a new migration.

I also deleted the DB as well.

I am ablle to successfullly generate new db in sqlLite.

But when I am trying to do the same in production / testing server SQL, it gives me below issue.

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE TABLE [AspNetRoles] (
          [Id] TEXT NOT NULL,
          [Name] TEXT NULL,
          [NormalizedName] TEXT NULL,
          [ConcurrencyStamp] TEXT NULL,
          CONSTRAINT [PK_AspNetRoles] PRIMARY KEY ([Id])
      );
fail: API.Program[0]
      An error occured during migration
      Microsoft.Data.SqlClient.SqlException (0x80131904): Column 'Id' in table 'AspNetRoles' is of a type that is invalid for use as a key column in an index.
      Could not create constraint or index. See previous errors.
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 
         at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
         at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

---

The error relates to something in AspNetRoles table But I am not even touching anything in this AspNetRoles table.

Can anyone help me, please?

Thanks

---

Autogenerated migration code below

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "AspNetRoles",
                columns: table => new
                {
                    Id = table.Column<string>(type: "TEXT", nullable: false),
                    Name = table.Column<string>(type: "TEXT", maxLength: 256, nullable: true),
                    NormalizedName = table.Column<string>(type: "TEXT", maxLength: 256, nullable: true),
                    ConcurrencyStamp = table.Column<string>(type: "TEXT", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetRoles", x => x.Id);
                });

Answer 1

The migrations created for SQLite is not useable for Sql Server, so you have to create new migrations like this:

step 1: Change "UseSqlite" to "UseSqlServer"

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(connectionString));

To

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

step 2: Remove all migrations (by deleting the "Migrations" folder)

step 3: add a new migration, then update the database

dotnet ef migrations add "CreateIdentitySchema"
dotnet ef database update