I need to erase collection from Entity Framework

Question

I can add, but not erase any item with the collection - unable to delete.

Found a few partial solutions, but nothing to guide me to a working solution. I can easily add values to the collection; ny help is appreciated.

I have the following:

[HttpPut("updateSOJ4")]
public IActionResult UpdateSOJ4([FromBody] Routing_Tool_SOJ4 Routing_Tool_SOJ4)
{
    Routing_Tool_SOJ4 request = new Routing_Tool_SOJ4();
    request.Id = Routing_Tool_SOJ4.Id;
    request.Routing_Tool_Services = Routing_Tool_SOJ4.Routing_Tool_Services;
    request.Routing_ToolId = Routing_Tool_SOJ4.Routing_ToolId;

    _repository.UpdateSOJ4(request);

    return Ok(request);
}

Here is where I was trying the different solutions, but, I am still stuck:

public void UpdateSOJ4(object routing_Tool_SOJ4)
{
    // var missingItem = _context.Routing_Tool_Service.Where(i => i.Routing_Tool_SOJ4Id == _context.Routing_Tool_SOJ4.Id).First(); -- DOES NOT WORK
    _context.Update(routing_Tool_SOJ4).State = EntityState.Modified;
    _context.SaveChanges();
}

Here is the database structure:

public class Routing_Tool_SOJ4
{
    [Key]
    [Required]
    public int Id { get; set; }
    
    public int Routing_ToolId { get; set; }
    
    [ForeignKey("Routing_ToolId")]
    public virtual Routing_Tool Routing_Tool { get; set; }
    
    public virtual ICollection <Routing_Tool_Service> Routing_Tool_Services { get; set; }
} 

Collection:

public class Routing_Tool_Service
{
    [Key]
    [Required]
    public int Id { get; set; }

    public string ServiceName { get; set; }

    [Required]
    [ForeignKey("Routing_Tool_SOJ4Id")]
    public int Routing_Tool_SOJ4Id { get; set; }
}

Answer 1

What I am deduce from your question is you have a method that accepts an updated Routing Tool object which contains an updated collection of Tool Services. You want to update that tool and it's associated services so that any service within that tool that is new gets added, otherwise updated, and any existing tool in the DB that is no longer in the passed in collection should be deleted..

If this is the case, you need to compare the provided version of the data to the database version of the data. For this example I am not using your Repository instance because I have no idea how it is implemented. Generally this pattern should be avoided unless there is a really good reason to have it.

[HttpPut("updateSOJ4")]
public IActionResult UpdateSOJ4([FromBody] Routing_Tool_SOJ4 updatedRoutingTool)
{
    using (var context = new AppDbContext())
    {
        // Get tool and services from DB.
        var existingRoutingTool = context.Routing_Tool_SOJ4s
            .Include(x => x.Routing_Tool_Services)
            .Single(x => x.Id == updatedRoutingTool.Id);

        // Copy values that can be updated from the updatedRoutingTool to existingRoutingTool.
        // ...

        var updatedServiceIds = updatedRoutingTool.Routing_Tool_Services
            .Select(x => x.Id)
            .ToList();
        var existingServiceIds = existingRoutingTool.Routing_Tool_Services
            .Select(x => x.Id)
            .ToList();
        var serviceIdsToRemove = existingServiceIds
            .Except(updatedServiceIds)
            .ToList();
         
        foreach (var service in updatedRoutingTool.Routing_Tool_Services)
        {
            var existingService = existingRoutingTool.Routing_ToolServices
                .SingleOrDefault(x => x.Id == service.Id);
            if (existingService == null)
                existingRoutingTool.Routing_Tool_Services.Add(service);
            else
            {
                 // Copy allowed values from service to existingService
            }
        }

        if(serviceIdsToRemove.Any())
        {
            var servicesToRemove = existingRoutingTool.Routing_Tool_Services
                .Where(x => serviceIdsToRemove.Contains(x.Id))
                .ToList();
            foreach(var serviceToRemove in servicesToRemove)
                existingRoutingTool.Routing_Tool_Services.Remove(serviceToRemove);
        }

        context.SaveChanges();
    }

    return Ok(request);
}

Normally the DbContext or Unit of Work would be injected into your controller, or the logic would be handed off to a service. This example uses a using block with a DbContext just to outline the minimum viable process flow for the operation.

Essentially load the current data state, compare that with the provided state to determine what needs to be added, updated, or removed.

Generally speaking when it comes to RESTful web services my recommendation is to avoid large update operations like this and instead structure the application to perform more atomic operations such as adding and removing services for a given tool as a distinct operation, working with a persisted copy (i.e. cached instance) of the data if you want the whole related operation to be committed to data state or abandoned at a higher level. This can help keep message sizes small, and server code more compact & worrying about a single responsibility. The risk of performing these large operations is that the passed in data must represent a complete picture of the data state or you could end up deleting/clearing data you don't intend. For example if you later want to optimize your code so that only added and updated services are sent over the wire, not unchanged services (to reduce message size) the above code will not work as it would delete anything not sent.