EF Core在动态SQL Server数据库中的适配可行性及实现方案咨询

Great question! The short answer is yes, you can use EF Core with dynamically generated tables and columns in SQL Server—but it’s not the framework’s primary use case, so you’ll need to leverage some workarounds and advanced features. Here’s a breakdown of how to make it work, plus alternatives if you need more flexibility:

Using EF Core for Dynamic Scenarios #

EF Core is built around static models, but these features let you adapt it to dynamic schemas:

• Keyless Entities: Define keyless types (with the [Keyless] attribute or HasNoKey() in your DbContext configuration) to map to tables that don’t have a primary key (common in dynamic scenarios). These are perfect for ad-hoc read operations.
• Raw SQL Queries: Use FromSqlRaw() or FromSqlInterpolated() to run direct SQL against dynamic tables. For example:

  var dynamicTableData = dbContext.DynamicResults
      .FromSqlInterpolated($"SELECT * FROM {dynamicTableName}")
      .ToList();
  

  Note: Always sanitize table/column names to avoid SQL injection risks!
• Dynamic LINQ: Use the System.Linq.Dynamic.Core NuGet package to build LINQ queries with dynamic column names, filters, and order-by clauses. This lets you avoid hardcoding property names for dynamic columns.
• Runtime Model Configuration: You can dynamically build your DbContext model at startup by querying the database’s schema (via INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS). For example:

  // Fetch schema info from SQL Server
  var tables = dbContext.Database.SqlQueryRaw<TableSchema>(
      "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
  ).ToList();
  
  foreach (var table in tables)
  {
      var entityBuilder = modelBuilder.Entity(table.TableName).HasNoKey();
      var columns = dbContext.Database.SqlQueryRaw<ColumnSchema>(
          $"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table.TableName}'"
      ).ToList();
      
      foreach (var column in columns)
      {
          entityBuilder.Property(column.ColumnName).HasColumnType(column.DataType);
      }
  }
  

  This lets your DbContext adapt to the current database schema on startup.

When EF Core Isn’t Enough: Alternative Libraries #

If you find EF Core’s static model constraints too restrictive, these libraries are designed specifically for dynamic database interactions:

• Dapper: A lightweight, fast ORM that plays nicely with dynamic objects. You can execute raw SQL and map results to dynamic, ExpandoObject, or anonymous types without pre-defined models. Example:

  using var connection = new SqlConnection(connectionString);
  var dynamicResults = connection.Query($"SELECT * FROM {dynamicTableName}").ToList<dynamic>();
  

• SqlKata: A fluent query builder that lets you construct complex SQL queries dynamically. It supports joins, filters, and schema-aware operations, and can execute queries and return dynamic results.
• ServiceStack.OrmLite: Offers dynamic model support with DynamicModel, allowing you to work with tables without pre-defined classes. It balances ORM conveniences with flexibility for dynamic schemas.

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