嘗試插入具有1:N關系的物體時，重復鍵值違反了EntityFramework中的唯一約束“PK

我正在構建一個 ASP.NET Web API 應用程式，我有兩個物體，用戶和設備。用戶與設備之間是一對多的關系（一個用戶有多個設備）。問題是，當我插入具有特定用戶 ID 的新設備時，我從正在使用的 Posgres 資料庫中收到一個嚴重錯誤。我將首先向您展示我的物體：

public class User
{
    [Key]
    public int Id { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }
    public string Address { get; set; }
    public string Role { get; set; }

    public ICollection<Device> Devices { get; set; }

    public User()
    {
        Devices = new List<Device>();
    }
}

public class Device
{
    [Key]
    public int Id { get; set; }
    public string Description { get; set; }
    public string Location { get; set; }
    public double MaxEnergyConsumption { get; set; }
    public double AverageEnergyConsumption { get; set; }

    public User User { get; set; }

}

無需顯示捕獲 post 請求以插入設備的控制器方法，該方法只需呼叫以下服務函式：

public async Task Insert(DeviceDTOWithoutId entity)
{
    var _deviceEntity = _mapper.Map<Device>(entity);

    var _userEntity = await _unitOfWork.Users.Get(q => q.Id == entity.UserId);

    _userEntity.Devices.Add(_deviceEntity);
    _deviceEntity.User = _userEntity;

    await _unitOfWork.Devices.Insert(_deviceEntity);
    await _unitOfWork.Save();
}

我正在使用帶有作業單元的存盤庫模式。存盤庫中的通用 Insert 方法非常簡單，并且適用于其他物體：

public async Task Insert(T entity)
{
    await _db.AddAsync(entity);
}

Let me now explain the details of my problem. For example, I have in my database a user with and Id of 1. In Swagger, I want to insert the following Device for example:

{
  "description": "Smart Sensor",
  "location": "Garage",
  "maxEnergyConsumption": 10,
  "averageEnergyConsumption": 5,
  "userId": 1 
}

I am saying that this device belongs to the user with an Id of 1. The request response code I'm getting is a 500 internal server error, and the following error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

 ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_Users"

   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)

   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)

   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)

   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)

   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

  Exception data:

    Severity: ERROR

    SqlState: 23505

    MessageText: duplicate key value violates unique constraint "PK_Users"

    Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.

    SchemaName: public

    TableName: Users

    ConstraintName: PK_Users

    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\access\nbtree\nbtinsert.c

    Line: 656

    Routine: _bt_check_unique

   --- End of inner exception stack trace ---

   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

   at EnergyManagement.Data.Repository.UnitOfWork.Save() in C:\Users\timot\Desktop\EnergyManagement\EnergyManagement\Data\Repository\UnitOfWork.cs:line 31

   at EnergyManagement.Services.DeviceService.Insert(DeviceDTOWithoutId entity) in C:\Users\timot\Desktop\EnergyManagement\EnergyManagement\Services\DeviceService.cs:line 62

   at EnergyManagement.Controllers.DeviceController.InsertDevice(DeviceDTOWithoutId deviceDTO) in C:\Users\timot\Desktop\EnergyManagement\EnergyManagement\Controllers\DeviceController.cs:line 33

   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)

   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)

   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)

   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)

   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)

   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)

   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)



HEADERS

=======

