代码之家 › 专栏 › 技术社区 › BeniaminoBaggins

表中不存在EF核心-密钥(Id)=(0)

ef-core-5.0 entity-framework-core

BeniaminoBaggins · 技术社区 · 3 年前

当我试图在数据库的联接表中插入值时,我遇到了以下错误:

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)

这三张桌子是 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"));

});

以下是我的实际数据库表:

看起来不应该存在此错误,GroceryIntems有一列 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() 添加到数据库中:

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

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

0 回复 | 直到 3 年前

BeniaminoBaggins 3 年前

删除 .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 。我以为它希望我的主键是 素食主义者 和 机构ID :

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