Cannot save changes to Database Context

Question

I have a problem saving the changes to Database Context. When i don't save i can see that the listing status is successfully changed , but when i try to save it I get an error which is saying : " There is already an open DataReader associated with this Connection which must be closed first." And i don't know where that comes from. When i try to do it asynchronous i get the same error.

AdministratorController.cs

 [Route("/Admin")]
    [ApiController]


    public class AdministratorController : Controller
    {
        private readonly dbContext _dbContext;

        public AdministratorController(dbContext dbContext)
        {
            _dbContext = dbContext;
        }


        ///////////////////////////////////
        ///                             //
        ///   Accept or Reject Listings //
        ///                             //
        //////////////////////////////////
        [HttpPost]
        [Route("acceptListing/{listingId}")]
        [AllowAnonymous]
        //[Authorize(Roles="Administrator")]
        public ActionResult AcceptList([FromRoute]int listingId)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest();
            }
            if (listingId == null)
            {
                return NotFound("Listing not found.");
            }

            foreach (Listing listing in _dbContext.Listings)
            {
                Console.WriteLine(listing.Status);
                if(listing.Id == listingId)
                {
                    if(listing.Status == ListingStatus.Accepted)
                    {
                        return BadRequest("Listing already accepted.");
                    }
                    else
                    {
                        listing.Status = ListingStatus.Accepted;
                        Console.WriteLine(listing.Status);
                        _dbContext.SaveChanges();

                    }
                    return Ok();
                }
            }

            return BadRequest("Couldn't find right listing.");
        }

Does this answer your question? [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — quaabaam, Dec 22 '22 at 17:49
Yes, that is his issue, he is iterating through listing while changing them in the same loop — Mocas, Dec 22 '22 at 17:51

score 1 · Answer 1 · answered Dec 22 '22 at 17:49

The problem here is that you are iterating the data being fetched from database and in the same time you are trying to save something from the same context. Quick fix is to use ToList in foreach:

foreach (Listing listing in _dbContext.Listings.ToList())
{
   // ..
}

But in general you should not fetch everything from the database to process only one item. Just write query that will filter everything on database side. Something along this lines (not tested):

var listing = _dbContext.Listings.FirstOrDefault(l => l.Id == listingId);
if (listing is null)
{
    return NotFound();
}

if (listing.Status == ListingStatus.Accepted)
{
    return BadRequest("Listing already accepted.");
}
else
{
    listing.Status = ListingStatus.Accepted;
    Console.WriteLine(listing.Status);
    _dbContext.SaveChanges();
}
return Ok();

Mocas · Accepted Answer · 2022-12-23T12:53:08.887

Rather than looping through all listings looking for the one with the Id you want, just filter and get.

Listing? listing = _dbContext.Listings.FirstOrDefault(l => l.Id == listingId);

if (listing is null)
{
    return BadRequest("Couldn't find right listing.");
}     

if(listing.Status == ListingStatus.Accepted)
{
    return BadRequest("Listing already accepted.");
}

listing.Status = ListingStatus.Accepted;
Console.WriteLine(listing.Status);
_dbContext.SaveChanges();

return Ok();

score 0 · Answer 3 · answered Dec 22 '22 at 17:49

change the code like this :

var listings = _dbContext.Listings.Tolist();
foreach (Listing listing in listings)
        {
            Console.WriteLine(listing.Status);
            if(listing.Id == listingId)
            {
                if(listing.Status == ListingStatus.Accepted)
                {
                    return BadRequest("Listing already accepted.");
                }
                else
                {
                    listing.Status = ListingStatus.Accepted;
                    Console.WriteLine(listing.Status);
                    _dbContext.Update(listing);
                    _dbContext.SaveChanges();
                }
                return Ok();
            }
        }

Cannot save changes to Database Context

3 Answers3

Linked