基于JSON构建动态类模型，实现EF Core多源API数据入库

针对多数据源JSON动态插入SQL的需求，核心要解决异构JSON到SQL实体的动态映射和EF Core的动态数据操作问题，以下是具体实现方案：

一、优化JSON解析与元数据提取 #

你当前的BindModelAsync已能将JSON转为ExpandoObject，可补充字段类型分析，为后续数据库操作提供依据：

public Task BindModelAsync(ModelBindingContext bindingContext)
{
    if (bindingContext == null)
        throw new ArgumentNullException(nameof(bindingContext));

    var valueProviderResult = bindingContext.ValueProvider.GetValue(bindingContext.ModelName);
    if (valueProviderResult == ValueProviderResult.None)
        return Task.CompletedTask;

    try
    {
        var stringValue = valueProviderResult.FirstValue;
        // 解析JSON并提取字段名、类型元数据
        using var doc = JsonDocument.Parse(stringValue);
        var root = doc.RootElement;
        var fieldTypes = new Dictionary<string, Type>();
        foreach (var property in root.EnumerateObject())
        {
            fieldTypes[property.Name] = property.Value.ValueKind switch
            {
                JsonValueKind.String => typeof(string),
                JsonValueKind.Number => property.Value.TryGetInt64(out _) ? typeof(long) : typeof(decimal),
                JsonValueKind.True or JsonValueKind.False => typeof(bool),
                JsonValueKind.Null => typeof(object),
                _ => typeof(string) // 复杂类型转为JSON字符串存储
            };
        }

        // 同时返回解析后的数据和字段元数据
        var result = JsonSerializer.Deserialize<ExpandoObject>(stringValue);
        bindingContext.Result = ModelBindingResult.Success(new { Data = result, FieldTypes = fieldTypes });
    }
    catch
    {
        bindingContext.ModelState.TryAddModelError(bindingContext.ModelName, "无效的JSON格式");
    }

    return Task.CompletedTask;
}

二、动态数据插入的两种实现方案 #

方案1：EF Core原生SQL参数化插入（推荐，简单高效） #

无需动态创建实体类，直接将ExpandoObject转为键值对，构建参数化INSERT语句，彻底避免SQL注入：

public async Task<int> InsertDynamicDataAsync(DbContext dbContext, string tableName, ExpandoObject data, Dictionary<string, Type> fieldTypes)
{
    var dataDict = data as IDictionary<string, object>;
    var columns = string.Join(", ", dataDict.Keys);
    var parameters = string.Join(", ", dataDict.Keys.Select(k => $"@{k}"));
    var sql = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";

    var sqlParameters = dataDict.Select(kv =>
    {
        var param = new SqlParameter($"@{kv.Key}", kv.Value ?? DBNull.Value);
        // 匹配SQL字段类型
        param.SqlDbType = fieldTypes[kv.Key] switch
        {
            Type t when t == typeof(string) => SqlDbType.NVarChar,
            Type t when t == typeof(long) => SqlDbType.BigInt,
            Type t when t == typeof(decimal) => SqlDbType.Decimal,
            Type t when t == typeof(bool) => SqlDbType.Bit,
            _ => SqlDbType.NVarChar
        };
        return param;
    }).ToArray();

    return await dbContext.Database.ExecuteSqlRawAsync(sql, sqlParameters);
}

方案2：动态创建实体类与EF Core DbSet映射 #

如果需要利用EF Core的变更追踪、迁移等特性，可通过反射动态生成实体类并注册到DbContext：

1. 动态生成实体类

public Type GenerateDynamicEntityType(string entityName, Dictionary<string, Type> fieldTypes)
{
    var assemblyName = new AssemblyName($"DynamicEntities_{Guid.NewGuid()}");
    var assemblyBuilder = AssemblyBuilder.DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run);
    var moduleBuilder = assemblyBuilder.DefineDynamicModule("DynamicModule");
    var typeBuilder = moduleBuilder.DefineType(entityName, TypeAttributes.Public | TypeAttributes.Class);

    // 添加主键字段（可根据业务调整规则）
    var idField = typeBuilder.DefineField("_id", typeof(int), FieldAttributes.Private);
    var idProperty = typeBuilder.DefineProperty("Id", PropertyAttributes.Public, typeof(int), null);
    idProperty.SetGetMethod(typeBuilder.DefineMethod("GetId", MethodAttributes.Public | MethodAttributes.HideBySig, typeof(int), Type.EmptyTypes)
        .Also(m => m.GetILGenerator().Emit(OpCodes.Ldarg_0).Emit(OpCodes.Ldfld, idField).Emit(OpCodes.Ret)));
    idProperty.SetSetMethod(typeBuilder.DefineMethod("SetId", MethodAttributes.Public | MethodAttributes.HideBySig, null, new[] { typeof(int) })
        .Also(m => m.GetILGenerator().Emit(OpCodes.Ldarg_0).Emit(OpCodes.Ldarg_1).Emit(OpCodes.Stfld, idField).Emit(OpCodes.Ret)));

    // 添加JSON解析得到的字段
    foreach (var (fieldName, fieldType) in fieldTypes)
    {
        var field = typeBuilder.DefineField($"_{fieldName.ToLower()}", fieldType, FieldAttributes.Private);
        var property = typeBuilder.DefineProperty(fieldName, PropertyAttributes.Public, fieldType, null);
        property.SetGetMethod(typeBuilder.DefineMethod($"Get{fieldName}", MethodAttributes.Public | MethodAttributes.HideBySig, fieldType, Type.EmptyTypes)
            .Also(m => m.GetILGenerator().Emit(OpCodes.Ldarg_0).Emit(OpCodes.Ldfld, field).Emit(OpCodes.Ret)));
        property.SetSetMethod(typeBuilder.DefineMethod($"Set{fieldName}", MethodAttributes.Public | MethodAttributes.HideBySig, null, new[] { fieldType })
            .Also(m => m.GetILGenerator().Emit(OpCodes.Ldarg_0).Emit(OpCodes.Ldarg_1).Emit(OpCodes.Stfld, field).Emit(OpCodes.Ret)));
    }

    return typeBuilder.CreateType()!;
}

2. 动态注册到DbContext并插入数据

public async Task InsertWithDynamicEntityAsync(DbContext dbContext, string tableName, ExpandoObject data, Dictionary<string, Type> fieldTypes)
{
    var entityType = GenerateDynamicEntityType(tableName, fieldTypes);
    // 实例化动态实体并赋值
    var entity = Activator.CreateInstance(entityType);
    var dataDict = data as IDictionary<string, object>;
    foreach (var (key, value) in dataDict)
    {
        entityType.GetProperty(key)?.SetValue(entity, value);
    }

    // 动态获取DbSet并添加数据
    var dbSetMethod = typeof(DbContext).GetMethod("Set", Type.EmptyTypes)!.MakeGenericMethod(entityType);
    var dbSet = dbSetMethod.Invoke(dbContext, null);
    var addMethod = dbSet!.GetType().GetMethod("Add")!;
    addMethod.Invoke(dbSet, new[] { entity });

    await dbContext.SaveChangesAsync();
}

三、关键注意事项 #

• SQL注入防护：必须使用参数化查询，禁止直接拼接字段值到SQL语句中
• 字段类型匹配：需根据JSON值类型与SQL表字段类型做对应映射，避免类型转换错误
• 表结构一致性：如果数据源JSON结构多变，可考虑动态创建表（生成CREATE TABLE语句），但需注意数据库权限与性能
• 性能考量：动态生成实体类会有一定性能开销，高并发场景优先选择方案1的原生SQL插入

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