如何循环通过实体框架核心中的dbcontext all dbset来获取计数？ [英] How to loop through dbcontext all dbset in Entity Framework Core to get count?

问题描述

我的上下文中有20个Dbset，我希望获得每个dbset的行数，以确保所有dbset的行数都是0。要获取一个dbset的计数，我的代码如下：

var person = context.Persons.Count();

有没有办法遍历上下文，动态获取每个dbset的计数？

推荐答案

有解决方案。使用方法很简单：

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

扩展返回IQueryable<TableInfo>，您可以稍后重用此查询。也许你需要用过滤发布观点，但我认为你可以处理好这一点。请注意，如果定义了全局查询筛选器，IgnoreQueryFilters可能很重要。

扩展功能：

它扫描Model以查找为特定DbContext注册的实体类型，并生成大量Concat(共Count个)查询。在这里，我们必须通过按常量值分组来实现此目的。

从原理上讲，它将生成以下LINQ查询：

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()}))
    ...

将转换为以下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
...

实现：

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

这篇关于如何循环通过实体框架核心中的dbcontext all dbset来获取计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！