AspNetCore.Identity using PostgreSQL: how to create structure?

Question

I have a ASP.NET Core web application with AspNetCore.Identity Logins. It uses SQL Server for user authentication.

I need to use PostgreSQL database I have added some nuget packages (EF.* etc), changed the connection string and code

 public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
            });
//                options.UseSqlServer( Configuration.GetConnectionString("DefaultConnection")));
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseNpgsql( Configuration.GetConnectionString("DefaultConnection")));

            services.AddDefaultIdentity<IdentityUser>()
                .AddEntityFrameworkStores<ApplicationDbContext>();

            services.AddControllersWithViews()
                .AddNewtonsoftJson();
            services.AddRazorPages();
        }

But when I run it, it connects to the PostgreSQL database and tries to create the structure I receive the error, what is wrong? Why EF cannot create the structure? How to solve this problem?

  Executing endpoint 'WebApplication1.Controllers.HomeController.Index (WebA
pplication1)'
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
      Route matched with {action = "Index", controller = "Home", page = "", area
 = ""}. Executing controller action with signature Microsoft.AspNetCore.Mvc.IAct
ionResult Index() on controller WebApplication1.Controllers.HomeController (WebA
pplication1).
info: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[1]
      Executing ViewResult, running view Index.
info: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[4]
      Executed ViewResult - view Index executed in 302.6169ms.
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action WebApplication1.Controllers.HomeController.Index (WebAppli
cation1) in 315.3645ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'WebApplication1.Controllers.HomeController.Index (WebAp
plication1)'
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 451.9255ms 200 text/html; charset=utf-8
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
      Request starting HTTP/1.1 GET https://localhost:5001/Identity/Account/Logi
