How to solve SQL Cannot find data type BLOB during code-first migration to deploy Azure

Question

I want to exchange the currently sqlite database that I'm using on ASP.NET Core with Azure SQL.I generated resource,sql server everything defined on project.

When I want to publish the app to deployment server of azure and sql database in cloud,it says Cannot find data type BLOB .I checked solutions on the internet but didn't work.I guess I have to update my model but I don't know in what way?

Here at first I've changed useSqlLiteServer to UseSqlServer

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

What else should I change on my models below listed my one model below others are similiar as well

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; }

    }

Full Error Message during EF migrations script generation

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

In Sqlite, type BLOB is used for storing byte array. In SQL Server, type byte[] is supported.

In your case, you used Sqlite at first, then you changed to use SQL Server. So, you need to remove the migration script history.

And then create and apply Initial Migration for SQl Server database:

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

And then tables will be created in your SQL Server database. And your application will be ready to run.