无法添加与ASP.NET核心和身份
问题描述：
我尝试自定义声明添加到现有的用户身份的一个要求，但我在运行时得到一个异常：无法添加与ASP.NET核心和身份
Npgsql.PostgresException: 23502: null value in column "Id" violates not-null constraint
帮助！
我做了什么。我创建使用下面的命令行
dotnet new mvc --auth Individual --framework netcoreapp1.1
我所做的更改发现here，使应用程序使用PostgreSQL作为后端数据库窗口一个简单的Web应用程序。创建的默认webapp工作正常。我可以作为一个新的用户注册，登录，注销等..
然后我修改的家庭控制器的测试方法（我知道的例外是丑陋的）：
[Authorize]
public async Task<IActionResult> Test()
{
var user = await GetCurrentUserAsync();
if (user == null) {
_logger.LogWarning("User is null.");
throw new Exception("Not logged in");
}
_logger.LogWarning("User: {0}, {1}", user.Email, user);
var claim = new Claim("TestClaimType", "TestClaimValue");
IdentityResult idRes = IdentityResult.Failed();
if (_userManager.SupportsUserClaim) {
idRes = await _userManager.AddClaimAsync(user, claim); <------- Adding the claim
}
_logger.LogWarning("Return from adding claim");
if (idRes != IdentityResult.Success) {
throw new Exception("Failed to add claim.");
}
return View();
}
在登录后我触发试验方法，并得到以下日志记录（该PostgresException已经接近尾声）：
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:5000/Home/Test
info: Microsoft.AspNetCore.Authentication.Cookies.CookieAuthenticationMiddleware[3]
HttpContext.User merged via AutomaticAuthentication from authenticationScheme: Identity.Application.
info: Microsoft.AspNetCore.Authorization.DefaultAuthorizationService[1]
Authorization was successful for user: [email protected]
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method AlumniConnect.Controllers.HomeController.Test (AlumniConnect) with arguments ((null)) - ModelState is Valid
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (1ms) [Parameters=[@__get_Item_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "e"."Id", "e"."AccessFailedCount", "e"."ConcurrencyStamp", "e"."Email", "e"."EmailConfirmed", "e"."LockoutEnabled", "e"."LockoutEnd", "e"."NormalizedEmail", "e"."NormalizedUserName", "e"."PasswordHash", "e"."PhoneNumber", "e"."PhoneNumberConfirmed", "e"."SecurityStamp", "e"."TwoFactorEnabled", "e"."UserName"
FROM "AspNetUsers" AS "e"
WHERE "e"."Id" = @__get_Item_0
LIMIT 1
warn: AlumniConnect.Controllers.HomeController[0]
User: [email protected], [email protected]
warn: AlumniConnect.Controllers.HomeController[0]
User: [email protected], [email protected]
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (4ms) [Parameters=[@__normalizedUserName_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "u"."Id", "u"."AccessFailedCount", "u"."ConcurrencyStamp", "u"."Email", "u"."EmailConfirmed", "u"."LockoutEnabled", "u"."LockoutEnd", "u"."NormalizedEmail", "u"."NormalizedUserName", "u"."PasswordHash", "u"."PhoneNumber", "u"."PhoneNumberConfirmed", "u"."SecurityStamp", "u"."TwoFactorEnabled", "u"."UserName"
FROM "AspNetUsers" AS "u"
WHERE "u"."NormalizedUserName" = @__normalizedUserName_0
LIMIT 1
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (33ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p17='?', @p3='?', @p4='?', @p18='?', @p5='?', @p6='?', @p7='?', @p8='?', @p9='?', @p10='?', @p11='?', @p12='?', @p13='?', @p14='?', @p15='?', @p16='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "AspNetUserClaims" ("ClaimType", "ClaimValue", "UserId")
VALUES (@p0, @p1, @p2)
RETURNING "Id";
UPDATE "AspNetUsers" SET "AccessFailedCount" = @p3, "ConcurrencyStamp" = @p4, "Email" = @p5, "EmailConfirmed" = @p6, "LockoutEnabled" = @p7, "LockoutEnd" = @p8, "NormalizedEmail" = @p9, "NormalizedUserName" = @p10, "PasswordHash" = @p11, "PhoneNumber" = @p12, "PhoneNumberConfirmed" = @p13, "SecurityStamp" = @p14, "TwoFactorEnabled" = @p15, "UserName" = @p16
WHERE "Id" = @p17 AND "ConcurrencyStamp" = @p18;
fail: Microsoft.EntityFrameworkCore.DbContext[1]
An exception occurred in the database while saving changes.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23502: null value in column "Id" violates not-null constraint
at Npgsql.NpgsqlConnector.<DoReadMessageAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
有很多记录更多，但它似乎并没有增加新的信息。我在整个日志中看到了多次提到的相同异常。
我该怎么办？这是PostgreSQL的特定问题吗？我是否试图以错误的方式添加索赔？
谢谢！
答
在移民身份，有一个产生整数ID的所有表具有注释添加该ID的自动生成。此注释是SQL Server特定的，如.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
修复此问题是为迁移添加Postgres特定注释：.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn)。在旧版本的Npgsql.EntityFrameworkCore.PostgreSQL中，可能需要使用.Annotation("Npgsql:ValueGeneratedOnAdd", true)。
迁移用于创建AspNetRoleClaims表，然后将类似于部分：
migrationBuilder.CreateTable(
name: "AspNetRoleClaims",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
ClaimType = table.Column<string>(nullable: true),
ClaimValue = table.Column<string>(nullable: true),
RoleId = table.Column<string>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_AspNetRoleClaims", x => x.Id);
table.ForeignKey(
name: "FK_AspNetRoleClaims_AspNetRoles_RoleId",
column: x => x.RoleId,
principalTable: "AspNetRoles",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
+0
这是一个比我的更好的解决方案，谢谢。 –
答
看起来，当将声明添加到数据库时，SQL语句的'RETURNING'Id''子句建议返回ID。但是，该表没有自动递增ID列。
我通过遵循Adding 'serial' to existing column in Postgres的说明进行验证。
的问题是现在当然，这应该被照顾的自动...
Npgsql.PostgresException：23502：在列“ID”空值违反非空约束。看起来像[dbo]上的Id，[AspNetUserClaims]没有被设置。你可以检查用户对象上的Id吗？ –
我没有办法检查这个。 Claim类没有ID属性。我认为，由于UserManager来自身份库，因此会提供ID。是不是代码需要自动生成一个ID，而事实并非如此？嗯，'RETURNING'Id''似乎暗示了类似的东西。 –
我假设用户对象上的Id属性未设置，但该对象不为空。你可以在测试中粘贴一个断点并检查用户对象。 –