entity framework update multiple records using join

Question

I'm trying to update multiple records using entity framework, but am not sure how to proceed.

Basic setup:

class Appointment
{
    public int Id {get; set;}
    public double Charge {get; set;}
    public DateTime Time {get; set;}
}

The view presents a list of appointments, and then the call to controller Post action passes in an Ienumerable<Appointment>.

public async Task<int> UpdateAppointments(IEnumerable<Appointment> appointments){
    // code goes here
    var appointmentsToUpdate = await _context
           .Appointments
           .Where(a => a.time > DateTime.Now).ToListAsync();

    // what to do here??
    // loop through appointmentsToUpdate and find the relevant 
    // record inside appointment, and then do an update? 
    // Seems like a merge would be more efficient.

}

What i want to do is merge appointments and appointmentsToUpdate and update the appointment time. In another scenario, with a different authorization, I want the administrator, for example, to only be able to change the appointment charge, so deleting all records and appending the new records isn't an option.

It seems like you can do this with pure sql statements, but then the appointments parameter is passed in as an IEnumerable, not as a table already in the database as in this answer: Bulk Record Update with SQL

First of all, can you do this kind of update using Linq? Does it translate directly to entity framework (core)?

Short answers - no and no. EF (Core) update process is: retrieve -> add/modify/delete -> `ApplyChanges`. — Ivan Stoev, Feb 08 '18 at 20:28
No you can't. If you want to change one or more values of an entity using entity framework you'll first have to fetch the entity itself, not a copy of some of its values. After fetching, change the entities you want to change, and finally call SaveChanges. When retrieving copies of some of the values, entity framework does not know anymore where you retrieved the values from, and thus can't update them. If you have to do this faster, consider bypassing the DbSets, by using DbContext.DataBase — Harald Coppoolse, Feb 09 '18 at 07:43

David Browne - Microsoft · Answer 1 · 2018-02-08T22:31:32.050

Without extension projects or store SQL the best you can do is to attach the Appointments as unchanged entities, and mark the target property as modified.

The Appointments you attach just need the Key Properties and the Time populated.

Like this:

class Db : DbContext
{

    public DbSet<Appointment> Appointments { get; set; }

    public void UpdateAppointmentTimes(IEnumerable<Appointment> appointments)
    {
        foreach(var a in appointments)
        {
            this.Appointments.Attach(a);
            this.Entry(a).Property(p => p.Time).IsModified = true;
        }
        this.SaveChanges();
    }
     . . .

Which will update only the changed column for all those appointments in a single transaction.

What does `this` refer to in your answer? is it the dbContext? — Allen Wang, Feb 08 '18 at 22:27

entity framework update multiple records using join

1 Answers1