时间戳

首页 > 解决方案 > EF Core - 表中不存在键 (Id)=(0)

postgresql - EF Core - 表中不存在键 (Id)=(0)

问题描述

尝试将值插入数据库中的联接表时出现此错误：

Detail: Key (Id)=(0) is not present in table "GroceryItems".

完整错误：

    Executing DbCommand [Parameters=[@p0='1', @p1='fsfsasf' (Nullable = false), @p2='0', @p3='df2sssfad' (Nullable = false), @p4=NULL, @p5='0', @p6='0', @p7='yosadsfdfsaff' (Nullable = false), @p8='0', @p9='0', @p10='[{"Id":11,"IconCodePoint":23145,"Name":"sdaf"}]' (Nullable = false), @p11=NULL, @p12='fMs' (Nullable = false), @p13='ffsfffs' (Nullable = false), @p14='Beacdsfff2dfh Rd' (Nullable = false), @p15=NULL, @p16=NULL], CommandType='Text', CommandTimeout='30']
          INSERT INTO "GroceryItems" ("Id", "Brand", "CurrentRevisionId", "Description", "Image", "IsNotVeganCount", "IsVeganCount", "Name", "Rating", "RatingsCount", "Tags")
          VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10);
          INSERT INTO "GroceryStores" ("City", "Name", "PlaceId", "Street", "StreetNumber", "Suburb")
          VALUES (@p11, @p12, @p13, @p14, @p15, @p16)
          RETURNING "Id";
    info: 03/26/2021 18:07:09.846 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
          Executed DbCommand (6ms) [Parameters=[@p0='1', @p1='fsfsasf' (Nullable = false), @p2='0', @p3='df2sssfad' (Nullable = false), @p4=NULL, @p5='0', @p6='0', @p7='yosadsfdfsaff' (Nullable = false), @p8='0', @p9='0', @p10='[{"Id":11,"IconCodePoint":23145,"Name":"sdaf"}]' (Nullable = false), @p11=NULL, @p12='fMs' (Nullable = false), @p13='ffsfffs' (Nullable = false), @p14='Beacdsfff2dfh Rd' (Nullable = false), @p15=NULL, @p16=NULL], CommandType='Text', CommandTimeout='30']
          INSERT INTO "GroceryItems" ("Id", "Brand", "CurrentRevisionId", "Description", "Image", "IsNotVeganCount", "IsVeganCount", "Name", "Rating", "RatingsCount", "Tags")
          VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10);
          INSERT INTO "GroceryStores" ("City", "Name", "PlaceId", "Street", "StreetNumber", "Suburb")
          VALUES (@p11, @p12, @p13, @p14, @p15, @p16)
          RETURNING "Id";
    dbug: 03/26/2021 18:07:09.847 CoreEventId.ForeignKeyChangeDetected[10803] (Microsoft.EntityFrameworkCore.ChangeTracking) 
          The foreign key property 'GroceryStore.Id' was detected as changed from '-2147482642' to '8' for entity with key '{Id: 8}'.
    dbug: 03/26/2021 18:07:09.847 CoreEventId.ForeignKeyChangeDetected[10803] (Microsoft.EntityFrameworkCore.ChangeTracking) 
          The foreign key property 'GroceryItemGroceryStore.EstablishmentId' was detected as changed from '-2147482642' to '8' for entity with key '{VeganItemId: 1, EstablishmentId: 8}'.
    dbug: 03/26/2021 18:07:09.847 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command) 
    The foreign key property 'GroceryItemGroceryStore.EstablishmentId' was detected as changed from '-2147482642' to '8' for entity with key '{VeganItemId: 1, EstablishmentId: 8}'.
    dbug: 03/26/2021 18:07:09.847 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command) 
          A data reader was disposed.
    dbug: 03/26/2021 18:07:09.847 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command) 
          Creating DbCommand for 'ExecuteReader'.
    dbug: 03/26/2021 18:07:09.847 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
          Created DbCommand for 'ExecuteReader' (0ms).
    dbug: 03/26/2021 18:07:09.847 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
          Executing DbCommand [Parameters=[@p17='8', @p18='1', @p19='0', @p20='0', @p21='0', @p22='2'], CommandType='Text', CommandTimeout='30']
          INSERT INTO "GroceryItemGroceryStores" ("EstablishmentId", "VeganItemId", "Id", "InEstablishmentCount", "NotInEstablishmentCount", "Price")
          VALUES (@p17, @p18, @p19, @p20, @p21, @p22);
    fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
          Failed executing DbCommand (7ms) [Parameters=[@p17='8', @p18='1', @p19='0', @p20='0', @p21='0', @p22='2'], CommandType='Text', CommandTimeout='30']
          INSERT INTO "GroceryItemGroceryStores" ("EstablishmentId", "VeganItemId", "Id", "InEstablishmentCount", "NotInEstablishmentCount", "Price")
          VALUES (@p17, @p18, @p19, @p20, @p21, @p22);
    Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (7ms) [Parameters=[@p17='8', @p18='1', @p19='0', @p20='0', @p21='0', @p22='2'], CommandType='Text', CommandTimeout='30']
    INSERT INTO "GroceryItemGroceryStores" ("EstablishmentId", "VeganItemId", "Id", "InEstablishmentCount", "NotInEstablishmentCount", "Price")
    VALUES (@p17, @p18, @p19, @p20, @p21, @p22);
    fail: 03/26/2021 18:07:09.858 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
          Failed executing DbCommand (7ms) [Parameters=[@p17='8', @p18='1', @p19='0', @p20='0', @p21='0', @p22='2'], CommandType='Text', CommandTimeout='30']
          INSERT INTO "GroceryItemGroceryStores" ("EstablishmentId", "VeganItemId", "Id", "InEstablishmentCount", "NotInEstablishmentCount", "Price")
          VALUES (@p17, @p18, @p19, @p20, @p21, @p22);
    dbug: 03/26/2021 18:07:09.883 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction) 
          Disposing transaction.
    dbug: 03/26/2021 18:07:09.883 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection) 
          Closing connection to database 'vepo_dev_db' on server 'tcp://localhost:5432'.
    dbug: 03/26/2021 18:07:09.883 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection) 
    Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (7ms) [Parameters=[@p17='8', @p18='1', @p19='0', @p20='0', @p21='0', @p22='2'], CommandType='Text', CommandTimeout='30']
