小贝子编程

尝试插入具有 1：N 关系的实体时，重复键值违反了 EntityFramework 中的唯一约束"PK_Users"错误



我正在构建一个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);
}

现在让我解释一下我的问题的细节。例如，我的数据库中有一个Id为1的用户。在Swagger中，我想插入以下设备，例如:

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

我说这个设备属于Id为1的用户。我得到的请求响应代码是一个500内部服务器错误，以及以下错误:

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.autopostgres.windows-x64srcbackendaccessnbtreenbtinsert.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:UserstimotDesktopEnergyManagementEnergyManagementDataRepositoryUnitOfWork.cs:line 31
at EnergyManagement.Services.DeviceService.Insert(DeviceDTOWithoutId entity) in C:UserstimotDesktopEnergyManagementEnergyManagementServicesDeviceService.cs:line 62
at EnergyManagement.Controllers.DeviceController.InsertDevice(DeviceDTOWithoutId deviceDTO) in C:UserstimotDesktopEnergyManagementEnergyManagementControllersDeviceController.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);
}
}

看起来你做的一切都是正确的，但也许你的用户-设备关系不匹配。您可以尝试添加导航属性

public int UserID {get; set;}

到Device类。或者在ApplicationDbContext中指定与fluent API的关系。您是否已经对此进行了配置?创建这些表的迁移看起来如何?

这应该是一个注释，但是我缺少注释

作为一个工作，尝试从你的代码中删除_userEntity.Devices.Add(_deviceEntity)，但我不确定它是否会工作

public async Task Insert(DeviceDTOWithoutId deviceDto)
{
var user = await _unitOfWork.Users.Get(q => q.Id == deviceDto.UserId);
if(user==null) return BadRequest("user is not found");
var device = _mapper.Map<Device>(deviceDto);
device.User = user;
await _unitOfWork.Devices.Insert(device);
await _unitOfWork.Save();
}

正如Thyselius已经建议的那样，更好的方法是将UserId添加到您的Device类

public int UserId {get; set;}

在此之后删除所有旧的迁移数据，并进行干净的数据库迁移

由于您的实体中有UserId，因此插入代码将更加简单

public async Task Insert(DeviceDTOWithoutId deviceDto)
{
var device = _mapper.Map<Device>(deviceDto);
await _unitOfWork.Devices.Insert(device);
await _unitOfWork.Save();
}

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium