skip to Main Content

I created new ASP.Net core project with WebAPI. Using the below link.

jwt-authentication-with-aspnet-core-2-web-api-angular-5-net-core-identity-and-facebook-login

I have to use add-migration update-database manually.

Here I add one thing more that I don’t know how to debug app in VS Code. After created my project in VS Code I open my project in Visual Studio 2017.

When I about to run the update command Update-Database in Package Manager Console. I encounter below error also in my understanding the above command is for create the database and add migrations tables in it. (Correct me If I’m wrong).

 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.`

Also I share some of my code here and upload my app on 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; }
}

My Question is how I create my database using migration update command and what’s going wrong in my code.

2

Answers


  1. Chosen as BEST ANSWER

    I've found the answer of my question.

    I just need to change my DefaultConnection in both of below files:

    appsettings.json and appsettings.Development.json

    Before

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

    After

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

    Then run the below queries.

    dotnet ef migrations add initial
    dotnet ef database update
    

    and how its works. Here is an proved snapshot.

    enter image description here


  2. By removing MultipleActiveResultSets=true EntityFrameworkCore will not be able to retrieve multiple sets of data at the same time. You might want to add that back in.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search