How can I prevent a second user from overwriting an entity property(column) in EF Core?

Question

If I have an entity called "Lesson" and it has an InstructorId as one of it's fields(columns) and I only want 1 instructor being able to register, do I need to create a concurrency token and catch a "DbConcurrencyException" if there is a value?

Ex. 2 instructors enter the controller "RegisterInstructor()", they fetch Lesson and see if there is a value in InstructorId or not, if they both see null they proceed to register. One finishes registering first and now has a value in the DB table Lesson for the InstructorId, but now the second instructor saves and his/her InstructorId will overwrite the first instructors id.

Question - To prevent this, do I need to create a property "version" and set it as a concurrency token so that if there is already an InstructorId in the table, it will throw an db concurrency exception or is there an easier/different way?

public class Lesson {
    public int InstructorId { get; set; }
    public Instructor Instructor { get; set; }
    public int Version { get; set; }
}

modelBuilder.Entity<Lesson>()
  .Property(p => p.Version)
  .IsConcurrencyToken();

Now that I think about it, do I even need a new column/property? Can't I just set the InstructorId column as the ConcurrencyToken() in the OnModelCreating()?

Answer 1

If you don't want to use a Timestamp and instead only want to ensure that an instructor can only be assigned once or otherwise avoid a race condition on that assignment, then an option would be to use an isolated check and update with a specific WHERE clause similar to a concurrency token. One issue with concurrency checks like this is that even if you reload an entity there will always be a window of time, albeit small, where two updates could possibly done leading to a data overwrite. With EF Core 7 there is an ExecuteUpdate() method that can help:

// given we already have the Lesson loaded in a tracked "lesson" entity:

using (var innerContext = _contextFactory.Create())
{
    var rowsUpdated = innerContext.Lessons
        .Where(x => x.LessonId == lesson.LessonId && !x.InstructorId.HasValue)
        .ExecuteUpdate(u => u.SetProperty(x => x.InstructorId, myInstructorId));
    if (rowsUpdated == 0)
         // Handle notifying the user that the item could not be updated.
}   
context.Entry(lesson).Load(); // Reload our tracled entity to reflect any change / current state.

You could execute this on the main DbContext instance, but ExecuteUpdate does not update change tracking and should not be mixed in the event you want to use SaveChanges. So the above example uses an isolated DbContext instance. The main DbContext instance (context) then needs to reload it's current state to reflect the change or the fact that nothing was updated due to a failed race. This example only updates the instructor ID if one hasn't been set. If instructors can change the instructor for a lesson, just you want to guard against the race condition, then change: .Where(x => x.LessonId == lesson.LessonId && !x.InstructorId.HasValue) to .Where(x => x.LessonId == lesson.LessonId && x.InstructorId <> lesson.InstructorId)

That may need additional #null comparison handling, I cannot remember if <> works with #null.