Why is setting the navigation property to null setting the foreign key property also to null?

Question

I use EF Core 7.0.3.

Here is my entity:

[Table("Common_T230")]
public class Employment 
{
    [Column("F230_ID")]
    public int Id { get; set; }

    [Column("F230_14")]
    public string VehicleNumber { get; set; }
    public virtual Vehicle Vehicle { get; set; }
}

And here are the definitions for the relationship:

modelBuilder.Entity<Employment>()
            .HasOne(e => e.Vehicle)
            .WithMany()
            .HasPrincipalKey(e => e.VehicleNumber)
            .HasForeignKey(e => e.VehicleNumber);

Now when I load the employment and set the VehicleNumber (the foreign key value) and set the navigation property to null, ctx.SaveChanges() will change null to F230_14 (VehicleNumber).

Can someone explain to me why this happen? VehicleNumber contains a valid foreign key value.

var employment = query.Include(x => x.Vehicle)
                      .FirstOrDefault(x => x.Date == currentDate 
                                           && x.ProjectNumber == projectNumber);
employment.PersonelNumber = personelNumber;
employment.Vehicle = null;
employment.VehicleNumber = vehicleNumber ?? "";

// here, EF Core will save null to F230_14 (VehicleNumber)
ctx.SaveChanges();

Answer 1

When you set the navigation property to NULL, you are saying that "this object does not have an associated Vehicle", thus there is no foreign key value. There might be scenarios (e.g. lazy loading) where you start with a foreign key value but no object (I have not used EF in a while), but if you explicitly set the object to NULL, I would expect it to set the FK value to NULL as well.

Answer 2

This has to do with the way your underlying data store represents related data. If you look at the database table representing your entity, it doesn't have a Vehicle on it. The concept that a row of Vehicle data is associated with the Employment row is modeled by having a VehicleNumber property (the F230_14 field) that matches the primary key of a Vehicle row.

So the only way to remove a Vehicle from an Employment in the database is to set the VehicleNumber to null. Entity Framework assumes that is your intent when you set the Vehicle property to null, so it changes that value when you save your changes.

Answer 3

In EF, if you have both a FK property and a navigation property, changes to the navigation property take precedence, even if you set both values. So the answer is if you want to update a navigation property by it's FK, just set the FK and do not touch the navigation property. After SaveChanges, EF will automatically load and associate the new navigation property. (if it was eager loaded in the first place)

var employment = query
    .Include(x => x.Vehicle)
    .FirstOrDefault(x => x.Date == currentDate && x.ProjectNumber == projectNumber);
employment.PersonelNumber = personelNumber;
employment.VehicleNumber = vehicleNumber;

Though I suspect you probably don't want to set the VehicleNumber to an empty string if it is null, but rather set it to #null if there is no Vehicle associated. VehicleNumber is a FK, so unless you have a Vehicle record with an ID of string.Empty in your DB for something like "Unassigned", that #null guard might cause you problems if you happen to hit a #null scenario.