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:
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:
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.
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
:
But I suppose the (Id) at the end of all of those means it is just Id
after all.
User contributions licensed under CC BY-SA 3.0