Entity Framework Core Code-First（MySQL）统计表与记录数相关问题咨询

以下为EF Core 3.1.20 Code-First模式对接MySQL场景下的对应解决方案，默认你已完成DbContext配置与数据库连接初始化：

1. 查询指定数据库内现有数据表总数量 #

直接通过DbContext执行原生SQL查询MySQL系统表information_schema.TABLES获取结果：

using var context = new YourCustomDbContext();
var dbConnection = context.Database.GetDbConnection();
await dbConnection.OpenAsync();

using var countCommand = dbConnection.CreateCommand();
countCommand.CommandText = @"
SELECT COUNT(*) 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = @targetDbName 
AND TABLE_TYPE = 'BASE TABLE'
";

// 替换参数值为你的实际数据库名称
var dbNameParam = countCommand.CreateParameter();
dbNameParam.ParameterName = "@targetDbName";
dbNameParam.Value = "你的数据库名称";
countCommand.Parameters.Add(dbNameParam);

var tableTotalCount = (long)await countCommand.ExecuteScalarAsync();

2. 统计指定数据库所有数据表的总记录条数 #

分为两种统计方式，按需选择：

快速估算统计（InnoDB引擎下为近似值，性能开销极低） #

using var context = new YourCustomDbContext();
var dbConnection = context.Database.GetDbConnection();
await dbConnection.OpenAsync();

using var sumCommand = dbConnection.CreateCommand();
sumCommand.CommandText = @"
SELECT IFNULL(SUM(TABLE_ROWS), 0)
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = @targetDbName 
AND TABLE_TYPE = 'BASE TABLE'
";

var dbNameParam = sumCommand.CreateParameter();
dbNameParam.ParameterName = "@targetDbName";
dbNameParam.Value = "你的数据库名称";
sumCommand.Parameters.Add(dbNameParam);

var approximateTotalRows = (long)await sumCommand.ExecuteScalarAsync();

精确统计（遍历所有表执行COUNT(*)，数据量大时性能开销较高） #

using var context = new YourCustomDbContext();
var dbConnection = context.Database.GetDbConnection();
await dbConnection.OpenAsync();

// 第一步：获取库内所有表名
using var getTableCommand = dbConnection.CreateCommand();
getTableCommand.CommandText = @"
SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = @targetDbName 
AND TABLE_TYPE = 'BASE TABLE'
";
var dbNameParam = getTableCommand.CreateParameter();
dbNameParam.ParameterName = "@targetDbName";
dbNameParam.Value = "你的数据库名称";
getTableCommand.Parameters.Add(dbNameParam);

var tableNameList = new List<string>();
using var tableReader = await getTableCommand.ExecuteReaderAsync();
while (await tableReader.ReadAsync())
{
    tableNameList.Add(tableReader.GetString(0));
}
await tableReader.DisposeAsync();

// 第二步：遍历所有表统计记录数求和
long exactTotalRows = 0;
foreach (var tableName in tableNameList)
{
    using var countCommand = dbConnection.CreateCommand();
    countCommand.CommandText = $"SELECT COUNT(*) FROM `{tableName}`";
    exactTotalRows += (long)await countCommand.ExecuteScalarAsync();
}

3. 获取指定单张数据表的总记录条数 #

强类型查询（Code-First模式下推荐，无需手写SQL） #

如果你要查询的表有对应的实体映射，直接调用EF Core原生方法即可：

using var context = new YourCustomDbContext();
// 替换YourEntity为对应表的实体类名
var tableRowCount = await context.Set<YourEntity>().CountAsync();

原生SQL查询（适用于无对应实体类的场景） #

using var context = new YourCustomDbContext();
// 替换为实际表名
var targetTableName = "要查询的表名";
var tableRowCount = await context.Database.ExecuteScalarAsync<long>($"SELECT COUNT(*) FROM `{targetTableName}`");

内容的提问来源于stack exchange，提问作者hansf14