How to loop through dbcontext all dbset in Entity Framework Core to get count?

Question

I have 20 Dbsets in my context which I want to get the row count for each dbset to make sure all dbset row count is 0. To get the count for one dbset, this is my code:

var person = context.Persons.Count();

Is there a way to loop through the context, get the count for each dbset dynamically?

Easy. But I think just get count is not enough. How do you plan to use this information? — Svyatoslav Danyliv, Feb 09 '21 at 05:43
Just to verify to make sure all table count is 0 before continue to some data import process. @SvyatoslavDanyliv — Steve, Feb 09 '21 at 05:53

Svyatoslav Danyliv · Accepted Answer · 2021-11-18T10:07:10.677

There is solution. Usage is simple:

var tablesinfo = ctx.GetTablesInfo();
if (tablesinfo != null)
{
    var withRecords = tablesinfo
        .IgnoreQueryFilters()
        .Where(ti => ti.RecordCount > 0)
        .ToArray();
}

Extension returns IQueryable<TableInfo> and you can reuse this query later. Probably you will need to filter out Views, but I think you can handle that. Note that IgnoreQueryFilters can be important if you have Global Query Filters defined.

What extension do:

It scans Model for entity types registered for particular DbContext and generates big Concat of Count queries. Here we have to do that via grouping by constant value.

Schematically it will generate the following LINQ query:

var tablesinfo = 
            ctx.Set<Entity1>.GroupBy(e => 1).Select(g => new TableInfo { TableName = "Entity1", RecordCount = g.Count()})
    .Concat(ctx.Set<Entity2>.GroupBy(e => 1).Select(g => new TableInfo { TableName = "Entity2", RecordCount = g.Count()}))
    .Concat(ctx.Set<Entity3>.GroupBy(e => 1).Select(g => new TableInfo { TableName = "Entity3", RecordCount = g.Count()}))
    ...

Which wll be converted to the following SQL:


SELECT "Entity1" AS TableName, COUNT(*) AS RecordCount FROM Entity1
UNION ALL
SELECT "Entity2" AS TableName, COUNT(*) AS RecordCount FROM Entity2
UNION ALL
SELECT "Entity3" AS TableName, COUNT(*) AS RecordCount FROM Entity3
...

Implementation:

public static class QueryableExtensions
{
    public class TableInfo
    {
        public string TableName { get; set; } = null!;
        public int RecordCount { get; set; }
    }

    public static IQueryable<TableInfo> GetTablesInfo(this DbContext ctx)
    {
        Expression query = null;
        IQueryProvider provider = null;

        var ctxConst = Expression.Constant(ctx);
        var groupingKey = Expression.Constant(1);

        // gathering information for MemberInit creation 
        var newExpression = Expression.New(typeof(TableInfo).GetConstructor(Type.EmptyTypes));
        var tableNameProperty = typeof(TableInfo).GetProperty(nameof(TableInfo.TableName));
        var recordCountProperty = typeof(TableInfo).GetProperty(nameof(TableInfo.RecordCount));

        foreach (var entityType in ctx.Model.GetEntityTypes())
        {
            var entityParam = Expression.Parameter(entityType.ClrType, "e");
            var tableName = entityType.GetTableName();

            // ctx.Set<entityType>()
            var setQuery = Expression.Call(ctxConst, nameof(DbContext.Set), new[] {entityType.ClrType});

            // here we initialize IQueryProvider, which is needed for creating final query
            provider ??= ((IQueryable) Expression.Lambda(setQuery).Compile().DynamicInvoke()).Provider;

            // grouping paraneter has generic type, we have to specify it
            var groupingParameter = Expression.Parameter(typeof(IGrouping<,>).MakeGenericType(typeof(int), entityParam.Type), "g");

            // g => new TableInfo { TableName = "tableName", RecordCount = g.Count() }
            var selector = Expression.MemberInit(newExpression, 
                Expression.Bind(tableNameProperty, Expression.Constant(tableName)),
                Expression.Bind(recordCountProperty,
                    Expression.Call(typeof(Enumerable), nameof(Enumerable.Count), new[] {entityParam.Type}, groupingParameter)));

            // ctx.Set<entityType>.GroupBy(e => 1)
            var groupByCall = Expression.Call(typeof(Queryable), nameof(Queryable.GroupBy), new[]
                {
                    entityParam.Type,
                    typeof(int)
                },
                setQuery,
                Expression.Lambda(groupingKey, entityParam)
            );

            // ctx.Set<entityType>.GroupBy(e => 1).Select(g => new TableInfo { TableName = "tableName",  RecordCount = g.Count()}))
            groupByCall = Expression.Call(typeof(Queryable), nameof(Queryable.Select),
                new[] {groupingParameter.Type, typeof(TableInfo)}, 
                groupByCall,
                Expression.Lambda(selector, groupingParameter));

            // generate Concat if needed
            if (query != null)
                query = Expression.Call(typeof(Queryable), nameof(Queryable.Concat), new[] {typeof(TableInfo)}, query,
                    groupByCall);
            else
                query = groupByCall;
        }

        // unusual situation, but Model can have no registered entities
        if (query == null)
            return null;

        return provider.CreateQuery<TableInfo>(query);
    }
}

I am using dependency injection and created my context at constructor for the context. So when I pass in _context, it says `cannot convert from 'xxx.Interfaces.IAbcContext' to 'Microsoft.EntityFrameworkCore.DbContext'` — Steve, Feb 09 '21 at 11:16
So you have created difficulties for yourself. Find a way to pass `DbContext` in proposed function. I know nothing about your interfaces and repositories. — Svyatoslav Danyliv, Feb 09 '21 at 11:28
For sure function can be rewritten to read properties. But this not original question. — Svyatoslav Danyliv, Feb 09 '21 at 11:32
downvoted because there are ZERO comments explaining what the GetTablesInfo method is doing. How are people supposed to learn if you don't explain it? — cdarrigo, Nov 13 '21 at 13:49

How to loop through dbcontext all dbset in Entity Framework Core to get count?

1 Answers1

Linked