如何用EF Core在数据库中持久化动态创建的数据结构？

针对你遇到的动态实体实例存储问题，这里提供几个无需设计时定义实体类、可运行时读写数据库的方案，尽量减少手动SQL编写：

1. ADO.NET + 动态键值对（推荐，简单直接） #

利用EF Core上下文获取数据库连接，结合SqlDataReader和参数化命令实现通用读写逻辑，无需提前定义实体类，同时避免SQL注入风险。

读取动态表数据 #

封装通用方法读取任意表内容，将每行数据转为Dictionary<string, object>：

public async Task<List<Dictionary<string, object>>> ReadDynamicTableAsync(DbContext context, string tableName)
{
    // 先验证表是否存在，避免非法注入
    var tableExists = await context.Database.ExecuteScalarAsync<int>(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName",
        new SqlParameter("@TableName", tableName)
    ) > 0;

    if (!tableExists)
        throw new InvalidOperationException($"表 {tableName} 不存在");

    var result = new List<Dictionary<string, object>>();
    using var connection = context.Database.GetDbConnection();
    await connection.OpenAsync();
    using var command = connection.CreateCommand();
    command.CommandText = $"SELECT * FROM {tableName}";
    
    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var row = new Dictionary<string, object>();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            var columnName = reader.GetName(i);
            row[columnName] = reader.IsDBNull(i) ? null : reader.GetValue(i);
        }
        result.Add(row);
    }
    return result;
}

写入动态实体实例 #

封装通用方法将键值对数据插入指定表：

public async Task InsertDynamicEntityAsync(DbContext context, string tableName, Dictionary<string, object> entityData)
{
    // 验证表存在
    var tableExists = await context.Database.ExecuteScalarAsync<int>(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName",
        new SqlParameter("@TableName", tableName)
    ) > 0;

    if (!tableExists)
        throw new InvalidOperationException($"表 {tableName} 不存在");

    var columns = string.Join(", ", entityData.Keys);
    var parameterPlaceholders = string.Join(", ", entityData.Keys.Select((_, idx) => $"@p{idx}"));
    var parameters = entityData.Select((kv, idx) => 
            new SqlParameter($"@p{idx}", kv.Value ?? DBNull.Value)
        ).ToArray();

    using var connection = context.Database.GetDbConnection();
    await connection.OpenAsync();
    using var command = connection.CreateCommand();
    command.CommandText = $"INSERT INTO {tableName} ({columns}) VALUES ({parameterPlaceholders})";
    command.Parameters.AddRange(parameters);
    
    await command.ExecuteNonQueryAsync();
}

调用示例：

// 插入Project实例
var projectData = new Dictionary<string, object>
{
    { "Name", "My Project" },
    { "Deadline", DateTime.Parse("16-02-2024") },
    { "Members", "Alice, Bob" }
};
await InsertDynamicEntityAsync(yourDbContext, "Project", projectData);

// 读取所有Project实例
var projects = await ReadDynamicTableAsync(yourDbContext, "Project");

2. EF Core运行时动态构建模型 #

如果想保留EF Core的上下文跟踪、SaveChanges等特性，可以在运行时动态构建实体模型，创建临时DbContext操作数据：

// 根据用户定义的Project结构动态构建模型
var modelBuilder = new ModelBuilder();
modelBuilder.Entity("Project", entity =>
{
    entity.Property<string>("Name").IsRequired();
    entity.Property<DateTime>("Deadline");
    entity.Property<string>("Members");
    entity.HasKey("Id"); // 假设表有自增主键Id
});

var dynamicModel = modelBuilder.Model;
// 创建临时DbContext
using var dynamicContext = new DbContext(new DbContextOptionsBuilder()
    .UseSqlServer(yourConnectionString)
    .UseModel(dynamicModel)
    .Options);

// 插入实例
var project = new ExpandoObject();
((IDictionary<string, object>)project)["Name"] = "My Project";
((IDictionary<string, object>)project)["Deadline"] = DateTime.Parse("16-02-2024");

dynamicContext.Set<dynamic>("Project").Add(project);
await dynamicContext.SaveChangesAsync();

// 查询实例
var projectList = dynamicContext.Set<dynamic>("Project").ToList();

这种方案适合需要利用EF Core高级特性的场景，但需要根据用户动态定义的结构实时构建模型，代码复杂度稍高。

3. EF Core原生SQL配合动态对象 #

借助EF Core的FromSqlRaw和System.Linq.Dynamic.Core扩展包，实现动态查询：

首先安装NuGet包：System.Linq.Dynamic.Core

然后可以这样操作：

// 查询Project表
var projects = yourDbContext.Set<dynamic>()
    .FromSqlRaw("SELECT * FROM Project")
    .ToList();

// 插入数据（自动参数化，避免注入）
await yourDbContext.Database.ExecuteSqlInterpolatedAsync(
    $"INSERT INTO Project (Name, Deadline, Members) VALUES ({projectData["Name"]}, {projectData["Deadline"]}, {projectData["Members"]})"
);

注意：查询结果为dynamic类型，需要自行转换为键值对使用。

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