Entity Framework Core: Update() method INSERTs instead of UPDATEs on dependent entity

Question

It seems like EF Core is doing an INSERT instead of an UPDATE, and thus MySQL complains about a duplicate key. However, I am using the Update method on the DbSet and the entities do have primary keys set. This results in a DUPLICATE ENTRY error in MySql.

Running VS 2019, EF Core 3.1.1 and ASP.NET Core 3.1

Model (I do not use Fluent config for relationships, just Convention):

public class Vehicle 
{
    public long Id {get; set; } // PRIMARY KEY, AUTO INCREMENT
    public string RegistrationNumber { get; set; }
    public VehicleSession Session { get; set; }
}


public class VehicleSession
{
    public long VehicleId { get; set; }
    public Vehicle Vehicle { get; set; }
    public string DeviceUuid { get; set; } // PRIMARY KEY
    public string AuthenticationToken { get; set; }
    public string OSName { get; set; }
    public string OSVersion { get; set; }
    public string DeviceModel { get; set; }
}

Database:

Table 'vehiclesessions' where the PRIMARY KEY is DeviceUuid and has key 'device2':

Controller:

A request comes in from somewhere. DeviceUuid is fetched from HTTP headers.

    public async Task<ActionResult<VehicleLoginResponse>> Login(VehicleLogin login)
    {
        Request.Headers.TryGetValue(BaseConstants.DEVICE_UUID, out StringValues deviceUuid);
        Vehicle vehicle = await vehicleService.Authenticate(login.Username, login.Password); // returns a Vehicle by asking dbContet: dbContext.Vehicles.FirstOrDefault(v => v.Username.Equals(username));
        VehicleSession vs = await vehicleService.CreateSession(vehicle, login, deviceUuid);
        // ...
    }

Service:

This service creates a new VehicleSession object, assigns it to the Vehicle property, and does an .Update on the Vehicle, so that the new session is saved with it.

    public async Task<VehicleSession> CreateSession(Vehicle vehicle, VehicleLogin vehicleLogin, string deviceUuid)
    {
        VehicleSession vs = new VehicleSession()
        {
            Vehicle = vehicle,
            AuthenticationToken = someTokenFetchedFromSomewhere,
            DeviceModel = vehicleLogin.DeviceModel,
            DeviceUuid = deviceUuid, // is 'device2'
            OSName = vehicleLogin.OSName,
            OSVersion = vehicleLogin.OSVersion
        };

        vehicle.Session = vs;
        dbContext.Vehicles.Update(vehicle);
        await dbContext.SaveChangesAsync();
        return vs;
    }

It doesn't matter if I replace the new VehicleSession and assignment, with just editing an already existing Vehicle.Session, same error:

    public async Task<VehicleSession> CreateSession(Vehicle vehicle, VehicleLogin vehicleLogin, string deviceUuid)
    {
        if (vehicle.Session == null)
            vehicle.Session = new VehicleSession(); // never executes this line
        vehicle.Session.AuthenticationToken = someTokenFetchedFromSomewhere;

        vehicle.Session.DeviceModel = vehicleLogin.DeviceModel;
        vehicle.Session.DeviceUuid = deviceUuid;
        vehicle.Session.OSName = vehicleLogin.OSName;
        vehicle.Session.OSVersion = vehicleLogin.OSVersion;

        await dbContext.SaveChangesAsync();
        return vehicle.Session;
    }

When doing so, I get an error saying:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'device2' for key 'vehiclesessions.PRIMARY' ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'device2' for key 'vehiclesessions.PRIMARY'

The SQL produced:

 Failed executing DbCommand (125ms) [Parameters=[@p0='?' (Size = 95), @p1='?' (Size = 95), @p2='?' (Size = 4000), @p3='?' (Size = 4000), @p4='?' (Size = 4000), @p5='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
      INSERT INTO `VehicleSessions` (`DeviceUuid`, `AuthenticationToken`, `DeviceModel`, `OSName`, `OSVersion`, `VehicleId`)
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'blabla'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'device2' for key 'vehiclesessions.PRIMARY'
       ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'device2' for key 'vehiclesessions.PRIMARY'

Why am I getting this error? It seems like an INSERT is done instead of an UPDATE, even though the Vehicle object is fetched from dbContext, passed along and has PRIMARY id set.

Answer 1

So, I think I might have found the issue.

As noted above, I first did a search to retrieve the Vehicle, like this:

Vehicle vehicle = dbContext.Vehicles.FirstOrDefault(v => v.Username.Equals(username));

In this case, the Vehicle.Session, if one existed, was not populated. Later on, I did the update as the code above shows, and it failed as noted above.

But, if I change the fetch code to this:

Vehicle entityVehicle = dbContext.Vehicles
    .Include(x => x.Session)// <-- NEW!
    .FirstOrDefault(v => v.Username.Equals(username));

then it works.

It doesn't matter if I assign the .Session a new VehicleSession(...) or if I change the properties in the existing object. It also doesn't matter if I use dbContext.Vehicles.Update(vehicle); before await dbContext.SaveChangesAsync();, the call to .Update(...) is not needed.

The only thing that made a difference, was to use .Include(...).

---

My own theory is:

When fetching the object from db, the EF Core "tracker" starts tracking. Property Session is NULL. When Session later is populated, the tracker detects that it was NULL but now is not NULL, thus, it figures that an INSERT is needed.

And then, if you populate the Session on fetch, tracker sees it is there, and should be updated.