0

This is doing my head in. I have a pretty simple model class like this (generated by EF Core Power Tools):

enter image description here

In the Azure SQL database, I have the following foreign key relationship in place which associates a Sighting record with to every MachineLearningTaggedImage record:

ALTER TABLE [dbo].[MachineLearningTaggedImage] WITH CHECK 
    ADD CONSTRAINT [FK_MachineLearningTaggedImage_Sighting]  
        FOREIGN KEY([SightingId]) REFERENCES [dbo].[Sighting] ([SightingId])
GO

ALTER TABLE [dbo].[MachineLearningTaggedImage] 
    CHECK CONSTRAINT [FK_MachineLearningTaggedImage_Sighting]
GO

I am then trying to do a very run of the mill insert, with the SightingId value populated (because I want the newly inserted MachineLearningTaggedImage record to link to an existing Sighting record that already exists in the database):

var newImageTag = new MachineLearningTaggedImage
{
    SightingId = sighting.SightingId
};

_db.MachineLearningTaggedImages.Add(newImageTag);
Save();

But the insert fails with the following error:

The MERGE statement conflicted with the FOREIGN KEY constraint "FK_MachineLearningTaggedImage_Sighting". The conflict occurred in database "MyDatabase", table "dbo.Sighting", column 'SightingId'.

I'm puzzled because my existing value of SightingId (3398670) definitely exists in the Sighting table as an existing record which I want to link to.

But for some reason, EF Core or SQL won't let me do this insert.

Now if I go directly into a SQL query window (outside of EF Core), I can successfully insert the new MachineLearningTaggedImage record with a SightingId value = 3398670 - no problem at all. It works..

What am I running into? Is it something related to me having only populated the SightingId value, but the actual Sighting child / related object property is still null when I attempt to insert? I thought you could simply populated an ID of a related object and EF Core would be happy with that.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aaron
  • 1,802
  • 3
  • 23
  • 50
  • I can DEFINITELY insert this same record manually into the SQL Server database. I.e. this works: INSERT INTO MachineLearningTaggedImage (SightingId) VALUES (3398672) but the insert won't work via EF Core....... – Aaron Jan 30 '23 at 01:12
  • 1
    Wow in my debugging window I can see that EF Core is trying to insert a brand new Sighting record, but of course I'm trying to set an existing SightingId, so that it does NOT create a new Sighting record. Why is it trying to insert a brand new Sighting record even though I've specified an existing SightingId value in the related table? – Aaron Jan 30 '23 at 01:14
  • 1
    No idea why, but I ditched the idea of populating an existing SightingId property and populated the entire existing .Sighting object instead. Now it inserts the new MachineLearningTaggedImage record fine...... ??? – Aaron Jan 30 '23 at 01:38
  • 1
    Make sure **not** populating `Sighting` property at all, just `SightingId`. Navigation property **must** be `null`, and *not* a fake object with populated `Id` property. Because `Add` method is known to mark for addition any referenced *untracked* object, regardless of whether the PK is specified or not. Note that PK != 0 does not mean existing, you may want to insert new record with such key (the so called identity insert), so EF Core does not make such assumptions. – Ivan Stoev Jan 30 '23 at 07:09
  • 1
    @Aaron can you check this [link](https://stackoverflow.com/questions/62866379/ef-core-unable-to-insert-new-entry-that-has-a-foreign-key) as it seems to be a similar issue. – RitikaNalwaya Jan 31 '23 at 11:50

0 Answers0