AspNetCore.Identity 如何使用 PostgreSQL 创建结构？

Question

我有一个使用AspNetCore.Identity登录的ASP.NET Core Web应用程序，它使用SQL Server进行用户身份验证。

我需要使用PostgreSQL数据库，已添加了一些NuGet包（EF.*等），更改了连接字符串和代码。

 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();
        }

但是当我运行它时，它连接到PostgreSQL数据库并尝试创建结构，我收到了错误信息，出了什么问题？为什么EF不能创建结构？如何解决这个问题？

  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

- ZedZip

---

请跟随这个视频教程，了解如何为 asp.net core 3.1 成员资格配置 postgres https://youtu.be/zNSbYggVX9k - navule

Answer 1

我不得不比更改供应商和连接字符串更进一步。

在我的 ApplicationDbContext 类中：

首先，我遇到了 PostgreSQL 强制添加双引号以添加未引用标识符并自动将其折叠成小写的问题。 PostgreSQL 文档。

我的解决方案是安装 EFCore.NamingConventions 包。然后按照下面所示重写 OnConfiguring。

Identity 3.0 显然希望使用 "dbo" 模式。因此，需要通过覆盖 OnModelCreating 来指定默认模式（在此示例中为“public”，也是 Postgresql 的默认值）。

最后，数据库表名需要转换为小写，在 OnModelCreating 覆盖中的 for 循环中处理。

在 ApplicationDbContext 类中：

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; }
}

在这之后，我添加了一个新的数据迁移:

add-migration modifyIdentityForPostgresql

然后更新数据库架构：

update-database

这导致了Identity表的创建。

PostgreSQL中新建的Identity表的屏幕截图

- Steven Tomlinson

---

做了这个操作后出现错误 无法创建类型为 'ApplicationDbContext' 的对象。有关设计时支持的不同模式，请参见<某个链接> - 这是解决方案：https://dev59.com/7VIH5IYBdhLWcg3wZtQs#59797176 - A Petrov

Answer 2

更改提供程序和连接字符串后，应运行数据库迁移，以便在您的PostgreSQL数据库中创建asp.net core身份验证表：

1. Add-Migration InitialCreate

2. Update-database

文档：https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/?tabs=vs

- Nan Yu

---

这是正确的答案。只要记得先删除初始迁移，如果您使用个人帐户创建新项目。 - user9152964

Answer 3

我的答案与@Steven Tomlinson提供的答案类似，但他的解决方案中ExampleTableName变成了exampletablename<string>，而我不太喜欢这样，因为我的目标是使用蛇形命名法。

1. 创建一个Helper.cs类

    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. 安装 EFCore.NamingConventions
3. 编辑 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));
            }
        }
    }

- A Petrov