INSERT INTO "GroceryItemGroceryStores" ("EstablishmentId", "VeganItemId", "Id", "InEstablishmentCount", "NotInEstablishmentCount", "Price")
VALUES (@p17, @p18, @p19, @p20, @p21, @p22);
fail: 03/26/2021 18:07:09.858 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
      Failed executing DbCommand (7ms) [Parameters=[@p17='8', @p18='1', @p19='0', @p20='0', @p21='0', @p22='2'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "GroceryItemGroceryStores" ("EstablishmentId", "VeganItemId", "Id", "InEstablishmentCount", "NotInEstablishmentCount", "Price")
      VALUES (@p17, @p18, @p19, @p20, @p21, @p22);
dbug: 03/26/2021 18:07:09.883 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Disposing transaction.
dbug: 03/26/2021 18:07:09.883 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closing connection to database 'vepo_dev_db' on server 'tcp://localhost:5432'.
dbug: 03/26/2021 18:07:09.883 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closed connection to database 'vepo_dev_db' on server 'tcp://localhost:5432'.
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'Vepo.Data.VepoContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> Npgsql.PostgresException (0x80004005): 23503: insert or update on table "GroceryItemGroceryStores" violates foreign key constraint "FK_GroceryItemGroceryStores_GroceryItems_Id"
         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)
2
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
2
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
        Exception data:
          Severity: ERROR
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
        Exception data:
          Severity: ERROR
          SqlState: 23503
          MessageText: insert or update on table "GroceryItemGroceryStores" violates foreign key constraint "FK_GroceryItemGroceryStores_GroceryItems_Id"
          Detail: Key (Id)=(0) is not present in table "GroceryItems".
          SchemaName: public
          TableName: GroceryItemGroceryStores
          ConstraintName: FK_GroceryItemGroceryStores_GroceryItems_Id
          File: ri_triggers.c
          Line: 3266
          Routine: ri_ReportViolation
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

这 3 个表是GroceryItems、GroceryStores和连接表GroceryItemGroceryStores。

这是我使用 fluent API 建立多对多关系的代码。

modelBuilder.Entity<GroceryStore>(gs =>
{
    gs.HasIndex(gs => gs.PlaceId).IsUnique();

    gs
    .HasMany(s => s.VeganItems)
    .WithMany(vi => vi.Establishments)
    .UsingEntity<GroceryItemGroceryStore>
    (gigs => gigs.HasOne<GroceryItem>().WithMany().HasForeignKey("Id"),
    gigs => gigs.HasOne<GroceryStore>().WithMany().HasForeignKey("Id"));

});

这是我的实际数据库表：

在此处输入图像描述

看起来这个错误不应该存在，GroceryItems 有一列Id是主键。为什么会发生错误？

这是运行的控制器端点：

// POST: api/GroceryItemGroceryStores
// To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
[HttpPost]
public async Task<ActionResult<GroceryItemGroceryStore>> PostGroceryItemGroceryStore(GroceryItemGroceryStore groceryItemGroceryStore)
{
    _context.GroceryItemGroceryStores.Add(groceryItemGroceryStore);

    try
    {
        await _context.SaveChangesAsync();
    }
    catch (DbUpdateException)
    {
        if (GroceryItemGroceryStoreExists(groceryItemGroceryStore.VeganItemId))
        {
            return Conflict();
        }
        else
        {
            throw;
        }
    }

    return CreatedAtAction("GetGroceryItemGroceryStore", new { id = groceryItemGroceryStore.VeganItemId }, groceryItemGroceryStore);
}

发送的 JSON 有效负载：

{
    "veganItem": {
        "name": "yosadsfdfsaff",
        "brand": "fsfsasf",
        "description": "df2sssfad",
        "tags": [
            {
                "name": "sdaf",
                "id": "11",
                "iconCodePoint": 23145
            }
        ]
    },
    "establishment": {
        "name": "fMs",
        "street": "Beacdsfff2dfh Rd",
        "placeId": "ffsfffs"
    },
    "price": 2.00
}

正在插入的类实例的调试器：

在此处输入图像描述

我发现了错误的更多详细信息 - 如果您看到错误，它似乎正在执行update（即使我只是在.Add()添加到数据库中：

当我尝试将 GroceryItem 插入 GroceryItem 表时，没有任何内容进入，它返回一个没有错误的空数组。

在我看来，这有点像.Add()执行错误的事情（更新而不是插入）。我已将原始 SQL 添加到问题顶部附近的错误中。

标签： postgresqlentity-framework-coreef-core-5.0

解决方案

删除.HasForeignKey("Id")使它工作。

modelBuilder.Entity<GroceryStore>(gs =>
{
    gs.HasIndex(gs => gs.PlaceId).IsUnique();

    gs.HasMany(gs => gs.VeganItems)
    .WithMany(vi => vi.Establishments)
    .UsingEntity<GroceryItemGroceryStore>
        (gigs => gigs.HasOne<GroceryItem>().WithMany(),
        gigs => gigs.HasOne<GroceryStore>().WithMany());
});

我认为我需要 ，HasForeignKey因为我的数据库具有所有这些外键约束VeganItemId和EstablishmentId。我认为它期望我的主键是VeganItemIdand EstablishmentId：

在此处输入图像描述

但我认为所有这些末尾的 (Id) 意味着它Id毕竟是。

推荐阅读