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

Question

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?

Answer 1

@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;
    }
}