ASP.Net Core 2中不使用迁移命令Update-Database创建数据库

Question

我使用以下链接创建了一个新的ASP.Net Core项目，其中包括WebAPI：jwt-authentication-with-aspnet-core-2-web-api-angular-5-net-core-identity-and-facebook-login。我必须手动使用add-migration和update-database命令。此外，我不知道如何在VS Code中调试应用程序。我在VS Code中创建了我的项目，但是在运行Package Manager控制台中的Update-Database命令时遇到以下错误（如果我理解正确，该命令用于创建数据库并在其中添加迁移表）。

 fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
  Failed executing DbCommand(3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
  CREATE TABLE[AspNetRoles] (

     [Id] nvarchar(450) NOT NULL,
     [ConcurrencyStamp] nvarchar(max) NULL,
      [Name] nvarchar(256) NULL,
      [NormalizedName] nvarchar(256) NULL,
      CONSTRAINT[PK_AspNetRoles] PRIMARY KEY([Id])
  );
 System.Data.SqlClient.SqlException(0x80131904): There is already an object named 'AspNetRoles' in the database.

 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)

 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action 1 wrapCloseInAction)

 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

 at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

 at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)

 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

 at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)

 ClientConnectionId:56b94f04-73ff-4391-87fc-36a6c256c650

 Error Number:2714,State:6,Class:16

 System.Data.SqlClient.SqlException(0x80131904): There is already an object named 'AspNetRoles' in the database.

 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)

 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)

 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

 at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

 at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)

 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

 at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)

 at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)

 at      Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)


 at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection)

 at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)

 at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)

 at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()

 at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

 ClientConnectionId:56b94f04-73ff-4391-87fc-36a6c256c650

 Error Number:2714, State:6, Class:16

 There is already an object named 'AspNetRoles' in the database.`

我在这里分享一些我的代码，并在github上上传了我的应用程序。

program.cs

namespace server
{
    public class Program
    {
        public static void Main(string[] args)
        {
            BuildWebHost(args).Run();
        }

        public static IWebHost BuildWebHost(string[] args) =>
             WebHost.CreateDefaultBuilder(args)
            .UseStartup<Startup>()
            .Build();

    }
}

Startup.cs

public class Startup
{
    private const string SecretKey = "iNivDmHLpUA223sqsfhqGbMRdRj1PVkH"; // todo: get this from somewhere secure
    private readonly SymmetricSecurityKey _signingKey = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(SecretKey));

    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
                b => b.MigrationsAssembly("server")));

        services.AddSingleton<IJwtFactory, JwtFactory>();

        // Register the ConfigurationBuilder instance of FacebookAuthSettings
        services.Configure<FacebookAuthSettings>(Configuration.GetSection(nameof(FacebookAuthSettings)));

        services.TryAddTransient<IHttpContextAccessor, HttpContextAccessor>();

        // jwt wire up
        // Get options from app settings
        var jwtAppSettingOptions = Configuration.GetSection(nameof(JwtIssuerOptions));

        // Configure JwtIssuerOptions
        services.Configure<JwtIssuerOptions>(options =>
        {
            options.Issuer = jwtAppSettingOptions[nameof(JwtIssuerOptions.Issuer)];
            options.Audience = jwtAppSettingOptions[nameof(JwtIssuerOptions.Audience)];
            options.SigningCredentials = new SigningCredentials(_signingKey, SecurityAlgorithms.HmacSha256);
        });

        var tokenValidationParameters = new TokenValidationParameters
        {
            ValidateIssuer = true,
            ValidIssuer = jwtAppSettingOptions[nameof(JwtIssuerOptions.Issuer)],

            ValidateAudience = true,
            ValidAudience = jwtAppSettingOptions[nameof(JwtIssuerOptions.Audience)],

            ValidateIssuerSigningKey = true,
            IssuerSigningKey = _signingKey,

            RequireExpirationTime = false,
            ValidateLifetime = true,
            ClockSkew = TimeSpan.Zero
        };

        services.AddAuthentication(options =>
        {
            options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
            options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;

        }).AddJwtBearer(configureOptions =>
        {
            configureOptions.ClaimsIssuer = jwtAppSettingOptions[nameof(JwtIssuerOptions.Issuer)];
            configureOptions.TokenValidationParameters = tokenValidationParameters;
            configureOptions.SaveToken = true;
        });

        // api user claim policy
        services.AddAuthorization(options =>
        {
            options.AddPolicy("ApiUser", policy => policy.RequireClaim(Constants.Strings.JwtClaimIdentifiers.Rol, Constants.Strings.JwtClaims.ApiAccess));
        });

        // add identity
        var builder = services.AddIdentityCore<EmployeeProfile>(o =>
        {
            // configure identity options
            o.Password.RequireDigit = false;
            o.Password.RequireLowercase = false;
            o.Password.RequireUppercase = false;
            o.Password.RequireNonAlphanumeric = false;
            o.Password.RequiredLength = 6;
        });
        builder = new IdentityBuilder(builder.UserType, typeof(IdentityRole), builder.Services);
        builder.AddEntityFrameworkStores<ApplicationDbContext>().AddDefaultTokenProviders();

        services.AddAutoMapper();
        services.AddMvc().AddFluentValidation(fv => fv.RegisterValidatorsFromAssemblyContaining<Startup>());
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
        }

        app.UseExceptionHandler(
            builder =>
            {
                builder.Run(
                    async context =>
                        {
                            context.Response.StatusCode = (int)HttpStatusCode.InternalServerError;
                            context.Response.Headers.Add("Access-Control-Allow-Origin", "*");

                            var error = context.Features.Get<IExceptionHandlerFeature>();
                            if (error != null)
                            {
                                context.Response.AddApplicationError(error.Error.Message);
                                await context.Response.WriteAsync(error.Error.Message).ConfigureAwait(false);
                            }
                        });
            });

        app.UseAuthentication();
        app.UseDefaultFiles();
        app.UseStaticFiles();
        app.UseMvc();
    }
}

ApplicationDbContext.cs

public class ApplicationDbContext : IdentityDbContext<EmployeeProfile>
{
    public ApplicationDbContext(DbContextOptions options)
        : base(options)
    {
    }

    public DbSet<EmployeeProfile> EmployeeProfiles { get; set; }
}

我的问题是如何使用迁移更新命令创建我的数据库，以及我的代码出了什么问题。

- Ahmer Ali Ahsan

---

你尝试通过代码创建上下文了吗？ - agua from mars

你尝试使用命令行 dotnet ef database update 了吗？ - agua from mars

@agua from mars，是的，我尝试了通过代码上下文以及使用上述更新命令，但在我的情况下都没有起作用。请克隆我的项目并给出您的代码审查，也许我漏掉了什么。 - Ahmer Ali Ahsan

当我尝试使用 dotnet ef database update 命令时，我收到以下错误信息：找不到包 Microsoft.VisualStudio.Web.CodeGeneration.Tools 的版本。请先解决这个问题。 - agua from mars

您需要安装Install-Package Microsoft.EntityFrameworkCore.Tools.DotNet -Version 2.0.1，并将其条目添加到.csproj文件中（如果未自动添加）。另外，我已经解决了我的问题，请参见我的答案。感谢您的关注和时间。 - Ahmer Ali Ahsan

Answer 1

我已经找到了我的问题的答案。

我只需要在以下两个文件中更改我的DefaultConnection：

appsettings.json 和 appsettings.Development.json

之前的内容

"ConnectionStrings": {
"DefaultConnection": "Server=(LocalDb)\\MSSQLLocalDB;Database=webapi;Trusted_Connection=True;MultipleActiveResultSets=true"
}

之后

"ConnectionStrings": {
"DefaultConnection": "Server=.;Database=WebApiEfCore;Trusted_Connection=True;"
}

然后运行以下查询。

dotnet ef migrations add initial
dotnet ef database update

它是如何工作的，这里有一个已验证的快照。

- Ahmer Ali Ahsan

Answer 2

通过移除 MultipleActiveResultSets=true，EntityFrameworkCore 将不能同时检索多个数据集。您可能希望将其添加回去。

- Kevin C.