Context not populating foreign key on adding new entities to database

Question

I have an issue where I scaffolded out a context and models from an existing MySQL database that contains a one parent to many children relationship. When I try to create a new parent and children, EF-core returns an error that the foreign key relationship has been violated.

I took a look at how EF scaffolded it out and cannot see any issues there. For some reason, the code executed against the database ignores the fact that the data model expects the foreign key to be populated. What did I miss that would cause this relationship to be ignored?

Below are the relevant bits:

1. Table Definitions
2. Auto-Scaffolded Context
3. Code executing the save
4. SQL code generated by EF that is throwing the error

Here are the two tables. Note the foreign key on the scan, which is the child to item.

item

CREATE TABLE `item` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(250) NOT NULL COLLATE 'utf8_general_ci',
    `UPC` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
    `friendly_name` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `date_created` DATETIME NOT NULL DEFAULT current_timestamp(),
    `date_deleted` DATETIME NULL DEFAULT NULL,
    `is_homemade` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=287
;

scan

CREATE TABLE `scan` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `item_id` INT(11) NOT NULL DEFAULT -1,
    `expiration_date` DATETIME NULL DEFAULT NULL,
    `use_date` DATETIME NULL DEFAULT NULL,
    `create_date` DATETIME NOT NULL DEFAULT current_timestamp(),
    `delete_date` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `fk_item_instance_item` (`item_id`) USING BTREE,
    CONSTRAINT `fk_item_instance_item` FOREIGN KEY (`item_id`) REFERENCES `inventory`.`item` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=537
;

The scaffolded code in the context for mapping the foreign key. This appears on the scan side of the modelBuilder.

entity.HasOne(d => d.Item)
  .WithMany(p => p.Scan)
  .HasForeignKey(d => d.ItemId)
  .OnDelete(DeleteBehavior.ClientSetNull)
  .HasConstraintName("fk_item_instance_item");

When I execute the following code to create both a new item with an associated new scan, I get an error from the database.

var newItem = new Item()
{
    Name = scan.Name,
    Upc = scan.UPC,
    DateCreated = DateTime.Now,
};

var newScan = new Scan()
{
    Item = newItem,
    CreateDate = DateTime.Now,
    ExpirationDate = scan.ExpirationDate,
};

newItem.Scan = new List<Scan>() { newScan };

if (ModelState.IsValid)
{
    _context.Item.Add(newItem);
    _context.Entry(newScan).State = EntityState.Added;

    await _context.SaveChangesAsync();
    ...

The database throws an error complaining about how the foreign key was violated. Looking into the SQL created, I can definitely see that it's not mapping the item to the scan.

INSERT INTO `item` (`date_created`, `date_deleted`, `friendly_name`, `is_homemade`, `name`, `UPC`)
VALUES (timestamp('2020-09-08 10:09:38.033399'), NULL, NULL, false, 'test item', 'testing');
SELECT `id`
FROM `item`
WHERE ROW_COUNT() = 1 AND `id` = LAST_INSERT_ID()

INSERT INTO `scan` (`create_date`, `delete_date`, `expiration_date`, `use_date`)
VALUES (timestamp('2020-09-08 10:09:38.037147'), NULL, NULL, NULL);
SELECT `id`, `item_id`
FROM `scan`
WHERE ROW_COUNT() = 1 AND `id` = LAST_INSERT_ID()

Answer 1

I think the problem is that you use your newItem as reference in the newScan before you have saved the newItem. So the newScan doesn't get an actual DB entity id to populate, only an in-memory copy that needs saving first.

Try it this way - (PS: I haven't tested this, just writing what I believe can fix it)

if (ModelState.IsValid)
{
    var newItem = new Item()
    {
        Name = scan.Name,
        Upc = scan.UPC,
        DateCreated = DateTime.Now,
    };

    _context.Item.Add(newItem);
    await _context.SaveChangesAsync();

    var newScan = new Scan()
    {
        Item = newItem, // after EF has saved the Item entity, it should retrieve the DB generated Item automatically
        CreateDate = DateTime.Now,
        ExpirationDate = scan.ExpirationDate,
    };

    newItem.Scan = new List<Scan>() { newScan };


    _context.Entry(newItem).State = EntityState.Modified;
    _context.Entry(newScan).State = EntityState.Added; // Maybe you should also save this before updating the newItem a second time. It may throw an error if not!
    await _context.SaveChangesAsync();    
    
}