System.InvalidOperationException while dynamically enumerating and retrieving .NET6 Entity Framework DbContext DbSet<...>s instances' records

Question

The subject runtime error's location is inside the following code. Please advise how to fix this issue.

using (var ctx = new MyDbContext())
{
    foreach (var entityInfo in ctx.GetEntitiesInfo())
        System.Console.WriteLine($"{entityInfo.Index}. EntityName = {entityInfo.Name}, Records count = {entityInfo.RecordCount}");
}

public static class Extensions
{
    public readonly record struct EntityInfo (int Index, string? Name, int RecordCount);

    public static IEnumerable<EntityInfo> GetEntitiesInfo(this MyDbContext context)
    {
        var dbSetProperties = context.GetDbSetProperties();
        var dbSets = dbSetProperties.Select(x => x.GetValue(context, null)).ToList();
        var index = 0;
        foreach (IQueryable? dbSet in dbSets)
        {
            ++index;
            // Runtime Error happens on next code line on second iteration
            //   when index = 2:
            //
            // System.InvalidOperationException:
            // 'There is already an open DataReader associated with
            //  this Connection which must be closed first
            //-
            dbSet.Load();
            var items = new List<object>();
            var enumerator = dbSet?.GetEnumerator();
            while (enumerator?.MoveNext() == true)
            {
                var item = enumerator.Current;
                items.Add(item);
            }

            yield return new EntityInfo(index, dbSet?.ElementType?.ToString(), items.Count);
        }
    }
    public static IEnumerable<PropertyInfo> GetDbSetProperties(this MyDbContext context)
    {
        foreach (var property in context.GetType().GetProperties())
            if (property?.PropertyType?.FullName?
                .Contains("Microsoft.EntityFrameworkCore.DbSet`") == true)
                     yield return property;
    }
}

[Update]

Actually I wanted to develop a generic code to get all DbSet(s) of a given DbContext. Here it's based on Ivan Stoev's answer to this topic:

public static IEnumerable<IQueryable<object>> GetDbSets(this MyDbContext context)
{
    var dbSetProperties = context.GetDbSetTypeProperties();
    return dbSetProperties.Select(x => x.GetValue(context, null))
              .Cast<IQueryable<object>>();
}
public static IEnumerable<PropertyInfo> GetDbSetTypeProperties(this MyDbContext context)
{
    foreach (var property in context.GetType().GetProperties())
        if (property?.PropertyType?.FullName?
            .Contains("Microsoft.EntityFrameworkCore.DbSet`") == true)
            yield return property;
}

Please advise if the above code can be even more simplified/streamlined.

[Update 2]

GetDbSets() method can be simplified this way:

public static IEnumerable<IQueryable<object>> GetDbSets(this MyDbContext context)
{
    return context
        .GetType()
        .GetRuntimeProperties()
        .Where(x => x.PropertyType?.FullName?.Contains("Microsoft.EntityFrameworkCore.DbSet`") == true)
        .Select(x => x.GetValue(context, null))
        .Cast<IQueryable<object>>();
}

but is there any other ways to enumerate all DbSet(s) of a given DbContext?

Answer 1

There are a lot of flaws in that code snippet, but the concrete problem in question is caused by the lack of disposal of the IEnumerator returned by the IEnumerable.GetEnumerator() call.

I know the non generic IEnumerator does not implement IDisposable, but that was a miss, and the generic IEnumerator<T> added later normally implements both IEnumerator and IDisposable.

So you have to either account for that and modify the code similar to this:

var items = new List<object>();
var enumerator = dbSet?.GetEnumerator();
try
{
    while (enumerator?.MoveNext() == true)
    {
        var item = enumerator.Current;
        items.Add(item);
    }
}
finally
{
    (enumerator as IDisposable)?.Dispose();
}

or better let the C# foreach do that for you (writing behind the scenes a code similar to the above):

var items = new List<object>();
foreach (var item in dbSet)
    items.Add(item);

Note that DbSet<T> type properties are the equivalent of Set<T>() method and are initialized by EF Core at the DbContext instance creation, so neither they nor their content is null, hence all the ?. operators are not needed.

Also calling the Load method is not needed since all it does is to enumerate the db set without storing items in a list (basically foreach with empty body).

Anyway, both previous code snippets will fix the original issue. But there is even better ways. Since all entities are required to be classes (reference types), each db set can be cast to IQueryable<object>

foreach (IQueryable<object> dbSet in dbSets)

which offers some additional benefits.

First, the original code could be fixed just by adding single using statement:

var items = new List<object>();
using var enumerator = dbSet?.GetEnumerator();
while (enumerator?.MoveNext() == true)
{
    var item = enumerator.Current;
    items.Add(item);
}

It can be replaced with foreach as before

var items = new List<object>();
foreach (var item in dbSet)
    items.Add(item);

but now you have access to many Enumerable (and Queryable and EF Core specific) extension methods, so you can replace all the iteration code with simple

var items = dbSet.ToList();

And in case you just need the record count, retrieve it with server side query without loading all the data in memory

var itemsCount = dbSet.Count();

yield return new EntityInfo(index, dbSet.ElementType.ToString(), itemsCount);