如何通过EF Core实现动态列的查询与插入操作？

问题1：除SP动态创建列外，EF Core的替代方案 #

• 原生DDL执行：直接通过EF Core执行ALTER TABLE语句添加列，无需依赖SP。利用context.Database.ExecuteSqlRaw或ExecuteSqlInterpolated方法，通过context.Database.ProviderName判断当前数据库类型，生成对应语法的DDL（SQL Server与MySQL的ALTER语法略有差异，可针对性处理）。
• JSON列存储动态字段：若动态字段频繁变动，推荐将其存储在JSON类型列中。EF Core支持主流数据库（SQL Server、MySQL）的JSON列映射，只需在实体类中定义字符串或字典类型属性映射到JSON列，无需修改表结构，跨数据库兼容性更强，配置表仅需定义JSON内的字段规则。
• 自定义迁移脚本：若动态列是一次性初始化需求，可自定义EF Core迁移操作，读取配置表信息生成对应AddColumn操作，执行迁移完成列创建。但该方式不适合频繁动态修改列的场景。

问题2：不借助SP，EF Core获取所有动态创建的列 #

可通过以下两种方式实现：

• 读取DbDataReader获取列与数据：执行原生SELECT查询，通过DbDataReader读取所有列名和对应数据，无需提前定义实体模型：

  var dataList = new List<Dictionary<string, object>>();
  using var reader = context.Database.ExecuteSqlRaw("SELECT * FROM TargetTable").Reader;
  while (reader.Read())
  {
      var row = new Dictionary<string, object>();
      for (int i = 0; i < reader.FieldCount; i++)
      {
          row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
      }
      dataList.Add(row);
  }
  

• 元数据API获取列信息：若EF Core模型已同步表结构，可通过模型元数据获取列名，但运行时动态新增的列不会自动同步到模型，仅适用于静态列场景：

  var entityType = context.Model.FindEntityType(typeof(TargetEntity));
  var columnNames = entityType.GetProperties().Select(p => p.GetColumnName()).ToList();
  

问题3：EF Core调用SP无需创建返回对象模型 #

直接通过DbDataReader读取SP返回结果，或转换为字典/动态对象：

• 读取DbDataReader处理结果：

  using var reader = context.Database.ExecuteSqlRaw("EXEC YourStoredProcedure @Param = {0}", paramValue).Reader;
  var result = new List<Dictionary<string, object>>();
  while (reader.Read())
  {
      var row = new Dictionary<string, object>();
      for (int i = 0; i < reader.FieldCount; i++)
      {
          row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
      }
      result.Add(row);
  }
  

• EF Core 7+ 使用SqlQuery返回字典：

  var result = context.Database.SqlQuery<Dictionary<string, object>>("EXEC YourStoredProcedure @Param").ToList();
  

• 转换为动态对象：利用ExpandoObject实现动态类型，方便业务使用：

  var dynamicResults = new List<dynamic>();
  using var reader = context.Database.ExecuteSqlRaw("EXEC YourStoredProcedure").Reader;
  var schema = reader.GetSchemaTable();
  while (reader.Read())
  {
      var expando = (IDictionary<string, object>)new ExpandoObject();
      foreach (DataRow schemaRow in schema.Rows)
      {
          string colName = schemaRow["ColumnName"].ToString();
          expando[colName] = reader.IsDBNull(reader.GetOrdinal(colName)) ? null : reader[colName];
      }
      dynamicResults.Add(expando);
  }
  

问题4：EF Core向动态列的表执行插入操作 #

• 原生SQL参数化插入：根据配置表的动态字段列表，拼接参数化INSERT语句，避免SQL注入：

  // 从配置表获取动态字段列表
  var dynamicColumns = GetDynamicColumnsFromConfig();
  var allColumns = new List<string> { "Id" }.Concat(dynamicColumns);
  var columnStr = string.Join(", ", allColumns);
  var paramStr = string.Join(", ", allColumns.Select(c => $"@{c}"));
  var sql = $"INSERT INTO TargetTable ({columnStr}) VALUES ({paramStr})";
  
  // 构造参数
  var parameters = new List<SqlParameter>
  {
      new SqlParameter("@Id", Guid.NewGuid())
  };
  parameters.AddRange(dynamicColumns.Select(c => new SqlParameter($"@{c}", GetDynamicFieldValue(c))));
  
  context.Database.ExecuteSqlRaw(sql, parameters.ToArray());
  

• 使用DbCommand手动插入：直接操作数据库连接和命令，灵活性更高：

  await context.Database.OpenConnectionAsync();
  using var command = context.Database.GetDbConnection().CreateCommand();
  command.CommandText = "INSERT INTO TargetTable (Id, DynamicCol1, DynamicCol2) VALUES (@Id, @DynamicCol1, @DynamicCol2)";
  command.Parameters.Add(new SqlParameter("@Id", Guid.NewGuid()));
  command.Parameters.Add(new SqlParameter("@DynamicCol1", value1));
  command.Parameters.Add(new SqlParameter("@DynamicCol2", value2));
  await command.ExecuteNonQueryAsync();
  

• JSON列插入（推荐）：若采用JSON列存储动态字段，直接序列化字典为JSON字符串赋值给实体属性，正常执行SaveChanges即可：

  var entity = new TargetEntity
  {
      Id = Guid.NewGuid(),
      DynamicFields = JsonSerializer.Serialize(new Dictionary<string, object>
      {
          { "DynamicCol1", value1 },
          { "DynamicCol2", value2 }
      })
  };
  context.TargetEntities.Add(entity);
  await context.SaveChangesAsync();
  

跨数据库管理SP的通用方式 #

由于SQL Server和MySQL的SP语法差异较大，可通过以下方式实现通用管理：

• 按数据库类型加载对应脚本：将不同数据库的SP创建脚本分别存储为资源文件（如SqlServer_SPs.sql、MySql_SPs.sql），初始化时根据context.Database.ProviderName判断当前数据库类型，读取对应脚本文件并批量执行。
• 业务逻辑上移至C#：尽量简化SP功能，仅保留基础数据操作，将复杂业务逻辑放在C#代码中实现，减少跨数据库的语法差异。
• 动态生成SP脚本：根据数据库类型动态生成符合语法的SP脚本，比如SQL Server与MySQL的CREATE PROCEDURE在参数定义、返回值处理上有差异，可通过条件判断生成对应内容。

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