Duplicate key value violates unique constraint "PK_Users" error in EntityFramework when tr-CodePudding

I am building a ASP.NET Web API application, and I have two entities, User and Device. A user has a one to many relationship with the devices (a user has multiple devices). The problem is, when I insert a new device with a certain user id, I get a nasty error from the Posgres database I am using. I'll start by showing you my entities:

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

}

No need to show the controller method that catches the post request to insert a device, that method just calls the following service function:

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

I am using a repository pattern with unit of work. The generic Insert method in the repository is very simple, and works well on other entities:

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

When I insert a device with a userId that is not already in the database, like 10 for example, it creates a new user with Id 10 with all fields null. This error arises when _context.SaveChangesAsync() is called. If I am to insert a device in the database with the data presented above using plain SQL directly in Postgres, it works fine. EntityFramework does something wrong, or I do something wrong. What could be the cause of my problem? If you need additional information, I would gladly offer it, I urgently need to solve this problem. Thank you!

Edit: My first migration looks like this:

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

I have added the navigation property like @Thyselius said, and it didn't solve the problem. This is the migration after adding that:

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

CodePudding user response：

It looks like you are doing everything correctly but maybe there's been a mismatch in your User - Device relationship. You could try to add the navigation property

public int UserID {get; set;}

to your Device class. Or specify the relationship in your ApplicationDbContext with the fluent API. Have you already configured this, and how does your migrations look that creates these tables?

This really should be a comment but I lack the rep for that

CodePudding user response：

As a work around, try to remove _userEntity.Devices.Add(_deviceEntity) from your code, but i am not sure if it is going to work

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

As Thyselius already recomended, the better way is to add UserId to your Device class

public int UserId {get; set;}

after this delete all old migration data, and make a clean database migration

Since you have UserId in your entity, the insert code will be much more simple

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

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