Foreign key constraint fine on INSERT, fails on UPDATE

Question

I'm using Entity Framework. In my model I made a foreign key nullable, and it seems to be reflected correctly in the database. Here's part of my Asset model class:

public class Asset
{
    [Key]
    public int AssetID { get; set; }

    [Required]
    [MaxLength(100)]
    public string AssetName { get; set; }

    [Required]
    public int AssetTypeID { get; set; }

    [ForeignKey("AssetTypeID")]
    public AssetType AssetType { get; set; }

    public int? SubtypeID { get; set; }

    [ForeignKey("SubtypeID")]
    public Subtype? Subtype { get; set; }
}

As you can see, the SubtypeID and the navigation property are both set to be nullable, and the column in the table is correctly set to nullable too. When inserting a new Asset, it accepts SubtypeID as NULL, however, when updating an already existing Asset to have NULL for SubtypeID, I get the following error:

SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Assets_Subtypes_SubtypeID". The conflict occurred in database "assetinventory", table "dbo.Subtypes", column 'TypeID'.

Here's part of the method throwing that exception:

var updatedAsset = viewModel.Asset;

using (var scope = App.ServiceProvider.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AssetDbContext>();

    SelectedAsset.AssetName = updatedAsset.AssetName;
    SelectedAsset.AssetTypeID = updatedAsset.AssetTypeID;
    SelectedAsset.AssetType = updatedAsset.AssetType;

    if (updatedAsset.SubtypeID == -1)
    {
        SelectedAsset.SubtypeID = null;
        SelectedAsset.Subtype = null;
    }
    else
    {
        SelectedAsset.SubtypeID = updatedAsset.SubtypeID;
        SelectedAsset.Subtype = updatedAsset.Subtype;
    }

    SelectedAsset.Owner = updatedAsset.Owner;
    SelectedAsset.Location = updatedAsset.Location;
    SelectedAsset.PurchaseDate = updatedAsset.PurchaseDate;
    SelectedAsset.Value = updatedAsset.Value;
    SelectedAsset.Status = updatedAsset.Status;
    SelectedAsset.Description = updatedAsset.Description;

    context.Assets.Update(SelectedAsset);

    var log = new AssetLog
    {
        AssetID = SelectedAsset.AssetID,
        Action = "Updated",
        Timestamp = DateTime.Now,
        PerformedBy = AuthenticationService.Instance.CurrentUser.GetTenantProfiles().ElementAt(0).Oid
    };

    context.AssetLogs.Add(log);

    await context.SaveChangesAsync();
}

The exception is thrown on the SaveChangesAsync method call.

I tried a bunch of stuff I found here, asked ChatGPT, everything. Couldn't understand why this might be happening. What can I try next?

Answer 1

Gave up on EF, changed it to raw SQL and it works like a charm.

using(var scope = App.ServiceProvider.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AssetDbContext>();
    var sql = @"UPDATE Assets SET AssetName = {0}, AssetTypeID = {1}, SubtypeID = {2}, Owner = {3}, Location = {4}, PurchaseDate = {5}, Value = {6}, Status = {7}, Description = {8} WHERE AssetID = {9}";
    await context.Database.ExecuteSqlRawAsync(sql, updatedAsset.AssetName, updatedAsset.AssetType.TypeID, updatedAsset.Subtype.TypeID == -1 ? null : updatedAsset.Subtype.TypeID, updatedAsset.Owner, updatedAsset.Location, updatedAsset.PurchaseDate, updatedAsset.Value, updatedAsset.Status, updatedAsset.Description, updatedAsset.AssetID);
}