如何让.NET API无需为每张表创建类即可动态适配任意数据库表？

针对你需要让.NET API无需手动创建实体类即可动态适配PostgreSQL中任意带GIS字段的表的需求，以下是两种可行的技术方案，解决你当前BaseEntity思路的核心问题：

核心问题分析 #

你之前的方案无法运行的原因是：EF Core是强类型ORM，要求每个数据库表对应一个明确的实体类型，而你的BaseEntity没有映射任何实际表字段，且反射逻辑只能识别编译时已存在的子类，无法处理运行时新增的数据库表。

方案一：EF Core动态生成实体类型 #

通过运行时读取数据库元数据、动态构建实体类并注册到DbContext，实现完全的动态映射：

1. 读取数据库表结构 #

先从PostgreSQL的information_schema中获取所有表和列的元数据：

private async Task<List<TableSchema>> GetTableSchemasAsync()
{
    var schemaQuery = @"
        SELECT 
            t.table_name,
            c.column_name,
            c.data_type,
            c.is_nullable,
            kcu.column_name AS primary_key
        FROM information_schema.tables t
        JOIN information_schema.columns c ON t.table_name = c.table_name
        LEFT JOIN information_schema.key_column_usage kcu 
            ON t.table_name = kcu.table_name 
            AND c.column_name = kcu.column_name 
            AND kcu.constraint_name LIKE '%_pkey'
        WHERE t.table_schema = 'public' 
          AND t.table_type = 'BASE TABLE'
        ORDER BY t.table_name, c.ordinal_position";

    var rawSchema = await Database.SqlQueryRaw<SchemaRow>(schemaQuery).ToListAsync();
    return rawSchema.GroupBy(r => r.table_name)
                    .Select(g => new TableSchema
                    {
                        TableName = g.Key,
                        Columns = g.Select(c => new ColumnSchema
                        {
                            ColumnName = c.column_name,
                            DataType = c.data_type,
                            IsNullable = c.is_nullable == "YES",
                            IsPrimaryKey = !string.IsNullOrEmpty(c.primary_key)
                        }).ToList()
                    }).ToList();
}

// 辅助数据类
public class SchemaRow
{
    public string table_name { get; set; }
    public string column_name { get; set; }
    public string data_type { get; set; }
    public string is_nullable { get; set; }
    public string primary_key { get; set; }
}

public class TableSchema
{
    public string TableName { get; set; }
    public List<ColumnSchema> Columns { get; set; }
}

public class ColumnSchema
{
    public string ColumnName { get; set; }
    public string DataType { get; set; }
    public bool IsNullable { get; set; }
    public bool IsPrimaryKey { get; set; }
}

2. 动态生成实体类型 #

使用Reflection.Emit在运行时创建对应表结构的实体类，包含GIS字段映射：

private readonly Dictionary<string, Type> _cachedEntityTypes = new();

private Type GenerateDynamicEntity(TableSchema tableSchema)
{
    if (_cachedEntityTypes.TryGetValue(tableSchema.TableName, out var existingType))
        return existingType;

    var assemblyBuilder = AssemblyBuilder.DefineDynamicAssembly(
        new AssemblyName($"DynamicEntities_{tableSchema.TableName}"), 
        AssemblyBuilderAccess.Run);
    var moduleBuilder = assemblyBuilder.DefineDynamicModule("DynamicEntities");
    var typeBuilder = moduleBuilder.DefineType(
        tableSchema.TableName, 
        TypeAttributes.Public | TypeAttributes.Class);

    // 添加Table特性
    var tableAttr = typeof(TableAttribute).GetConstructor(new[] { typeof(string) });
    typeBuilder.SetCustomAttribute(new CustomAttributeBuilder(tableAttr, new object[] { tableSchema.TableName }));

    foreach (var column in tableSchema.Columns)
    {
        // 映射PostgreSQL类型到CLR类型
        var propertyType = GetClrType(column.DataType);
        if (column.IsNullable && propertyType.IsValueType)
            propertyType = typeof(Nullable<>).MakeGenericType(propertyType);

        // 创建属性和字段
        var field = typeBuilder.DefineField($"_{column.ColumnName.ToLower()}", propertyType, FieldAttributes.Private);
        var property = typeBuilder.DefineProperty(column.ColumnName, PropertyAttributes.None, propertyType, null);

        // 实现getter/setter
        var getter = typeBuilder.DefineMethod($"get_{column.ColumnName}", 
            MethodAttributes.Public | MethodAttributes.SpecialName, propertyType, Type.EmptyTypes);
        var getterIl = getter.GetILGenerator();
        getterIl.Emit(OpCodes.Ldarg_0);
        getterIl.Emit(OpCodes.Ldfld, field);
        getterIl.Emit(OpCodes.Ret);

        var setter = typeBuilder.DefineMethod($"set_{column.ColumnName}", 
            MethodAttributes.Public | MethodAttributes.SpecialName, null, new[] { propertyType });
        var setterIl = setter.GetILGenerator();
        setterIl.Emit(OpCodes.Ldarg_0);
        setterIl.Emit(OpCodes.Ldarg_1);
        setterIl.Emit(OpCodes.Stfld, field);
        setterIl.Emit(OpCodes.Ret);

        // 添加Column特性
        var columnAttr = typeof(ColumnAttribute).GetConstructor(new[] { typeof(string) });
        property.SetCustomAttribute(new CustomAttributeBuilder(columnAttr, new object[] { column.ColumnName }));

        // 主键标记
        if (column.IsPrimaryKey)
        {
            var keyAttr = typeof(KeyAttribute).GetConstructor(Type.EmptyTypes);
            property.SetCustomAttribute(new CustomAttributeBuilder(keyAttr, Array.Empty<object>()));
        }

        // GIS字段特殊配置
        if (column.DataType.StartsWith("geometry"))
        {
            var typeNameAttr = typeof(ColumnAttribute).GetProperty(nameof(ColumnAttribute.TypeName));
            property.SetCustomAttribute(new CustomAttributeBuilder(
                columnAttr, 
                new object[] { column.ColumnName },
                new[] { typeNameAttr },
                new object[] { column.DataType }));
        }
    }

    var dynamicType = typeBuilder.CreateType();
    _cachedEntityTypes.Add(tableSchema.TableName, dynamicType);
    return dynamicType;
}

