c# - 如何解决 SQL 在代码优先迁移部署 Azure 期间找不到数据类型 BLOB

Question

我想将当前在 ASP.NET Core 上使用的 sqlite 数据库与 Azure SQL 进行交换。我生成了资源，sql server 项目中定义的所有内容。

当我想将应用程序发布到云中的azure和sql数据库的部署服务器时，它说无法找到数据类型BLOB。我在互联网上检查了解决方案，但没有工作。我猜我必须更新我的模型，但我不知道如何更新？

首先，我将 useSqlLiteServer 更改为 UseSqlServer

  services.AddDbContext<DataContext>(x => x.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

我还应该对下面列出的模型进行哪些更改，下面列出了我的一个模型，其他模型也类似

namespace HospitalAppointmentAPI.Models
{
    public class Patient
    {   
        public int Id { get; set; }
        public string IdentityNumber { get; set; }
        public string Name { get; set; } 
        public byte[] PasswordHash { get; set; }
        public byte[] PasswordSalt { get; set; }
        public DateTime BirthDate { get; set; }
        public string Email { get; set; }
        public ICollection<Appointment> Appointments { get; set; }

    }
}

DataContext.cs

 public class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> options) : base(options) {}
        public DbSet<Hospital> Hospitals { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Doctor> Doctors { get; set; }
        public DbSet<Patient> Patients { get; set; }
        public DbSet<Appointment> Appointments { get; set; }

    }

EF 迁移脚本生成期间的完整错误消息

C:\Program Files\dotnet\sdk\3.0.100\Sdks\Microsoft.NET.Sdk.Publish\targets\PublishTargets\Microsoft.NET.Sdk.Publish.MSDeploy.targets(140,5): Error : Web deployment task failed. (An error occurred during execution of the database script. The error occurred between the following lines of the script: "23" and "39". The verbose log might have more information about the error. The command started with the following: "IF NOT EXISTS(SELECT * FROM [__EFMigrationsHisto" Column, parameter, or variable #4: Cannot find data type BLOB. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE.) Failed to publish the database. This can happen if the remote database cannot run the script. Try modifying the database scripts, or disabling database publishing in the Package/Publish Web properties page. If the script failed due to database tables already exist, try dropping existing database objects before creating new ones. For more information on doing these options from Visual Studio, see http://go.microsoft.com/fwlink/?LinkId=179181. Error details: An error occurred during execution of the database script. The error occurred between the following lines of the script: "23" and "39". The verbose log might have more information about the error. The command started with the following: "IF NOT EXISTS(SELECT * FROM [__EFMigrationsHisto" Column, parameter, or variable #4: Cannot find data type BLOB. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE. Column, parameter, or variable #4: Cannot find data type BLOB. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout) Publish failed to deploy.

Answer 1

在Sqlite中，BLOB类型用于存储字节数组。在 SQL Server 中，支持类型 byte[]。

就您的情况而言，您首先使用 Sqlite，然后更改为使用 SQL Server。因此，您需要删除迁移脚本历史记录。

然后创建并应用 SQl Server 数据库的初始迁移:

dotnet ef migrations add init -o Data\Migrations
dotnet ef database update

然后表将在您的 SQL Server 数据库中创建。您的应用程序将准备好运行。