asp.net-core entity-framework-core ef-code-first mysql-workbench asp.net-core-3.1

The User table is not created in MySql database using Entity Framework Core 3.1 (Code-first approach )

How can I create user table in MySql database using Code First approach and Entity Framework Core 3.1?

"DataAccessMySqlProvider": "server=localhost;database=jahan_alpha;uid=xxxx;password=xxxx" in appsettings.json

And in startup.cs:

public void ConfigureServices(IServiceCollection services)
{
   services.AddDbContext<ApplicationDbContext>(options =>
            options.UseMySQL(Configuration["ConnectionStrings:DataAccessMySqlProvider"],
            b => b.MigrationsAssembly("Alpha.DataAccess")));
   // .UseMySQL is in Microsoft.EntityFrameworkCore namespace
}

I run Add-Migration Init-MySql-database and then Update-database in Package Manager Console. After doing that, I got this error:

PM> Update-Database Build started... Build succeeded.
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'ApplicationDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options:
MigrationsAssembly=Alpha.DataAccess 
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1,299ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `jahan_alpha`; Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9,281ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `__EFMigrationsHistory` (
          `MigrationId` varchar(95) NOT NULL,
          `ProductVersion` varchar(32) NOT NULL,
          CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`)
      ); Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (118ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='jahan_alpha' AND TABLE_NAME='__EFMigrationsHistory';
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `MigrationId`, `ProductVersion`
      FROM `__EFMigrationsHistory`
      ORDER BY `MigrationId`; Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20200515231101_Init-MySql'. Applying migration '20200515231101_Init-MySql'. fail:
Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (181ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE `User` DROP INDEX `UserNameIndex`; Failed executing DbCommand (181ms) [Parameters=[], CommandType='Text',
CommandTimeout='30'] ALTER TABLE `User` DROP INDEX `UserNameIndex`;
MySql.Data.MySqlClient.MySqlException (0x80004005): Table
'jahan_alpha.user' doesn't exist  --->
MySql.Data.MySqlClient.MySqlException (0x80004005): Table
'jahan_alpha.user' doesn't exist
    at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
    at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
    at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
    at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 226
    at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 74
    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
    at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
    at Pomelo.EntityFrameworkCore.MySql.Migrations.Internal.MySqlMigrator.Migrate(String targetMigration)
    at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
    at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
    at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
    at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Table 'jahan_alpha.user' doesn't exist

ApplicationDbContext:

namespace Alpha.DataAccess
{
    public class ApplicationDbContext : IdentityDbContext<User, Role, int, UserClaim, UserRole, UserLogin, RoleClaim, UserToken>
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

        }
        // some codes...

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // it should be placed here, otherwise it will rewrite the following settings!
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<RoleClaim>(builder =>
            {
                builder.HasOne(roleClaim => roleClaim.Role).WithMany(role => role.Claims).HasForeignKey(roleClaim => roleClaim.RoleId);
                builder.ToTable("RoleClaim");
            });
            modelBuilder.Entity<Role>(builder =>
            {
                builder.ToTable("Role");
            });
            modelBuilder.Entity<UserClaim>(builder =>
            {
                builder.HasOne(userClaim => userClaim.User).WithMany(user => user.Claims).HasForeignKey(userClaim => userClaim.UserId);
                builder.ToTable("UserClaim");
            });
            modelBuilder.Entity<UserLogin>(builder =>
            {
                builder.HasOne(userLogin => userLogin.User).WithMany(user => user.Logins).HasForeignKey(userLogin => userLogin.UserId);
                builder.ToTable("UserLogin");
            });

            modelBuilder.Entity<User>(builder =>
            {
                builder.ToTable("User"); //.HasMany(e => e.Comments).WithOne().OnDelete(DeleteBehavior.Cascade);
            });
            modelBuilder.Entity<UserRole>(builder =>
            {
                builder.HasOne(userRole => userRole.Role).WithMany(role => role.Users).HasForeignKey(userRole => userRole.RoleId);
                builder.HasOne(userRole => userRole.User).WithMany(user => user.Roles).HasForeignKey(userRole => userRole.UserId);
                builder.ToTable("UserRole");
            });
            modelBuilder.Entity<UserToken>(builder =>
            {
                builder.HasOne(userToken => userToken.User).WithMany(user => user.UserTokens).HasForeignKey(userToken => userToken.UserId);
                builder.ToTable("UserToken");
            });

            // some codes...
        }

        public static async Task CreateAdminAccount(IServiceProvider serviceProvider, IConfiguration configuration)
        {
            UserManager<User> userManager = serviceProvider.GetRequiredService<UserManager<User>>();
            RoleManager<Role> roleManager = serviceProvider.GetRequiredService<RoleManager<Role>>();

            string userName = configuration["Data:AdminUser:Name"];
            string email = configuration["Data:AdminUser:Email"];
            string password = configuration["Data:AdminUser:Password"];
            string role = configuration["Data:AdminUser:Role"];

            if (await userManager.FindByNameAsync(userName) == null)
            {
                if (await roleManager.FindByNameAsync(role) == null)
                {
                    await roleManager.CreateAsync(new Role(role));
                }

                User user = new User
                {
                    Email = email,
                    UserName = userName
                };
                var result = userManager.CreateAsync(user, password);
                if (result.IsCompletedSuccessfully)
                {
                    await userManager.AddToRoleAsync(user, role);
                }
            }
        }
    }
}

Solution

Based on the fact that the first command executed is ALTER DATABASE, I assume 1) that you already had a manually generated database with the table User when you generated the initial migration and 2) then you removed the database 3) then you ran the Update-Database command.

Think of migrations as incremental patches moving the database from one stable state into another. If you want to see the actual SQL that would be run when updating the database, you can issue the dotnet ef migrations script -i -o migrations.sql command. This will generate a complete script with all the migrations from initial to current state.

I further assume that you actually have a DbSet<User> property within your ApplicationDbContext somewhere in the hidden // some codes... code block

As already pointed out in comments, what you need to do is;

drop the jahan_alpha database from server localhost
remove the Migrations folder entirely from within your project
run dotnet ef migrations add initial
run dotnet ef migrations script -i -o migrations.sql
verify that the SQL commands in the migrations.sql file are correct (for example CREATE DATABASE before any instance of ALTER DATABASE)
now run dotnet ef database update - this will generate the jahan_alpha database with the schema defined in your code-first project