Configure ASP.NET Core 2 MVC Identity to use Postgresql

A guide to customising the Identity to use long as primary key, different schema name, postgresql naming conventions and hilo id genaration

I will explain the simple steps required to modify the ASP.NET Core 2 MVC Identity template to work with Postgresql.

Add support for postgresql

1. Create an ASP.NET Core 2 Application with Identity Support

Make sure that you are selecting 'Individual User Accounts' as your authentication method. New ASP.NET Core 2 Application with Identity Support

2. Install support for Postgresql

Open the Nuget Package Manager under References->Manage Nuget Packages and look for (and install) Npgsql.EntityFrameworkCore.PostgreSQL and Npgsql.EntityFrameworkCore.PostgreSQL.Design

3. Register postgresql in your Startup file

Open the file Startup.cs file and look for the following statement:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

You will need to change the UseSqlServer line to UseNpgsql like in the following example:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));

4. Update your connection strings to point towards postgresql

You will find the connection strings in the file appsettings.json. My connection strings looks like this:

  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-IdentityAndPostgresql-116DE96C-1C1E-4571-9A1A-470E286FAE14;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

Update them to point to postgresql like this:

  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=5432;Database=aspnet-IdentityAndPostgresql;User Id=postgres;Password=yourpassword;"
  }

Customize the model

1. Use long as id for the ApplicationUser

Application user class from:

    public class ApplicationUser : IdentityUser
    {
    }

To an inherited class from the generic version of IdentityUser like:

    public class ApplicationUser : IdentityUser<long>
    {
    }

2. Modify some classes coming from the template to support long as ID

Go to the UrlHelperExtensions class and update it to support long as the userId

public static string EmailConfirmationLink(this IUrlHelper urlHelper, long userId, string code, string scheme)

And

public static string ResetPasswordCallbackLink(this IUrlHelper urlHelper, long userId, string code, string scheme)

Then finally modify the ManageController.cs and locate the method LinkLoginCallback.

Modify the follwing line:

var info = await _signInManager.GetExternalLoginInfoAsync(user.Id);

To something like this:

var info = await _signInManager.GetExternalLoginInfoAsync(user.Id.ToString());

3. Add long as ID for the remaining Identity classes

Add a new class version for you other Identity models

Include a file next to ApplicationUser.cs and name it IdentityModels.cs. Put this code inside of it:

namespace IdentityAndPostgresql.Models
{
    using Microsoft.AspNetCore.Identity;

    public class UserLogin : IdentityUserLogin<long> { }
    public class UserRole : IdentityUserRole<long> { }
    public class UserClaim : IdentityUserClaim<long> { }
    public class Role : IdentityRole<long> { }
    public class RoleClaim : IdentityRoleClaim<long> { }
    public class UserToken : IdentityUserToken<long> { }
}

Modify the startup class

Go back to the Startup class and look for the following code:

    services.AddIdentity<ApplicationUser, IdentityRole>()
        .AddEntityFrameworkStores<ApplicationDbContext>()
        .AddDefaultTokenProviders();

Now modify it so it looks like this:

    services.AddIdentity<ApplicationUser, IdentityRole<long>>()
        .AddEntityFrameworkStores<ApplicationDbContext>()
        .AddDefaultTokenProviders();

4. Modify the ApplicationDBContext

Modify the inheritance

Update the inheritance of your ApplicationDBContext. It should come from the new types we have created above. Change it from the original:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

To something like:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser, IdentityRole<long>, long, IdentityUserClaim<long>, IdentityUserRole<long>, IdentityUserLogin<long>, IdentityRoleClaim<long>, IdentityUserToken<long>>

Add your tables to the OnModelCreating

Add the new code only after the base.OnModelCreating(builder) call. Feel free to change the schema name if you wish.

    builder.Entity<ApplicationUser>().ToTable("User", "identity");
    builder.Entity<IdentityRole<long>>().ToTable("Role", "identity");
    builder.Entity<IdentityUserClaim<long>>().ToTable("UserClaim", "identity");
    builder.Entity<IdentityRoleClaim<long>>().ToTable("RoleClaim", "identity");

    builder.Entity<IdentityUserLogin<long>>().ToTable("UserLogin", "identity");
    builder.Entity<IdentityUserRole<long>>().ToTable("UserRole", "identity");
    builder.Entity<IdentityUserToken<long>>().ToTable("UserToken", "identity");

Add support for HiLo ID generation stretegy

Add support for HiLo strategy by adding the following lines of code:

    builder.HasAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SequenceHiLo);
    builder.ForNpgsqlUseSequenceHiLo();

This will add support for all IDs globally in the Model.

TIP: You can apply support to only a particular ID by calling the ForNpgsqlUseSequenceHiLo method on that ID.

An example of this would be:

builder.Entity<ApplicationUser>().ToTable("User", "identity").Property(t => t.Id).ForNpgsqlUseSequenceHiLo();

Add support for postgresql table naming conventions

Postgresql has a snake type naming convention. A table name looks_like_this, same goes for the table columns. This step is not mandatory but you can add it if you wish.

To apply this snake naming convention take a look at a complete implementation of the ApplicationDBContext class:

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.HasAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SequenceHiLo);
        builder.ForNpgsqlUseSequenceHiLo();

        builder.Entity<ApplicationUser>().ToTable("User", "identity");
        builder.Entity<IdentityRole<long>>().ToTable("Role", "identity");
        builder.Entity<IdentityUserClaim<long>>().ToTable("UserClaim", "identity");
        builder.Entity<IdentityRoleClaim<long>>().ToTable("RoleClaim", "identity");

        builder.Entity<IdentityUserLogin<long>>().ToTable("UserLogin", "identity");
        builder.Entity<IdentityUserRole<long>>().ToTable("UserRole", "identity");
        builder.Entity<IdentityUserToken<long>>().ToTable("UserToken", "identity");

        ApplySnakeCaseNames(builder);
    }

    private void ApplySnakeCaseNames(ModelBuilder modelBuilder)
    {
        var mapper = new Npgsql.NpgsqlSnakeCaseNameTranslator();

        foreach (var entity in modelBuilder.Model.GetEntityTypes())
        {
            // modify column names
            foreach (var property in entity.GetProperties())
            {
                property.Relational().ColumnName = mapper.TranslateMemberName(property.Relational().ColumnName);
            }

            // modify table name
            entity.Relational().TableName = mapper.TranslateMemberName(entity.Relational().TableName);

            // move asp_net tables into schema 'identity'
            if (entity.Relational().TableName.StartsWith("asp_net_"))
            {
                entity.Relational().TableName = entity.Relational().TableName.Replace("asp_net_", string.Empty);
                entity.Relational().Schema = "identity";
            }
        }
    }

Regenerate the Migration class

Delete the migrations folder in "Data\Migrations". Go to a console window and navigate to the root folder in your project and execute the following command:

C:\Users\hhernandez\source\repos\IdentityAndPostgresql\IdentityAndPostgresql> dotnet ef migrations add Initial -o .\Data\Migrations

Run and enjoy

When you will run the application for the first time you will get an error page since the migration has not been executed. Click on the button "Add Migration" and let EF create the tables for you in the db. An alternative would be to do it by yourself by executing the command in console:

C:\Users\hhernandez\source\repos\IdentityAndPostgresql\IdentityAndPostgresql> dotnet ef database update

Enjoy!