private Type GetClrType(string postgresType)
{
    return postgresType switch
    {
        "integer" => typeof(int),
        "double precision" => typeof(double),
        "character varying" => typeof(string),
        "geometry" => typeof(NetTopologySuite.Geometries.Geometry),
        "timestamp without time zone" => typeof(DateTime),
        _ => typeof(object)
    };
}

3. 在DbContext中注册动态实体 #

重写OnModelCreating方法，注册所有动态生成的实体并配置GIS支持：

protected override async void OnModelCreating(ModelBuilder modelBuilder)
{
    var tableSchemas = await GetTableSchemasAsync();
    foreach (var schema in tableSchemas)
    {
        var entityType = GenerateDynamicEntity(schema);
        var entityBuilder = modelBuilder.Entity(entityType);

        // 配置GIS字段的NetTopologySuite转换器
        var geometryCols = schema.Columns.Where(c => c.DataType.StartsWith("geometry"));
        foreach (var col in geometryCols)
        {
            entityBuilder.Property(col.ColumnName)
                         .HasConversion(new NetTopologySuite.IO.NpgsqlGeometryConverter());
        }
    }

    base.OnModelCreating(modelBuilder);
}

4. 动态查询方法 #

实现根据表名查询数据的方法：

public async Task<IEnumerable<object>> GetTableDataAsync(string tableName)
{
    var schema = (await GetTableSchemasAsync())
        .FirstOrDefault(s => s.TableName.Equals(tableName, StringComparison.OrdinalIgnoreCase));
    
    if (schema == null)
        throw new ArgumentException($"表 {tableName} 不存在");

    var entityType = GenerateDynamicEntity(schema);
    var setMethod = typeof(DbContext).GetMethod(nameof(DbContext.Set), Type.EmptyTypes)
                                     .MakeGenericMethod(entityType);
    var dbSet = (IQueryable)setMethod.Invoke(this, null);

    return await dbSet.ToListAsync();
}

方案二：Dapper+ADO.NET（轻量替代） #

如果不需要EF Core的ORM特性，直接用Dapper处理动态查询更简单，同时支持GIS字段：

public async Task<IEnumerable<dynamic>> GetTableDataWithDapperAsync(string tableName)
{
    using var connection = Database.GetDbConnection();
    await connection.OpenAsync();

    var reader = await connection.ExecuteReaderAsync($"SELECT * FROM {tableName}");
    var result = new List<dynamic>();

    while (await reader.ReadAsync())
    {
        var row = new ExpandoObject() as IDictionary<string, object>;
        for (int i = 0; i < reader.FieldCount; i++)
        {
            var colName = reader.GetName(i);
            if (reader.IsDBNull(i))
            {
                row[colName] = null;
                continue;
            }

            // 转换GIS字段为NetTopologySuite对象
            if (reader.GetFieldType(i) == typeof(NpgsqlTypes.NpgsqlGeometry))
            {
                var npgsqlGeom = (NpgsqlTypes.NpgsqlGeometry)reader.GetValue(i);
                row[colName] = npgsqlGeom.Geometry;
            }
            else
            {
                row[colName] = reader.GetValue(i);
            }
        }
        result.Add(row);
    }

    return result;
}

注意事项 #

1. 缓存优化：动态生成的实体类型建议缓存，避免重复创建
2. GIS配置：确保DbContext已启用UseNetTopologySuite()
3. 权限控制：数据库用户需要有读取information_schema的权限
4. 性能：EF Core动态实体的查询性能与编译时实体接近，Dapper在动态场景下更高效
5. 复杂查询：如需过滤、排序，可通过构建ExpressionTree或直接编写原生SQL实现

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