EF Core is trying to insert a record with an existing primary key

.net-core c# entity-framework-core sql-server

Question

EF Core throws an exception:

System.InvalidOperationException: 'The instance of entity type 'Appointment' cannot be tracked because another instance with the key value '{Id: 6}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.'

when trying to insert a model even though its primary key = 0.

Appointment.cs

public class Appointment
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int Id { get; set; }
    public Doctor Doctor { get; set; }
    public Patient Patient { get; set; }
    public DateTime DateTime { get; set; }

    public Appointment()
    {
    }

    public Appointment(Doctor doctor, Patient patient)
    {
        this.Doctor = doctor;
        this.Patient = patient;
        this.DateTime = DateTime.Now;
    }

    public Appointment(Doctor doctor, Patient patient, DateTime dateTime)
    {
        this.Doctor = doctor;
        this.Patient = patient;
        this.DateTime = dateTime;
    }
}

How I create my Appointment instance:

internal async Task AddAppointmentAsync(int doctorId, int patientId, DateTime dateTime)
{
    Doctor doctor = null;
    Patient patient = null;

    //Retrieve doctor from the db
    using (var doctorController = new DoctorController())
        await Task.Run(() => doctor = doctorController.GetDoctor(doctorId));
    if (doctor == null)
        throw new KeyNotFoundException("Doctor with the specified id doesn't exist.");

    //Retrieve patient from the db
    using (var patientController = new PatientController())
        await Task.Run(() => patient = patientController.GetPatient(patientId));
    if (patient == null)
        throw new KeyNotFoundException("Patient with the specified id doesn't exist.");

    //Create and insert appointment into the db
    Appointment appointment = new Appointment(doctor, patient, dateTime);
    using (_controller = new AppointmentController())
       await Task.Run(() => _controller.AddAppointment(appointment));
}

Add appointment method

public void AddAppointment(Appointment appointment)
{
    if (appointment == null) throw new ArgumentNullException(nameof(appointment));
    if (appointment.Doctor == null) throw new ArgumentNullException(nameof(appointment.Doctor));
    if (appointment.Patient == null) throw new ArgumentNullException(nameof(appointment.Patient));

    Doctor doctor = appointment.Doctor;
    Patient patient = appointment.Patient;
    appointment.Doctor = null;
    appointment.Patient = null;

    this._context.Appointments.Add(appointment); 

    appointment.Doctor = doctor;
    appointment.Patient = patient;

    this._context.SaveChanges(); //Exception is thrown here
}

My context:

public class HospitalContext : DbContext
{
    private const string _connectionString = "Server=(localdb)\\mssqllocaldb;Database=ClientDb;Trusted_Connection=True;";

    public DbSet<Doctor> Doctors { get; private set; }
    public DbSet<Patient> Patients { get; private set; }
    public DbSet<Appointment> Appointments { get; private set; }
    public DbSet<MedicalRecord> MedicalRecords { get; private set; }

    public HospitalContext()
        : base()
    {
    }

    public HospitalContext(DbContextOptions<HospitalContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Doctor>()
            .HasMany(d => d.Appointments)
            .WithOne(a => a.Doctor)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Doctor>()
            .HasMany(w => w.WorkDays)
            .WithOne(a => a.Doctor)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Patient>()
            .HasMany(p => p.Appointments)
            .WithOne(p => p.Patient)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Patient>()
            .HasMany(p => p.MedicalRecords)
            .WithOne(m => m.Patient)
            .OnDelete(DeleteBehavior.Cascade);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(_connectionString);
            optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
            optionsBuilder.EnableSensitiveDataLogging();

        }
    }
}

What I'm passing to the method:

enter image description here

What I already have in my Appointments table:

enter image description here

Update

It looks like my table cannot contain multiple entries with the same DoctorId and PatientId foreign keys. e.g.: Exception thrown when trying to add an entry with DoctorId=3, PatientId=1:

enter image description here

1
0
12/15/2019 5:00:23 PM

Accepted Answer

Assigning entity's State to Added before adding it to the context solved the issue.

Add appointment method

public void AddAppointment(Appointment appointment)
{
    if (appointment == null) throw new ArgumentNullException(nameof(appointment));
    if (appointment.Doctor == null) throw new ArgumentNullException(nameof(appointment.Doctor));
    if (appointment.Patient == null) throw new ArgumentNullException(nameof(appointment.Patient));

    this._context.Entry(appointment).State = EntityState.Added;

    this._context.Appointments.Add(appointment);
    this._context.SaveChanges();
}
0
12/15/2019 5:16:03 PM

Popular Answer

If the entity you try to insert has the ID of an entity already in the database, then very likely it IS the entity already in the database.

Maybe you do something like:

  var item = YourCollection.First();

  item.ID = 0;
  item.DateTime = DateTime.Now


  YourCollection.Add(item);

This is not going to work and will produce your behaviour. You have to physically create a new Item with new Appointment()

The same would also happen, if you insert it twice

  YourCollection.Add(item);
  YourCollection.Add(item);

That's rather not your case, cause you have the ID of an existing item assigned.

The tracking by ID is only one part of Entity Framework, but it also tracks the Entities by reference.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow