Question 285700 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – EF Core: Disconnected delete from a large child collection with pagination

My question is based on a similar question but with the added complexity of pagination.

public class Parent
{
    public Parent()
    {
        this.Children = new List<Child>();
    }

    public int Id { get; set; }

    public virtual ICollection<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }

    public int ParentId { get; set; }

    public string Data { get; set; }
}

Say the Children collection has 1000 records:

{ 1, 2, 3, … 998, 999, 1000 }

public Parent Get(int parentId, int childrenPageSize)
{
    var existingParent = _dbContext.Parents
    .Where(p => p.Id == parentId)
    .Include(p => p.Children.Take(childrenPageSize))
    .AsNoTracking()
    .SingleOrDefault();
}

With pagination, we only take the first 10 children:

{ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }

The user then edits the Parent object in the front-end application, and deletes 2 children in the collection, and clicks Save to Update the parent:

{ 3, 4, 5, 6, 7, 8, 9, 10 }

I use Update method in the repository class

public void Update(UpdateParentModel model)
{
    var existingParent = _dbContext.Parents
    .Where(p => p.Id == model.Id)
    .Include(p => p.Children)
    .SingleOrDefault();

    // Delete children
    foreach (var existingChild in existingParent.Children.ToList())
    {
        if (!model.Children.Any(c => c.Id == existingChild.Id))
            _dbContext.Children.Remove(existingChild);
    }
}

Question: When Entity Framework loads the existingParent, it eager loads the entire collection of 1000 children. The model argument contains a collection of 8 children as two were deleted. How can I tell Entity Framework to remove only the 2 children that were deleted?

Note: I want to avoid using ExecuteDelete, RawSQL, or Stored Procedures. I need to be able to call _dbContext.SaveChangesAsync().

Tags:

2

Answers


  1. 0

    UpdateParentModel should contain a property of IEnumerable<int> that represents the IDs of Child objects to delete. You can then attach a new Child object as deleted with only the ID value set for each ID value in that enumerable property, avoiding the need to load anything from the database.

    The models you use for your operations do not need to match the entity or entities they’re acting upon. Package the data in the most useful/efficient fashion.

    foreach( var childId in model.RemovedChildIDs )
{
    var entry = dbContext.Attach( new Child
    {
        Id = childId,
    } );

    entry.State = EntityState.Deleted;
}

…

dbContext.SaveChangesAsync();
    Login or Signup to reply.
  2. 0

    If you had the complete set in both cases then it could determine which ones to remove, but you have different sets, one is a paginated set and one is a complete one. Given 8 items, EF has no way of knowing what the original 10 was, unless you tell it. One option is to give it the page size and page number, assuming you can change the "page" of the child view:

    public void Update(UpdateParentModel model, int pageNumber = 1, int pageSize = 10)
{
    var existingParent = _dbContext.Parents
    .Where(p => p.Id == model.Id)
    .Include(p => p.Children
        .OrderBy(c => c.Id)
        .Skip((pageNumber-1)*pageSize)
        .Take(PageSize)
        .ToList())
    .SingleOrDefault();


    var existingChildrenIds = existingParent.Children.Select(x => x.Id);
    var updatedChildrenIds = model.Children.Select(x => x.Id);
    var childrenIdsToRemove = existingChildrenIds.Except(updatedChildrenIds);

    if (childrenIdsToRemove.Any())
    {
        childrenToRemove = existingParent.Children
            .Where(x => childrenIdsToRemove.Any(x.Id))
            .ToList();
        foreach(var child in childrenToRemove)
            existingParent.Children.Remove(child);
    }     
}

    This also avoids iterating over 1000 (or in this case 10) children to see if any need to be removed. Over 10 items it won’t make much of a difference, but over larger sets it can.

    One caveat here is that when using ‘Skip’ & ‘Take’ you should always be including an OrderBy clause to ensure that results for pagination are predictable and repeatable. Without it, larger sets of data could come back in different or unpredictable order based on database index paging.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search