n
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint '/Account/Login'
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[3]
      Route matched with {page = "/Account/Login", area = "Identity", action = "
", controller = ""}. Executing page /Account/Login
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[101]
      Executing handler method Microsoft.AspNetCore.Identity.UI.V4.Pages.Account
.Internal.LoginModel.OnGetAsync - ModelState is Valid
info: Microsoft.AspNetCore.Authentication.Cookies.CookieAuthenticationHandler[11
]
      AuthenticationScheme: Identity.External signed out.
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[102]
      Executed handler method OnGetAsync, returned result .
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[103]
      Executing an implicit handler method - ModelState is Valid
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[104]
      Executed an implicit handler method, returned result Microsoft.AspNetCore.
Mvc.RazorPages.PageResult.
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[4]
      Executed page /Account/Login in 104.6291ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint '/Account/Login'
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 157.5038ms 200 text/html; charset=utf-8
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
      Request starting HTTP/1.1 POST https://localhost:5001/Identity/Account/Log
in application/x-www-form-urlencoded 264
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint '/Account/Login'
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[3]
      Route matched with {page = "/Account/Login", area = "Identity", action = "
", controller = ""}. Executing page /Account/Login
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[101]
      Executing handler method Microsoft.AspNetCore.Identity.UI.V4.Pages.Account
.Internal.LoginModel.OnPostAsync - ModelState is Valid
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.0 initialized 'ApplicationDbContext' using provi
der 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (182ms) [Parameters=[@__normalizedUserName_0='?
'], CommandType='Text', CommandTimeout='30']
      SELECT a."Id", a."AccessFailedCount", a."ConcurrencyStamp", a."Email", a."
EmailConfirmed", a."LockoutEnabled", a."LockoutEnd", a."NormalizedEmail", a."Nor
malizedUserName", a."PasswordHash", a."PhoneNumber", a."PhoneNumberConfirmed", a
."SecurityStamp", a."TwoFactorEnabled", a."UserName"
      FROM "AspNetUsers" AS a
      WHERE a."NormalizedUserName" = @__normalizedUserName_0
      LIMIT 1
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for cont
ext type 'WebApplication1.Data.ApplicationDbContext'.
      Npgsql.PostgresException (0x80004005): 42P01: relation "AspNetUsers" does
not exist
         at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__Rea
dMessageLong|0>d.MoveNext()
      --- End of stack trace from previous location where exception was thrown -
--
         at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__Rea
dMessageLong|0>d.MoveNext()
      --- End of stack trace from previous location where exception was thrown -
--
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsumin
g)
         at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Bo
olean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavi
or, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReade
rAsync(RelationalCommandParameterObject parameterObject, CancellationToken cance
llationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReade
rAsync(RelationalCommandParameterObject parameterObject, CancellationToken cance
llationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReade
rAsync(RelationalCommandParameterObject parameterObject, CancellationToken cance
llationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.As
yncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationTok
en cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecuti
onStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 v
erifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.As
yncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: relation "AspNetUsers" does not exist
          Position: 294
          File: parse_relation.c
          Line: 1159
          Routine: parserOpenTable
Npgsql.PostgresException (0x80004005): 42P01: relation "AspNetUsers" does not ex
ist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessa
geLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean
async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, Ca
ncellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync
(RelationalCommandParameterObject parameterObject, CancellationToken cancellatio
nToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync
(RelationalCommandParameterObject parameterObject, CancellationToken cancellatio
nToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync
(RelationalCommandParameterObject parameterObject, CancellationToken cancellatio
nToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnu
merator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken can
cellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStra
tegy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifyS
ucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnu
merator.MoveNextAsync()
  Exception data:
    Severity: ERROR
    SqlState: 42P01
    MessageText: relation "AspNetUsers" does not exist
    Position: 294
    File: parse_relation.c
    Line: 1159
    Routine: parserOpenTable
info: Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker[4]
      Executed page /Account/Login in 2362.999ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint '/Account/Login'
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeo
ut='30']
      SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_name
space n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 3019.116ms 500 text/html; charset=utf-8

Answer 1

I had to go a bit further than changing the provider and connection string.

In my ApplicationDbContext class:

First, I had an issue with Postgresql wanting to force double-quotes to be added to unquoted identifiers and automatically folding them to lower-case. PostgreSQL Docs.

My solution was to install the EFCore.NamingConventions package. Then override OnConfiguring as shown below.

Identity 3.0 is apparently expecting the "dbo" schema to be used. Consequently, that needs to be specified by setting the default schema ("public" in this example and that is also the default for Postgresql) by overriding OnModelCreating.

Finally, the database table names need to be lower-cased which is handled by the for loop in the OnModelCreating override.

In the ApplicationDbContext class:

public class ApplicationDbContext
            : IdentityDbContext<IdentityUser<int>,IdentityRole<int>,int>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
        .UseSnakeCaseNamingConvention();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // PostgreSQL uses the public schema by default - not dbo.
        modelBuilder.HasDefaultSchema("public");
        base.OnModelCreating(modelBuilder);

        //Rename Identity tables to lowercase
        foreach (var entity in modelBuilder.Model.GetEntityTypes())
        {
            var currentTableName = modelBuilder.Entity(entity.Name).Metadata.GetDefaultTableName();
            modelBuilder.Entity(entity.Name).ToTable(currentTableName.ToLower());
        }
    } 
    public DbSet<Address> Address { get; set; }
}

After doing this I added a new migration: add-migration modifyIdentityForPostgresql

Then updated the database schema: update-database

Which results in the Identity tables being created.

Screen clip of newly created Identity tables in Postgresql

Answer 2

My answer is similar to the one that @Steven Tomlinson provided, but in his solution ExampleTableName becomes exampletablename<string>, which I didn't really like as my goal was snake_case.

1. Create a Helper.cs class

    public class Helper
    {
        public static string ToUnderscoreCase(string str)
        {
            return string.Concat(str.Select((x, i) => i > 0 && char.IsUpper(x) ? "_" + x.ToString() : x.ToString())).ToLower();
        }
    }

2. Install EFCore.NamingConventions

3. Edit ApplicationDbContext.cs:

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

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
            .UseSnakeCaseNamingConvention(); // Not even sure if this is used? it 
didn't seem to make any difference

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("public");
            base.OnModelCreating(modelBuilder);

            foreach (var entity in modelBuilder.Model.GetEntityTypes())
            {
                var currentTableName = modelBuilder.Entity(entity.Name).Metadata.GetDefaultTableName();
                if (currentTableName.Contains("<"))
                {
                    currentTableName = currentTableName.Split('<')[0];
                }
                modelBuilder.Entity(entity.Name).ToTable(Helper.ToUnderscoreCase(currentTableName));
            }
        }
    }