EF Core迁移SQL Server时Guid转String失败问题求助

错误信息 #

System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'.
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerStringTypeMapping.GenerateNonNullSqlLiteral(Object value)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.GenerateProviderValueSqlLiteral(Object value)
         at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendSqlLiteral(StringBuilder commandStringBuilder, IColumnModification modification, String tableName, String schema)
         at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.<>c.<AppendValues>b__24_0(StringBuilder sb, IColumnModification o, ValueTuple`3 p)
         at System.Text.StringBuilderExtensions.AppendJoin[T,TParam](StringBuilder stringBuilder, IEnumerable`1 values, TParam param, Action`3 joinAction, String separator)
         at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendValues(StringBuilder commandStringBuilder, String name, String schema, IReadOnlyList`1 operations)
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerUpdateSqlGenerator.AppendInsertMultipleRows(StringBuilder commandStringBuilder, IReadOnlyList`1 modificationCommands, List`1 writeOperations, Boolean& requiresTransaction)
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerUpdateSqlGenerator.AppendBulkInsertOperation(StringBuilder commandStringBuilder, IReadOnlyList`1 modificationCommands, Int32 commandPosition, Boolean& requiresTransaction)
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ApplyPendingBulkInsertCommands()
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.Complete(Boolean moreBatchesExpected)
         at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.CreateCommandBatches(IEnumerable`1 commandSet, Boolean moreCommandSets, Boolean assertColumnModification, ParameterNameGenerator parameterNameGenerator)+MoveNext()
         at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(InsertDataOperation operation, IModel model, MigrationCommandListBuilder builder, Boolean terminate)
         at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.<>c.<.cctor>b__82_28(MigrationsSqlGenerator g, MigrationOperation o, IModel m, MigrationCommandListBuilder b)
         at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
         at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
         at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
         at Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
         at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration, MigrationsSqlGenerationOptions options)
         at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c__DisplayClass16_2.<GetMigrationCommandLists>b__2()
         at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
         at RonBv.GameSite.Api.Data.DatabaseMigrator.ApplyMigrations(WebApplication app) in D:\a\1\s\...\Data\DatabaseMigrator.cs:line 25

问题原因 #

你的迁移代码是针对Sqlite生成的，却在SQL Server环境下执行，核心冲突点：

1. 所有Guid类型的列都硬指定了Sqlite专属的type: "TEXT"，但SQL Server中Guid对应的原生类型是uniqueidentifier。EF执行迁移插入Guid值时，会按照字符串类型处理，触发类型转换异常。
2. 迁移中包含Sqlite专属注解Annotation("Sqlite:Autoincrement", true)，该注解在SQL Server环境下无效，也会干扰EF的类型映射逻辑。
3. 其他类型（如bool用INTEGER、DateTimeOffset用TEXT）都是Sqlite的类型定义，与SQL Server的类型体系不兼容。

解决方案 #

方案1：重新生成适配SQL Server的迁移（推荐） #

1. 移除现有迁移文件：

   # CLI命令
   dotnet ef migrations remove
   # 或者Package Manager Console命令
   Remove-Migration
   

2. 确保项目的数据库连接字符串指向SQL Server，而非Sqlite。
3. 重新生成针对SQL Server的迁移：

   dotnet ef migrations add Initial
   # 或者
   Add-Migration Initial
   

4. 执行迁移：

   dotnet ef database update
   # 或者
   Update-Database
   

方案2：手动修改现有迁移文件 #

如果不想删除现有迁移，可直接修改迁移代码适配SQL Server：

• 将所有table.Column<Guid>(type: "TEXT", ...)替换为table.Column<Guid>(type: "uniqueidentifier", ...)
• 删除所有Annotation("Sqlite:Autoincrement", true)注解，SQL Server自增主键可通过Identity()实现
• 替换其他Sqlite类型为SQL Server对应类型：
  • INTEGER（bool）→ bit
  • INTEGER（int）→ int
  • TEXT（DateTimeOffset）→ datetimeoffset

预防措施 #

• 分库维护迁移：针对不同数据库（Sqlite、SQL Server）单独生成和维护迁移文件，不要跨数据库复用同一迁移。
• 生成迁移前确认目标数据库：生成迁移时，确保当前DbContext的连接字符串指向目标数据库，EF会自动生成对应数据库的原生类型映射。
• 避免硬编码数据库专属类型：在实体配置或迁移中，不要手动指定数据库专属的类型字符串，让EF根据数据库提供者自动完成类型映射。
• 多环境配置隔离：开发阶段可通过环境变量、配置文件等方式切换数据库连接，确保迁移生成和执行的环境一致。

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