EF Core - Key (Id)=(0) is not present in table

1

I get this error when trying to insert a value into my join table in my database:

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

Full error:

    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)

The 3 tables are GroceryItems, GroceryStores, and the join table GroceryItemGroceryStores.

Here is my code using the fluent API to set up a many to many relationship.

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

});

Here are my actual database tables:

enter image description here

It doesn't look like this error should exist, GroceryItems has a column Id which is the primary key. Why does the error occur?

Here is the controller endpoint that runs:

// 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 payload sent:

{
    "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
}

debugger of class instance being inserted:

enter image description here

I have found some more details to the error - It seems to be performing an update if you see the error (even though I am just doing .Add() to add to the database:

When I try to insert a GroceryItem to the GroceryItem table nothing goes in and it returns an empty array with no errors.

It kinda seems to me like the .Add() is performing the wrong thing (update instead of insert). I have added the raw SQL into the error near the top of the question.

postgresql
entity-framework-core
ef-core-5.0
asked on Stack Overflow Mar 25, 2021 by BeniaminoBaggins • edited Mar 26, 2021 by BeniaminoBaggins

1 Answer

0

Removing .HasForeignKey("Id") made it work.

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

I thought I needed the HasForeignKey because my database had all these foreign key constraints with VeganItemId and EstablishmentId. I thought that it was expecting my primary keys to be VeganItemId and EstablishmentId:

enter image description here

But I suppose the (Id) at the end of all of those means it is just Id after all.

answered on Stack Overflow Mar 26, 2021 by BeniaminoBaggins

User contributions licensed under CC BY-SA 3.0