CODEWITHSUNDEEP
c#sql-serverazureentity-framework-core
Andre.Santarosa
Andre.Santarosa

Reputation: 1280

An exception occurred while iterating over the results of a query for context type. The connection is closed

I'm getting the following error during a LINQ query running

An exception occurred while iterating over the results of a query for context type. The connection is closed

It's curious that this happens only when app is published on Azure (Db is also in Azure) locally everything works like a charm

The following block of code generates the error

List<StoreProductCatalogPrice> pricesToUpdate = await _storeProductCatalogPriceRepository.GetCurrentByProductCatalogId(productCatalogToUpdate);`

Note: productCatalogToUpdate is a large List<Guid> with around 7k Guids

Repository implementation:

public async Task<List<StoreProductCatalogPrice>> GetCurrentByProductCatalogId(List<Guid> productCatalogsIds)
{          
    return await DbSet.Where(x => productCatalogsIds.Contains(x.StoreProductCatalogId)).ToListAsync();
}

Note 2: everything related to context is handled by native DI, via AddDbContext<T>()

Any idea why this happens?

Upvotes: 11

Views: 58253

Answers (5)

mikiyas wendu
mikiyas wendu

Reputation: 1

So I have this action that retrieves products from the database

[HttpGet("products")]
public async Task<IActionResult> GetProductsFromDb()
{

   var items =  _repository.ScrappedData.GetScrappedDataAsync(trackChanges:false);
   if(items == null)
   {
    _logger.LogInfo($"there is no product scrapped in your database");
    return NotFound();
   }

   var productsToReturn = _mapper.Map<IEnumerable<ScrappedProductReadDto>>(items);
        
   return Ok(productsToReturn);

}

I was hitting this endpoint but it was throwing this error "Microsoft.EntityFrameworkCore.Query[10100] An exception occurred while iterating over the results of a query for context type 'Entities.RepositoryContext'. System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Guid'."

the problem was I was executing the GetScrappedDataAsync method and proceed to the next code underneath it. for me only putting await before accessing the repository fixed it.

Upvotes: -1

tme5092
tme5092

Reputation: 389

In my case, the error occurred as a result of manually in database modified data that did not map well to associated C# types.
I was using SQLite which does not support DateTime, so Text is used for this column. Some values had an empty string "" assigned, which could not be converted to a valid DateTime, whereas null was completely fine.

Upvotes: -1

Geo242
Geo242

Reputation: 597

I was able to resolve this issue by using .AsSplitQuery() due to having many includes.

Upvotes: 1

Mohammad Komaei
Mohammad Komaei

Reputation: 9676

I got this error because I wrote async Add() method but called Add() method without await.

Upvotes: -1

Andre.Santarosa
Andre.Santarosa

Reputation: 1280

@GertArnold were absolutely right, thanks buddy!

The problem was that .Contains() is too slow receiving a large numbers of items and was causing this strange behavior. To fix it I followed the answer pointed by @GertArnold that lead me to another post.

The strategy is break the large amount of items in chunks and create multiple queries in the end, it may sound weird that many queries will run faster than a single one, but I've made some benchmarks before adopt the solution and the code proven to be around 30% faster even running 14 queries (in my case) instead of a single one.

Here is the final code:

public async Task<List<StoreProductCatalogPrice>> GetCurrentByProductCatalogId(List<Guid> productCatalogsIds)
{
    var chunks = productCatalogsIds.ToChunks(500);
    return chunks.Select(chunk => DbSet.Where(c => chunk.Contains(c.StoreProductCatalogId))).SelectMany(x => x).ToList();
}

This extension method breaks a single IEnumerable<T> in smaller ones according the quantity you pass by parameter. This method was posted also by @GertArnold (thanks again) and can be found here

public static IEnumerable<IEnumerable<T>> ToChunks<T>(this IEnumerable<T> enumerable, int chunkSize)
{
    int itemsReturned = 0;
    var list = enumerable.ToList(); // Prevent multiple execution of IEnumerable.
    int count = list.Count;
    while (itemsReturned < count)
    {
        int currentChunkSize = Math.Min(chunkSize, count - itemsReturned);
        yield return list.GetRange(itemsReturned, currentChunkSize);
        itemsReturned += currentChunkSize;
    }
}

Upvotes: 10

Related Questions