如何循环通过实体框架核心中的dbcontext all dbset来获取计数? [英] How to loop through dbcontext all dbset in Entity Framework Core to get count?
本文介绍了如何循环通过实体框架核心中的dbcontext all 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屋!
查看全文