How to use add and remove in the same route controller-CodePudding

Is there a way I can add and remove entities in the same route controller? I tried this method, adding works but removing the entity gave me an error.

[HttpPost]
public async Task<ActionResult<Upvote>> AddRemoveUpvote(CreateUpvoteDTO upvoteDtO)
{
    if (_context.Upvotes == null)
    {
        return Problem("Entity set 'ApplicationDbContext.Upvotes'  is null.");
    }
    var upvote = _mapper.Map<Upvote>(upvoteDtO);
    foreach (var item in _context.Upvotes)
    {
        if (item.UserId == upvote.UserId)
        {
            _context.Upvotes.Remove(item);
            await _context.SaveChangesAsync();
            return Ok("Topic unliked");
        }
    }
    _context.Upvotes.Add(upvote);
    await _context.SaveChangesAsync();
    
    return Ok("Topic Liked");
}

Here is the error while trying to remove:

System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first. at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at Microsoft.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)

CodePudding user response：

You are getting that error because you are still looping over the Upvotes DbSet while trying to save changes. An Entity Framework DbContext uses a single connection to the database. The second problem I see is that you are looping over every single record in the Upvotes DbSet on every call to this API endpoint. That is massively inefficient and will get slower and slower over time as more records are added to that table. Instead, get EF to filter the values to the relevant records only.

A much tidier version of your code would look like this:

// See if an existing upvote exists...
var existingUpvote = await _context.Upvotes
    .Where(uv => uv.UserId == upvote.UserId)
    .SingleOrDefaultAsync();

// If it exists, then remove it
if(existingUpvote != null)
{
    _context.Upvotes.Remove(item);
    await _context.SaveChangesAsync();
    return Ok("Topic unlike");
}

// since we reached here there was no existing vote so we can add a new one
_context.Upvotes.Add(upvote);
await _context.SaveChangesAsync();

return Ok("Topic Liked");

Additionally, I suspect you are also missing a check for the topic so your where clause should probably look more like this:

var existingUpvote = await _context.Upvotes
    .Where(uv => uv.UserId == upvote.UserId && uv.TopicId == upvote.TopicId)
    .SingleOrDefaultAsync();