Change IdentityServer4 Entity Framework table names

c# entity-framework entity-framework-core identityserver4 postgresql

Question

I am trying to change the default table names created by the PersistedGrantDb and ConfigurationDb for IdentityServer4 and have Entity Framework generate the correct SQL. For example; instead of the using the entity IdentityServer4.EntityFramework.Entities.ApiResource using the table ApiResources, I want the data to be mapped into a table named mytesttable

According to the documentation this should be as simple as adding ToTable invocations for each entity that I want to remap in the DBContext's OnModelCreating method to override the default behaviour of TableName = EntityName. The problem is that this does indeed create a table mytesttable but the SQL created by Entity Framework at runtime still uses ApiResources in the query and consequently fails.

The steps I've taken are I've created a DBContext that derives from IdentityServer's ConfigurationDbContext in order to be able to override OnModelCreating and customize the table names:

public class MyTestDbContext : ConfigurationDbContext
{
    public MyTestDbContext(DbContextOptions<ConfigurationDbContext> options, ConfigurationStoreOptions storeOptions) : base(options, storeOptions)
    { }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        Console.WriteLine("OnModelCreating invoking...");

        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<IdentityServer4.EntityFramework.Entities.ApiResource>().ToTable("mytesttable");

        base.OnModelCreating(modelBuilder);

        Console.WriteLine("...OnModelCreating invoked");
    }
}

I've also implemented a DesignTimeDbContextFactoryBase<MyTestDBContext> class to manufacture the MyTestDbContext instance when invoked at design time via the dotnet ef migrations command line syntax.

This works and an invocation of dotnet ef migrations add InitialIdentityServerConfigurationDbMigration -c MyTestDbContext -o Data/Migrations/IdentityServer/MyTestContext creates the initial migrations in my assembly.

I then start the IdentityServer instance, invoking a test method from Startup that contains the following logic:

