Only Update Entity (Parent or children) if Property Values Have Changed with EF

Question

As I build a project with Entity Framework 6 (using EF for the first time), I noticed that when I only Update the relationships of an Entity, EF updates the main Entity too.

I can tell this is happening because I'm using System Versioned tables on Sql Server 2017.

This is a made up scenario, but most of the concept is here.

public async Task<ActionResult> Edit([Bind(Include="Id,Name,LocationTimes")] LocationViewModel locationVM) {
    if (ModelState.IsValid) {
        var location = await _db.Locations.FirstOrDefaultAsync(l => l.Id == locationsViewModel.Id && l.UserId == UserId);
        if (location == null) {
            return HttpNotFound();
        }

        location.Name = locationsViewModel.Name;
        // ... other properties

        foreach (var day in locationsViewModel.LocationTimes.Days) {
            var time = new Time {
                Day = day.Day,
                OpenTime = day.OpenTime,
                CloseTime = day.CloseTime,
            };

            // Find current Time or keep newly created
            time = await time.FindByTimeAsync(time, _db) ?? time;

            // Find LocationTime with same day
            var locationTime = location.LocationTimes.FirstOrDefault(lt => lt.Time.Day == day.Day);

            // If all times are the same, skip (continue)
            if (locationTime != null && locationTime.Time.OpenTime == time.OpenTime && locationTime.Time.CloseTime == time.CloseTime)
                continue;

            if (locationTime != null && (locationTime.Time.OpenTime != time.OpenTime || locationTime.Time.CloseTime != time.CloseTime)) {
                // Remove, At least one of the Times do not match
                locationTime.Time = time;
                _db.Entry(locationTime).State = EntityState.Modified;
            } else {
                location.LocationTimes.Add(new LocationTime {
                    Location = location,
                    Time = time,
                });
            }
        }

        _db.Entry(location).State = EntityState.Modified;
        await _db.SaveChangesAsync();
        return RedirectToAction("Index");
    }
}

I assume, that by marking the entire Entity as Modified, EF will call the update statement.

How can I avoid an UPDATE to the parent Entity, if no properties have changed on the parent, but still Add/Update the child relationships?

I assume I have to check that each property has not changed and therefore I should not be setting location state to Modified, but how would I handle the newly added Times?

Update #1

So I tried what I mentioned and it works, but is this the correct way to do this?

public async Task<ActionResult> Edit([Bind(Include="Id,Name,LocationTimes")] LocationViewModel locationVM) {
    if (ModelState.IsValid) {
        var location = await _db.Locations.FirstOrDefaultAsync(l => l.Id == locationsViewModel.Id && l.UserId == UserId);
        if (location == null) {
            return HttpNotFound();
        }

        /*******************
        This is new part
        *******************/
        if (
            location.Name != locationsViewModel.Name
        //  || ... test other properties
        ) {
            location.Name = locationsViewModel.Name;
            // ... other properties

            _db.Entry(location).State = EntityState.Modified;
        } else {
            _db.Entry(location).State = EntityState.Unchanged;
        }
        /*******************/

        foreach (var day in locationsViewModel.LocationTimes.Days) {
            var time = new Time {
                Day = day.Day,
                OpenTime = day.OpenTime,
                CloseTime = day.CloseTime,
            };

            // Find current Time or keep newly created
            time = await time.FindByTimeAsync(time, _db) ?? time;

            // Find LocationTime with same day
            var locationTime = location.LocationTimes.FirstOrDefault(lt => lt.Time.Day == day.Day);

            // If all times are the same, skip (continue)
            if (locationTime != null && locationTime.Time.OpenTime == time.OpenTime && locationTime.Time.CloseTime == time.CloseTime)
                continue;

            if (locationTime != null && (locationTime.Time.OpenTime != time.OpenTime || locationTime.Time.CloseTime != time.CloseTime)) {
                // Remove, At least one of the Times do not match
                locationTime.Time = time;
                _db.Entry(locationTime).State = EntityState.Modified;
            } else {
                location.LocationTimes.Add(new LocationTime {
                    Location = location,
                    Time = time,
                });
            }
        }

        /* removed, added above */
        //_db.Entry(location).State = EntityState.Modified;
        await _db.SaveChangesAsync();
        return RedirectToAction("Index");
    }
}

