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);
                });

CodePudding user response：

I think for sql server's text type index is maximum length required. using FluentApi you can set maxLength for Id.

public class AppDbContext
{
        ....

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Role>().Property(x => x.Id).HasMaxLength(250);

        base.OnModelCreating(builder);
    }
}

and re-migration created required.

CodePudding user response：

This error is due to the fact that the migration code EF generates for SQLite is different from the migration code generated for SQL Server. Check your builder configuration logic (Program.cs or Startup.cs) and make sure that you are specifying the correct database options when invoking AddDbContext. When using a SQL Server database you'll need to specify that as follows:

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

For comparison to your SQLite migration code, this is what the SQL Server migration code looks like for the default AspNetRoles table:

migrationBuilder.CreateTable(
    name: "AspNetRoles",
    columns: table => new
    {
        Id = table.Column<string>(type: "nvarchar(450)", nullable: false),
        Name = table.Column<string>(type: "nvarchar(256)", maxLength: 256, nullable: true),
        NormalizedName = table.Column<string>(type: "nvarchar(256)", maxLength: 256, nullable: true),
        ConcurrencyStamp = table.Column<string>(type: "nvarchar(max)", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_AspNetRoles", x => x.Id);
    });