private static void InitalizeDatabase(IApplicationBuilder app)
{
        using (var serviceScope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
         {

            serviceScope.ServiceProvider.GetRequiredService<PersistedGrantDbContext>().Database.Migrate();

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

            /* Add some test data here... */
        }
}

and this happily wanders through and creates the necessary tables in my PostGRES database using NpgSQL provider, including the table named mytesttable in place of ApiResources for the entity IdentityServer4.EntityFramework.Entities.ApiResource. However, when I invoke a command from the IdentityServer instance, the SQL that is generated is still referencing ApiResources instead of mytesttable:

  Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
  SELECT x."Id", x."Description", x."DisplayName", x."Enabled", x."Name"
  FROM "ApiResources" AS x
  ORDER BY x."Id"
  Npgsql.PostgresException (0x80004005): 42P01: relation "ApiResources" does not exist

Any assistance appreciated.

1
3
7/23/2018 4:58:55 PM

Accepted Answer

There is two parts to this answer; firstly the table names need to be adjusted in IdentityServer's configuration so that it generates queries using the new table names. Secondly; the schema generated by entity framework needs to be amended so that it knows to create the differently named tables for the Identity Framework entities. Read on...

So, first up; the ability to change the table names used in the Entity Framework queries is exposed on AddOperationalStore and AddConfigurationStore methods that hang off the AddIdentityServer middleware method. The options argument of the delegate supplied to the configuration methods exposes the table names, for example: options.{EntityName}.Name = {WhateverTableNameYouWantToUse} - or options.ApiResource.Name = mytesttable. You can also override the schema on a per table basis as well by adjusting the Schema property.

The example below uses reflection to update all the entites to use table names prefixed with idn_, so idn_ApiResources, idn_ApiScopes etc:

services.AddIdentityServer()
.AddConfigurationStore(options => {
                // Loop through and rename each table to 'idn_{tablename}' - E.g. `idn_ApiResources`
                foreach(var p in options.GetType().GetProperties()) {
                if (p.PropertyType == typeof(IdentityServer4.EntityFramework.Options.TableConfiguration))
                {
                    object o = p.GetGetMethod().Invoke(options, null);
                    PropertyInfo q = o.GetType().GetProperty("Name");

                    string tableName = q.GetMethod.Invoke(o, null) as string;
                    o.GetType().GetProperty("Name").SetMethod.Invoke(o, new object[] { $"idn_{tableName}" });

                }
            }

         // Configure DB Context connection string and migrations assembly where migrations are stored  
            options.ConfigureDbContext = builder => builder.UseNpgsql(_configuration.GetConnectionString("IDPDataDBConnectionString"),
                sql => sql.MigrationsAssembly(typeof(IdentityServer.Data.DbContexts.MyTestDbContext).GetTypeInfo().Assembly.GetName().Name));
}
.AddOperationalStore(options => { 

 // Copy and paste from AddConfigurationStore logic above.
}

The second part is to amend the schema generated by entity framework from the IdentityServer entities. To accomplish this you've got two choices; you can either derive from one of IdentityServer's supplied DBContexts; ConfigurationDbContext or PeristedGrantDbContext and then override the OnModelCreating method to remap each IdentityServer entity to the modified table name and then create your initial migration or updated migration as documented here (Fluent Api syntax), or you can create the initial migration from the supplied IdentityServer DBContext's ConfigurationDbContext and PersistedGrantDbContext as per the tutorial Adding Migrations section, and then just do a find and replace with a text editor on all the table names and references to those table names in the created migration files.

Whichever method you choose you will still need to use the dotnet ef migrations ... command line syntax to create either the initial migration files as shown in Adding Migrations or a modified set with table changes and once you've done this, run your IdentityServer project and the schema will be created in the target database.

Note; OnModelCreating is invoked via the dotnet ef migrations syntax (aka at Design Time) and also at runtime if you call Database.Migrate() on your DBContext - E.g. MyDbContextInstance.Database.Migrate() (or the async equivalent method).

If you want to use a custom DBContext so you can customise OnModelCreating, you need to add a few design time classes which are used when you call dotnet ef from the command line and add the new context to Startup.

For the sake of completeness below is a hacky rough example where the context target is a PostGres database (use UseSQLServer in place of UseNpgsql or whatever your backing store is if it differs) and the connection string name is IDPDataDBConnectionString in the appsettings.json file and the custom DB context in this case is MyTestDbContext which derives from IdentityServer's ConfigurationDbContext.

Copy and paste the code, adjust the path to appsettings.json (or refactor) and then from the command line execute dotnet ef migrations add InitialIdentityServerConfigurationDbMigration -c MyTestDbContext -o Data/Migrations/IdentityServer/ConfigurationDbCreatedWithMyTestContext and you should see the Entity Framework generate the schema migration files using whatever overrides you've placed in OnModelCreating on your derived context. The example below also includes some Console.WriteLine invocations to make it easier to track what's going on.

Add this to Startup:

 services.AddDbContext<MyTestDbContext>(options =>
        {
            options.UseNpgsql(_configuration.GetConnectionString("IDPDataDBConnectionString"));
        }); 

Note the use of the design time classes also allows you to separate your IdentityServer database migration files into a separate class library if you like. Make sure you target it in Startup if you do this (See here for more info).

namespace MyIdentityServer.DataClassLibrary.DbContexts
{

public class MyTestDbContext : ConfigurationDbContext
{
    public MyTestDbContext(DbContextOptions<ConfigurationDbContext> options, ConfigurationStoreOptions storeOptions) : base(options, storeOptions)
    { }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        Console.WriteLine("OnModelCreating invoking...");

        base.OnModelCreating(modelBuilder);

        // Map the entities to different tables here
        modelBuilder.Entity<IdentityServer4.EntityFramework.Entities.ApiResource>().ToTable("mytesttable");

        Console.WriteLine("...OnModelCreating invoked");
    }

}
public class MyTestContextDesignTimeFactory : DesignTimeDbContextFactoryBase<MyTestDbContext>
{

    public MyTestContextDesignTimeFactory()
        : base("IDPDataDBConnectionString", typeof(MyTestContextDesignTimeFactory).GetTypeInfo().Assembly.GetName().Name)
    {
    }

    protected override MyTestDbContext CreateNewInstance(DbContextOptions<MyTestDbContext> options)
    {
        var x = new DbContextOptions<ConfigurationDbContext>();

        Console.WriteLine("Here we go...");

        var optionsBuilder = newDbContextOptionsBuilder<ConfigurationDbContext>();

        optionsBuilder.UseNpgsql("IDPDataDBConnectionString", postGresOptions => postGresOptions.MigrationsAssembly(typeof(MyTestContextDesignTimeFactory).GetTypeInfo().Assembly.GetName().Name));

        DbContextOptions<ConfigurationDbContext> ops = optionsBuilder.Options;

        return new MyTestDbContext(ops, new ConfigurationStoreOptions());
    }
}




/* Enable these if you just want to host your data migrations in a separate assembly and use the IdentityServer supplied DbContexts 

public class ConfigurationContextDesignTimeFactory : DesignTimeDbContextFactoryBase<ConfigurationDbContext>
{

    public ConfigurationContextDesignTimeFactory()
        : base("IDPDataDBConnectionString", typeof(ConfigurationContextDesignTimeFactory).GetTypeInfo().Assembly.GetName().Name)
    {
    }

    protected override ConfigurationDbContext CreateNewInstance(DbContextOptions<ConfigurationDbContext> options)
    {
        return new ConfigurationDbContext(options, new ConfigurationStoreOptions());
    }
}

public class PersistedGrantContextDesignTimeFactory : DesignTimeDbContextFactoryBase<PersistedGrantDbContext>
{
    public PersistedGrantContextDesignTimeFactory()
        : base("IDPDataDBConnectionString", typeof(PersistedGrantContextDesignTimeFactory).GetTypeInfo().Assembly.GetName().Name)
    {
    }

    protected override PersistedGrantDbContext CreateNewInstance(DbContextOptions<PersistedGrantDbContext> options)
    {
        return new PersistedGrantDbContext(options, new OperationalStoreOptions());
    }
}
*/

public abstract class DesignTimeDbContextFactoryBase<TContext> :
IDesignTimeDbContextFactory<TContext> where TContext : DbContext
{
    protected string ConnectionStringName { get; }
    protected String MigrationsAssemblyName { get; }
    public DesignTimeDbContextFactoryBase(string connectionStringName, string migrationsAssemblyName)
    {
        ConnectionStringName = connectionStringName;
        MigrationsAssemblyName = migrationsAssemblyName;
    }

    public TContext CreateDbContext(string[] args)
    {
        return Create(
            Directory.GetCurrentDirectory(),
            Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT"),
            ConnectionStringName, MigrationsAssemblyName);
    }
    protected abstract TContext CreateNewInstance(
        DbContextOptions<TContext> options);

    public TContext CreateWithConnectionStringName(string connectionStringName, string migrationsAssemblyName)
    {
        var environmentName =
            Environment.GetEnvironmentVariable(
                "ASPNETCORE_ENVIRONMENT");

        var basePath = AppContext.BaseDirectory;

        return Create(basePath, environmentName, connectionStringName, migrationsAssemblyName);
    }

    private TContext Create(string basePath, string environmentName, string connectionStringName, string migrationsAssemblyName)
    {
        var builder = new ConfigurationBuilder()
            .SetBasePath(basePath)
            .AddJsonFile(@"c:\change\this\path\to\appsettings.json")
            .AddJsonFile($"appsettings.{environmentName}.json", true)
            .AddEnvironmentVariables();

        var config = builder.Build();

        var connstr = config.GetConnectionString(connectionStringName);

        if (String.IsNullOrWhiteSpace(connstr) == true)
        {
            throw new InvalidOperationException(
                "Could not find a connection string named 'default'.");
        }
        else
        {
            return CreateWithConnectionString(connstr, migrationsAssemblyName);
        }
    }

    private TContext CreateWithConnectionString(string connectionString, string migrationsAssemblyName)
    {
        if (string.IsNullOrEmpty(connectionString))
            throw new ArgumentException(
         $"{nameof(connectionString)} is null or empty.",
         nameof(connectionString));

        var optionsBuilder =
             new DbContextOptionsBuilder<TContext>();

        Console.WriteLine(
            "MyDesignTimeDbContextFactory.Create(string): Connection string: {0}",
            connectionString);

        optionsBuilder.UseNpgsql(connectionString, postGresOptions => postGresOptions.MigrationsAssembly(migrationsAssemblyName));

        DbContextOptions<TContext> options = optionsBuilder.Options;

        Console.WriteLine("Instancing....");

        return CreateNewInstance(options);
    }
}

}

Side note; If you've already got a database with the IdentityServer tables in, you can just rename them manually ignoring EntityFrameworks migrations - the only bit you'll then need is the changes in Startup to AddConfigurationStore and AddOperationalStore.

4
7/27/2018 4:50:19 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow