如何在C# .NET Core 6中实现动态表映射BaseModel的查询接口？

1. 定义基础模型与实体类 #

首先明确BaseModel和对应数据库表的实体类，对应字段映射关系：

// 统一返回的基础模型
public class BaseModel
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

// 表A对应的实体（对应数据库"表A"）
public class TableA
{
    public int IdA { get; set; }
    public string CodeA { get; set; } = string.Empty;
    public string NameA { get; set; } = string.Empty;
}

// 表B对应的实体（对应数据库"表B"）
public class TableB
{
    public int IdB { get; set; }
    public string CodeB { get; set; } = string.Empty;
    public string NameB { get; set; } = string.Empty;
}

2. 配置DbContext #

注册数据库上下文，关联实体与数据库表：

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    // 声明对应表的DbSet
    public DbSet<TableA> TableA { get; set; }
    public DbSet<TableB> TableB { get; set; }

    // 映射实体到数据库表名（如果实体名与表名不一致）
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TableA>().ToTable("表A");
        modelBuilder.Entity<TableB>().ToTable("表B");
    }
}

3. 实现通用查询服务 #

创建服务处理动态表名查询、过滤和映射逻辑，这里使用Dynamic LINQ简化过滤条件解析（需安装NuGet包System.Linq.Dynamic.Core）：

// 服务接口
public interface IGenericDataService
{
    Task<List<BaseModel>> GetListAsync(string tableName, string? filter = null);
}

// 服务实现
public class GenericDataService : IGenericDataService
{
    private readonly AppDbContext _dbContext;
    // 表名与实体类型的映射配置
    private readonly Dictionary<string, Type> _tableEntityMap = new()
    {
        { "表A", typeof(TableA) },
        { "表B", typeof(TableB) }
    };

    public GenericDataService(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<List<BaseModel>> GetListAsync(string tableName, string? filter = null)
    {
        // 验证表名合法性
        if (!_tableEntityMap.TryGetValue(tableName, out var entityType))
        {
            throw new ArgumentException($"不存在指定表：{tableName}");
        }

        // 动态获取DbSet
        var dbSetMethod = typeof(DbContext).GetMethod(nameof(DbContext.Set))!.MakeGenericMethod(entityType);
        var dbSet = dbSetMethod.Invoke(_dbContext, null) as IQueryable;

        // 应用过滤条件（支持如"NameA.Contains('张三')"或"IdA > 10"的字符串表达式）
        if (!string.IsNullOrEmpty(filter))
        {
            dbSet = dbSet.Where(filter);
        }

        // 将实体映射到BaseModel并返回
        return await MapToBaseModelList(dbSet, entityType);
    }

    // 实体到BaseModel的映射逻辑（支持扩展更多表）
    private async Task<List<BaseModel>> MapToBaseModelList(IQueryable dbSet, Type entityType)
    {
        var entityList = await dbSet.ToListAsync();
        var baseModels = new List<BaseModel>();

        foreach (var entity in entityList)
        {
            var baseModel = new BaseModel();
            switch (entityType.Name)
            {
                case nameof(TableA):
                    var tableA = entity as TableA;
                    baseModel.Id = tableA!.IdA;
                    baseModel.Name = tableA.NameA;
                    break;
                case nameof(TableB):
                    var tableB = entity as TableB;
                    baseModel.Id = tableB!.IdB;
                    baseModel.Name = tableB.NameB;
                    break;
                // 新增表时在这里添加映射规则
            }
            baseModels.Add(baseModel);
        }

        return baseModels;
    }
}

4. 注册服务与创建API接口 #

在Program.cs中注册服务：

var builder = WebApplication.CreateBuilder(args);

// 注册DbContext（以SQL Server为例）
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// 注册通用查询服务
builder.Services.AddScoped<IGenericDataService, GenericDataService>();

// 添加控制器支持
builder.Services.AddControllers();

var app = builder.Build();

app.MapControllers();
app.Run();

创建API控制器对外提供接口：

[ApiController]
[Route("api/data")]
public class DataController : ControllerBase
{
    private readonly IGenericDataService _dataService;

    public DataController(IGenericDataService dataService)
    {
        _dataService = dataService;
    }

    /// <summary>
    /// 获取指定表的过滤数据
    /// </summary>
    /// <param name="tableName">表名（如"表A"）</param>
    /// <param name="filter">过滤条件（如"NameA.Contains('张三')"）</param>
    [HttpGet("getlist")]
    public async Task<IActionResult> GetList([FromQuery] string tableName, [FromQuery] string? filter = null)
    {
        try
        {
            var result = await _dataService.GetListAsync(tableName, filter);
            return Ok(result);
        }
        catch (ArgumentException ex)
        {
            return BadRequest(ex.Message);
        }
        catch (Exception ex)
        {
            return StatusCode(500, $"查询失败：{ex.Message}");
        }
    }
}

5. 调用示例 #

• 查询表A所有数据：GET /api/data/getlist?tableName=表A
• 查询表A中名称包含"张三"的数据：GET /api/data/getlist?tableName=表A&filter=NameA.Contains('张三')

扩展优化建议 #

如果需要支持更多表，可以通过特性标记+反射自动映射字段，避免手动编写switch逻辑：

1. 定义映射特性：

[AttributeUsage(AttributeTargets.Property)]
public class MapToBaseAttribute : Attribute
{
    public string TargetProperty { get; set; }
    public MapToBaseAttribute(string targetProperty) => TargetProperty = targetProperty;
}

2. 给实体属性标记特性：

public class TableA
{
    [MapToBase("Id")]
    public int IdA { get; set; }
    public string CodeA { get; set; } = string.Empty;
    [MapToBase("Name")]
    public string NameA { get; set; } = string.Empty;
}

3. 修改映射方法为自动反射映射：

private BaseModel AutoMapToBase(object entity)
{
    var baseModel = new BaseModel();
    var entityProps = entity.GetType().GetProperties();
    
    foreach (var prop in entityProps)
    {
        var attr = prop.GetCustomAttribute<MapToBaseAttribute>();
        if (attr == null) continue;
        
        var targetProp = typeof(BaseModel).GetProperty(attr.TargetProperty);
        targetProp?.SetValue(baseModel, prop.GetValue(entity));
    }
    
    return baseModel;
}

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