How do I specify a schema for IdentityServer4 Entity Framework Core Migrations?

Question

I am using IdentityServer4 2.0.2, and have followed the QuickStart tutorial to use Entity Framework Core. I am trying to change from the default schema (dbo) to a custom schema in SQL Server. The following code is working correctly, instructing the DbContexts to look for the tables in the "idsrv4" schema.

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();

    var identityConnectionString = Configuration.GetConnectionString("Identity");
    var migrationsAssembly = typeof(Startup).GetTypeInfo().Assembly.GetName().Name;

    services.AddIdentityServer()
        .AddDeveloperSigningCredential()
        .AddTestUsers(Config.GetUsers())
        .AddConfigurationStore(options =>
        {
            options.DefaultSchema = "idsrv4";
            options.ConfigureDbContext = builder => builder.UseSqlServer(identityConnectionString,
                sql => sql.MigrationsAssembly(migrationsAssembly));

        })
        .AddOperationalStore(options =>
        {
            options.DefaultSchema = "idsrv4";
            options.ConfigureDbContext = builder => builder.UseSqlServer(identityConnectionString, 
                sql => sql.MigrationsAssembly(migrationsAssembly));                    
        });
}

In my development environment, I am initializing the database from the Configure() method in Startup.cs with the following code:

var context = serviceScope.ServiceProvider.GetRequiredService<ConfigurationDbContext>();
context.Database.Migrate();

The problem is that the tables are still being created in the dbo schema. How can I instruct the Migrate() method (from Microsoft.EntityFrameworkCore) to use the schema that I've provided?

Did you set `DefaultSchema` *before* you created your migration(s)? — Kirk Larkin, Dec 01 '17 at 17:36
I didn't manually create migrations. I just called Migrate() and my database was created. To test, I am dropping my database and running the app again. It creates the database when it hits the context.Database.Migrate() method call. Should I be looking for coded migrations somewhere? There isn't a Migrations folder in the project. Working with an EF model that is in a NuGet package is new territory. — LJFraney, Dec 01 '17 at 18:53
The [docs](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.migrate?view=efcore-2.0#Microsoft_EntityFrameworkCore_RelationalDatabaseFacadeExtensions_Migrate_Microsoft_EntityFrameworkCore_Infrastructure_DatabaseFacade_) state that `Migrate` *Applies any pending migrations for the context to the database. Will create the database if it does not already exist.*. This makes me think you must have migration classes somewhere that are already set to use `dbo`. — Kirk Larkin, Dec 01 '17 at 19:00
You are correct. I had created the migrations weeks ago, and forgot that the [QuickStart instructions had me put them in a folder named Data](http://docs.identityserver.io/en/release/quickstarts/8_entity_framework.html#adding-migrations),which was different than where I was used to. — LJFraney, Dec 01 '17 at 19:36
After deleting the migrations, I attempted to follow the instructions from the [QuickStart Tutorial](http://docs.identityserver.io/en/release/quickstarts/8_entity_framework.html#adding-migrations) by running the `dotnet ef migrations add ...` commands. This failed, because of my call to context.Database.Migrate() from inside Startup.Configure(). I had to comment the call to Migrate() in order to successfully recreate the migrations. Once recreated, they had the schema as intended. Then I was able to uncomment the call to Migrate() and run my application. — LJFraney, Dec 01 '17 at 20:27
[This](https://stackoverflow.com/questions/45941707/why-remove-migration-run-my-app/45942026#45942026) might be helpful. — Kirk Larkin, Dec 01 '17 at 21:28

aaronR · Answer 1 · 2017-12-01T19:06:34.757

Here is how I got it to work for .Net Core 1.1 and IdentityServer4 1.x

Added a section for my configuration to appsettings.json.

"IdentityServerConfig": {
    "DBConfig": {
      "IdentityServer": "Data Source=dbservername.database.windows.net;User ID=user_Name;Password=password;Initial Catalog=DBNAme;",
      "DefaultSchema": "IDSVR"
    },
    "CertificateConfig": {
      "Thumbprint": "",
      "FileName": "cert.pfx",
      "SubDirectory": "",
      "PassPhrase": "password"
    },
    "RaiseErrorEvents": true,
    "RaiseFailureEvents": true,
    "RaiseInformationEvents": true,
    "RaiseSuccessEvents": true
  }

Created a class for configuration settings for IdentityServer.

public class IdentityServerConfig
    {
        public CertificateConfig CertificateConfig { get; set; }
        public DBConfig DBConfig { get; set; }

        public bool RaiseErrorEvents { get; set; }
        public bool RaiseFailureEvents { get; set; }
        public bool RaiseInformationEvents { get; set; }
        public bool RaiseSuccessEvents { get; set; }
    }

public void ConfigureServices(IServiceCollection services)
{
     ...Other Code

     var identityServerConfig = config.GetSection("IdentityServerConfig").Get<IdentityServerConfig>();


    var migrationsAssembly = typeof(Startup).GetTypeInfo().Assembly.GetName().Name; 


    builder = services.AddIdentityServer(options =>
            {
                options.Events.RaiseErrorEvents = identityServerConfig.RaiseErrorEvents ;
                options.Events.RaiseFailureEvents = identityServerConfig.RaiseFailureEvents ;
                options.Events.RaiseInformationEvents = identityServerConfig.RaiseInformationEvents ;
                options.Events.RaiseSuccessEvents = identityServerConfig.RaiseSuccessEvents ;
            })

     .AddConfigurationStore(
                     b => b.UseSqlServer(identityServerConfig.DBConfig.IdentityServer ,
                     options =>
                     {
                         options.MigrationsAssembly(migrationsAssembly);
                     }), storeOption =>
                     {
                         storeOption.DefaultSchema = identityServerConfig.DBConfig.DefaultSchema ;//IDSVR
                     })
     .AddOperationalStore(
                 b => b.UseSqlServer(identityServerConfig.DBConfig.IdentityServer,
                 options => options.MigrationsAssembly(migrationsAssembly)
                 ), storeOption => storeOption.DefaultSchema = identityServerConfig.DBConfig.DefaultSchema //IDSVR
                 );
}

Are you using 2.0? I get "'ConfigurationStoreOptions' does not contain a definition for 'UseSqlServer'". This is the same solution that I've seen in other places, but I'm guessing there is a difference in 2.0. — LJFraney, Dec 01 '17 at 18:57
The 'ConfigurationStoreOptions' is a parameter for 'AddConfigurationStore()' — aaronR, Dec 01 '17 at 19:13

How do I specify a schema for IdentityServer4 Entity Framework Core Migrations?

1 Answers1