EntityFramework6 "FOREIGN KEY constraint failed" on nullable foreign key

Question

I have my entity defined like this:

public class Entity : BaseModel // Has the already ID defined
{
    private int? companyId;

    public Company? Company { get; set; }

    public int? CompanyId {
        get => this.companyId == 0 ? null : this.companyId; // I tried this for debugging purposes to force this value to "null" -> made no difference
        set => this.companyId = value;
    }
}

public class Company : BaseModel // Has the already ID defined
{
    public IEnumerable<Entity> Entities { get; set; } = new List<Entity>();
}

Anyway, if I set the CompanyId to null, my DB throws an exception with the message: "FOREIGN KEY constraint failed". If the CompanyId is set to, e.g. 123, the relationship is resolved accordingly.

I mean, it makes sense, that EF cannot find null in my DB, but how do I want to set an optional value otherwise? I am using code first annotations only, hence my OnModelCreating of my context is completely empty.

Answer 1

How are you loading the entities in the first place? Are you loading an Entity by ID and trying to dis-associate it from a company, or have you loaded a company with it's entities and trying to remove one association?

Normally when working with relations where you have navigation properties, you want to de-associate them (or delete them) via the navigation properties, not the FK properties. For instance if loading a company and wanting to de-associate one of the entities you should eager-load the entities then remove the desired one from the collection:

var company = _context.Companies.Include(c => c.Entitites).Single(c => c.Id == companyId);
var entityToRemove = company.Entities.SingleOrDefault(e => e.Id == entityId);
if(entityToRemove != null)
company.Entities.Remove(entityToRemove);
_context.SaveChanges();

Provided that the relationship between Company and Entity is set up properly as an optional HasMany then provided these proxies are loaded, EF should work out to set the entityToRemove's FK to null.

If you want to do it from the Entity side:

var entityToRemove = _context.Entities.Include(e => e.Company).Single(e => e.Id == entityId);
entityToRemove.Company = null;
_context.SaveChanges();

That too should de-associate the entities. If these don't work then it's possible that your mapping is set up for a required relationship, though I am pulling this from memory so I might need to fire up an example to verify. :) You also should be checking for any code that might set that CompanyId to 0 when attempting to remove one, whether that might be happening due to some mapping or deserialization. Weird behaviour like that can occur when entities are passed around in a detached state or deserialized into controller methods. (which should be avoided)

Update: Code like this can be very dangerous and lead to unexpected problems like what you are encountering:

public virtual async Task<bool> Update(TModel entity)
{
   Context.Update(entity);
   await Context.SaveChangesAsync();
   return true;
}

Update() is typically used for detached entities, and it will automatically treat all values in the entity as Modified. If model was already an entity tracked by the Context (and the context is set up for change tracking) then it is pretty much unnecessary. However, something in the calling chain or wherever has constructed the model (i.e. Entity) has set the nullable FK to 0 instead of #null. This could have been deserialized from a Form etc. in a view and sent to a Controller as an integer value based on a default for a removed selection. Ideally entity classes should not be used for this form of data transfer from view to controller or the like, instead using a POCO view model or DTO. To correct the behaviour as your code currently is, you could try the following:

public async Task<bool> UpdateEntity(Entity entity)
{
   var dbEntity = Context.Set<Entity>().Include(x => x.Customer).Single(x => x.Id == entityId);
   if (!Object.ReferenceEquals(entity, dbEntity))
   {   // entity is a detached representation so copy values across to dbEntity.
       // TODO: copy values from entity to dbEntity
       if(!entity.CustomerId.HasValue || entity.CustomerId.Value == 0)
           dbEntity.Customer = null;
   }
   await Context.SaveChangesAsync();
   return true;
}

In this case we load the entity from the DbContext. If this method was called with an entity tracked by the DbContext, the dbEntity would be the same reference as entity. In this case with change tracking the Customer/CustomerId reference should have been removed. We don't need to set entity state or call Update. SaveChanges should persist the change. If instead the entity was a detached copy deserialized, (likely the case based on that 0 value) the reference would be different. In this case, the allowed values in the modified entity should be copied across to dbEntity, then we can inspect the CustomerId in that detached entity for #null or 0, and if so, remove the Customer reference from dbEntity before saving.

The caveats here are: This won't work as a pure Generic implementation. To update an "Entity" class we need knowledge of these relationships like Customer so this data service, repository, or what-have-you implementation needs to be concrete and non-generic. It can extend a Generic base class for common functionality but we cannot rely on a purely Generic solution. (Generic methods work where implementation is identical across supported classes.) This also means removing that attempt at trying to handle Zero in the Entity class. It should just be:

public class Entity : BaseModel
{
    public Company? Company { get; set; }
    [ForeignKey("Company")]
    public int? CompanyId { get; set; }

    // ...
}

Marking Foreign Keys explicitly is a good practice to avoid surprises when you eventually find yourself needing to break conventions that EF accommodates in simple scenarios.