Accept: */*

Accept-Encoding: gzip, deflate, br

Accept-Language: en-GB,en;q=0.5

Connection: close

Content-Length: 109

Content-Type: application/json

Host: localhost:44397

Referer: https://localhost:44397/swagger/index.html

Te: trailers

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:93.0) Gecko/20100101 Firefox/93.0

origin: https://localhost:44397

sec-fetch-dest: empty

sec-fetch-mode: cors

sec-fetch-site: same-origin

sec-gpc: 1

當我插入一個用戶 ID 不在資料庫中的設備時，例如 10，它會創建一個 ID 為 10 且所有欄位為空的新用戶。呼叫 _context.SaveChangesAsync() 時會出現此錯誤。如果我要直接在 Postgres 中使用純 SQL 使用上面顯示的資料在資料庫中插入一個設備，它作業正常。EntityFramework 做錯了什么，或者我做錯了什么。我的問題可能是什么原因？如果您需要其他資訊，我很樂意提供，我迫切需要解決這個問題。謝謝！

編輯：我的第一次遷移如下所示：

public partial class firstMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Users",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                Email = table.Column<string>(type: "text", nullable: true),
                Password = table.Column<string>(type: "text", nullable: true),
                Name = table.Column<string>(type: "text", nullable: true),
                BirthDate = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                Address = table.Column<string>(type: "text", nullable: true),
                Role = table.Column<string>(type: "text", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Users", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "Devices",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                Description = table.Column<string>(type: "text", nullable: true),
                Location = table.Column<string>(type: "text", nullable: true),
                MaxEnergyConsumption = table.Column<double>(type: "double precision", nullable: false),
                AverageEnergyConsumption = table.Column<double>(type: "double precision", nullable: false),
                UserId = table.Column<int>(type: "integer", nullable: true),
                UserId1 = table.Column<int>(type: "integer", nullable: true),
                UserId2 = table.Column<int>(type: "integer", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Devices", x => x.Id);
                table.ForeignKey(
                    name: "FK_Devices_Users_UserId",
                    column: x => x.UserId,
                    principalTable: "Users",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
                table.ForeignKey(
                    name: "FK_Devices_Users_UserId1",
                    column: x => x.UserId1,
                    principalTable: "Users",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
                table.ForeignKey(
                    name: "FK_Devices_Users_UserId2",
                    column: x => x.UserId2,
                    principalTable: "Users",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Devices_UserId",
            table: "Devices",
            column: "UserId");

        migrationBuilder.CreateIndex(
            name: "IX_Devices_UserId1",
            table: "Devices",
            column: "UserId1");

        migrationBuilder.CreateIndex(
            name: "IX_Devices_UserId2",
            table: "Devices",
            column: "UserId2");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Devices");

        migrationBuilder.DropTable(
            name: "Users");
    }
}

我像@Thyselius 說的那樣添加了導航屬性，但并沒有解決問題。這是添加后的遷移：

public partial class addedUserIdToDevice : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Devices_Users_UserId",
            table: "Devices");

        migrationBuilder.AlterColumn<int>(
            name: "UserId",
            table: "Devices",
            type: "integer",
            nullable: false,
            defaultValue: 0,
            oldClrType: typeof(int),
            oldType: "integer",
            oldNullable: true);

        migrationBuilder.AddForeignKey(
            name: "FK_Devices_Users_UserId",
            table: "Devices",
            column: "UserId",
            principalTable: "Users",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Devices_Users_UserId",
            table: "Devices");

        migrationBuilder.AlterColumn<int>(
            name: "UserId",
            table: "Devices",
            type: "integer",
            nullable: true,
            oldClrType: typeof(int),
            oldType: "integer");

        migrationBuilder.AddForeignKey(
            name: "FK_Devices_Users_UserId",
            table: "Devices",
            column: "UserId",
            principalTable: "Users",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);
    }
}

uj5u.com熱心網友回復：

看起來您所做的一切都正確，但您的用戶 - 設備關系可能不匹配。您可以嘗試添加導航屬性

public int UserID {get; set;}

到您的設備類。或者使用 fluent API 在 ApplicationDbContext 中指定關系。你已經配置了這個，你的遷移看起來如何創建這些表？

這真的應該是一個評論，但我缺乏代表

uj5u.com熱心網友回復：

作為解決方法，嘗試從您的代碼中洗掉 _userEntity.Devices.Add(_deviceEntity)，但我不確定它是否會起作用

public async Task Insert(DeviceDTOWithoutId entity)
{
    var _deviceEntity = _mapper.Map<Device>(entity);

    var _userEntity = await _unitOfWork.Users.Get(q => q.Id == entity.UserId);
    _deviceEntity.User = _userEntity;

    await _unitOfWork.Devices.Insert(_deviceEntity);
    await _unitOfWork.Save();
}

由于 Thyselius 已經推薦，更好的方法是將 UserId 添加到您的設備類

public int UserId {get; set;}

之后洗掉所有舊的遷移資料，并進行干凈的資料庫遷移

由于您的物體中有 UserId，因此插入代碼會簡單得多

public async Task Insert(DeviceDTOWithoutId entity)
{
    var _deviceEntity = _mapper.Map<Device>(entity);

    await _unitOfWork.Devices.Insert(_deviceEntity);
    await _unitOfWork.Save();
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/334436.html

標籤：C＃ PostgreSQL 实体框架 asp.net-web-api 昂首阔步

上一篇：如何統計上個月的資料？

下一篇：在PostgreSQL中更改時間戳的一部分（不是相對的）

嘗試插入具有1:N關系的物體時，重復鍵值違反了EntityFramework中的唯一約束“PK_Users”錯誤