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?

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?

• c#
• database
• entity-framework

• you might try searching their bug tracker: github/dotnet/efcore/issues – pestophagous Commented Mar 7 at 16:51
• Did you change any of the entity classes in c# or any fields in the database. With Entirty when changes are made to the classes or database you must run migration to sync the c# classes with the tables in the database. Run Migration with the option Update. This is different from the Update in your posted c# code. – jdweng Commented Mar 7 at 18:25
• Yes, of course, I ran the migration and updated the database, as I mentioned in the question, it's reflected properly in the database. The SubtypeID column is nullable. When I add a new Asset by calling context.Assets.Add(asset) with an Asset that has NULL for SubtypeID, it runs fine, and the row is added to the database. So this is why I'm confused. – user29931385 Commented Mar 8 at 5:59
• Did the code actually hit the condition if (updatedAsset.SubtypeID == -1)? There's no reason why this shouldn't work. – Gert Arnold Commented Mar 9 at 19:36

3 Answers 3

Be careful when updating entities using navigation properties sourced from values coming in, especially something like a deserialized copy. For instance:

    SelectedAsset.SubtypeID = updatedAsset.SubtypeID;
    SelectedAsset.Subtype = updatedAsset.Subtype;

Typically I recommend using a shadow property for FKs where a navigation property is used rather than exposing both columns. When your entity exposes both a navigation and update both values, it is the navigation property that takes precedence. Check the values of the navigation property to see if the ID of that Subtype was actually populated, and that it wasn't #null. Normally if this is something like an API where the updatedAsset and associated Subtype were deserialized, the SubType would not be recognized automatically as an "existing" record because it is not attached to the DbContext. EF by default would attempt to insert a row or trying to associate a row with a default ID (I.e. 0 or -1)

You can opt to just update the relationship by setting the FK so long as you aren't counting on serializing/using the SelectedAsset and it's Subtype since setting the FK and not changing the navigation property will trigger an UPDATE to the database, but after saving the SelectedAsset.Subtype will not be reloaded automatically after SaveChanges is called, meaning if you serialize the SelectedAsset to a view it would be the old navigation property.

    SelectedAsset.SubtypeID = updatedAsset.SubtypeID;
    //SelectedAsset.Subtype = updatedAsset.Subtype; // --Only set FK

Normally when exposing a navigation property and updating the reference (subtype) using a FK value you would either fetch the SubType from the DbContext or Attach() the copy after checking the DbContext isn't already tracking. So for instance if the passed in updatedAsset has a SubTypeId:

Fetching the Subtype to associate:

if (SelectedAsset.Subtype.Id != updatedAsset.SubtypeID)
    SelectedAsset.SubType = _dbContext.Subtypes.Single(x => x.ID == updatedAsset.SubtypeID)

Fetching the subtype will pull from the DB if the subtype isn't already tracked. This also serves as a check that the SubtypeID is valid.

Attaching the Subtype:

if (SelectedAsset.Subtype.Id != updatedAsset.SubtypeID)
{
    var existingSubtype = _dbContext.Subtypes.Local.FirstOrDefault(x => x.ID == updatedAsset.SubType.ID);
    if (existingSubtype == null)
        existingSubtype = _dbContext.Attach(updatedAsset.Subtype);
    SelectedAsset.SubType = existingSubtype;
}

Attaching is not always as simple as calling Attach() since there can be situations where the DbContext could already be tracking an instance of the Subtype for the same ID, where Attach() would result in an exception raised. This is situational based on what instances happen to be tracked so it can seem like an intermittent error at runtime. (Works most of the time then fails for some particular user/scenario) The above checks against .Local check against the tracking cache and use that reference if found, or attach and use if not.

you might want to double check that the SubtypeID column in your database allows NULL. If it doesn't, modify the constraint to allow it. something like this:

ALTER TABLE Assets
DROP CONSTRAINT FK_Assets_Subtypes_SubtypeID;
ALTER TABLE Assets
ADD CONSTRAINT FK_Assets_Subtypes_SubtypeID
FOREIGN KEY (SubtypeID) REFERENCES Subtypes(TypeID) ON DELETE SET NULL;

Even though you’ve ran migrations, ensure that the database schema is fully up to date:

dotnet ef migrations add UpdateForeignKeyConstraint
dotnet ef database update

It might be worth revisiting how the update is done. Since it works with Add() but not Update(), there might be a difference in tracking the entity state. If needed, use context.Attach() to track the entity during the update:

context.Attach(SelectedAsset); 
context.Entry(SelectedAsset).State = EntityState.Modified;

Ensure that the relationship is configured correctly with the Fluent API:

modelBuilder.Entity<Asset>()
    .HasOne(a => a.Subtype)
    .WithMany()
    .HasForeignKey(a => a.SubtypeID)
    .OnDelete(DeleteBehavior.SetNull);

This should resolve the issue and allow you to update the SubtypeID to NULL without violating the foreign key constraint.

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