If you do not want location changes persisted, either [set it's state to Unchanged](https://msdn.microsoft.com/en-us/library/jj592676(v=vs.113).aspx) or fetch it without tracking [_db.Locations.AsNoTracking().FirstOrDefaultAsync(l => l.Id == locationsViewModel.Id && l.UserId == UserId);](https://msdn.microsoft.com/en-us/data/jj556203) and don't change it's state to modified. — Steve Greene, Feb 23 '18 at 17:32
@SteveGreene, so you're saying, I should compare each property to see if any have changed, and only if there is a change, mark it as `Unchanged`? If I modify a relationship, will that relationship Update/Add? — RoLYroLLs, Feb 23 '18 at 17:42
I'm unclear on what you want. It appears to me you have a parent (location) with a child collection (locationTimes). The title of your question implies you want to update the children without updating the parent. Dealing with the child collection is the hard part. See [here](https://stackoverflow.com/questions/33056482/is-it-really-impossible-to-update-child-collection-in-ef-out-of-the-box-aka-non). — Steve Greene, Feb 23 '18 at 17:53
If your question is about only modifying changed properties, see [here](https://stackoverflow.com/questions/3642371/how-to-update-only-one-field-using-entity-framework). — Steve Greene, Feb 23 '18 at 17:55
@SteveGreene Sorry about the confusion, That's why I added "..Without Always...". I'll make sure the last question in the post is more clear about only updating the parent if it has changed. — RoLYroLLs, Feb 23 '18 at 17:57
@SteveGreene, thanks for that link. It look promising. I guess I do have to check every property in some way. — RoLYroLLs, Feb 23 '18 at 17:58
Personally, we don't worry about that. If the user clicks save and the object is valid we just fetch it, replace the values and save it. [Automapper](http://automapper.org/) makes it easy. — Steve Greene, Feb 23 '18 at 18:04
@SteveGreene, Unfortunately, with `System Versioned` tables, this causes rows to be added to the `History Table` as if the record changed, even when the record did not. — RoLYroLLs, Feb 23 '18 at 18:06

score 0 · Accepted Answer · answered Feb 25 '18 at 20:15

So after trial and error, I guess I misunderstood how EF handles the EntityState. I though if a child was Modified, you had to set the parent as Modified as well.

Gladly, that's not the case and the code below works as desired.

public async Task<ActionResult> Edit([Bind(Include="Id,Name,LocationTimes")] LocationViewModel locationVM) {
    if (ModelState.IsValid) {
        var location = await _db.Locations.FirstOrDefaultAsync(l => l.Id == locationsViewModel.Id && l.UserId == UserId);
        if (location == null) {
            return HttpNotFound();
        }

        /*******************
        This is new part

        check if at least one property was changed
        *******************/
        if (
            location.Name != locationsViewModel.Name
            || location.Ref != locationsViewModel.Ref
        //  || ... test other properties
        ) {
            location.Name = locationsViewModel.Name;
            location.Ref = locationsViewModel.Ref;
            // ... other properties

            // Tell EF that the Entity has been modified (probably not needed, but just in case)
            _db.Entry(location).State = EntityState.Modified;
        } else {
            // Tell EF that the Entity has *NOT* been modified
            _db.Entry(location).State = EntityState.Unchanged;
        }
        /*******************/

        foreach (var day in locationsViewModel.LocationTimes.Days) {
            var time = new Time {
                Day = day.Day,
                OpenTime = day.OpenTime,
                CloseTime = day.CloseTime,
            };

            // Find current Time or keep newly created
            time = await time.FindByTimeAsync(time, _db) ?? time;

            // Find LocationTime with same day
            var locationTime = location.LocationTimes.FirstOrDefault(lt => lt.Time.Day == day.Day);

            // If all times are the same, skip (continue)
            if (locationTime != null && locationTime.Time.OpenTime == time.OpenTime && locationTime.Time.CloseTime == time.CloseTime)
                continue;

            if (locationTime != null && (locationTime.Time.OpenTime != time.OpenTime || locationTime.Time.CloseTime != time.CloseTime)) {
                // Remove, At least one of the Times do not match
                locationTime.Time = time;
                _db.Entry(locationTime).State = EntityState.Modified;
            } else {
                location.LocationTimes.Add(new LocationTime {
                    Location = location,
                    Time = time,
                });
            }
        }

        /* removed, added above */
        //_db.Entry(location).State = EntityState.Modified;
        await _db.SaveChangesAsync();
        return RedirectToAction("Index");
    }
}

Only Update Entity (Parent or children) if Property Values Have Changed with EF

1 Answers1