Not able to execute SQL commands from Migration Class

[jintomenachery]

I am trying to execute some SQL commands using Sql() method from Up(). But, getting exception dialog when doing that, see the log window content below,

---------------------------
Assertion Failed: Abort=Quit, Retry=Debug, Ignore=Continue
---------------------------

   at System.Data.SQLite.EF6.Migrations.SQLiteMigrationSqlGenerator.GenerateSqlStatementConcrete(MigrationOperation migrationOperation)

   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)

   at System.Data.SQLite.EF6.Migrations.SQLiteMigrationSqlGenerator.GenerateSqlStatement(MigrationOperation migrationOperation)

   at System.Data.SQLite.EF6.Migrations.SQLiteMigrationSqlGenerator.GenerateStatement(MigrationOperation migrationOperation)

   at System.Data.SQLite.EF6.Migrations.SQLiteMigrationSqlGenerator.Generate(IEnumerable`1 migrationOperations, String providerManifestToken)

   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)

   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)

   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnume......

<truncated>
---------------------------
Abort   Retry   Ignore   
---------------------------

Since DropColumn is not supported, I was trying to achieve same using plain SQL command. Sample code listed below.

Sql(@"
                CREATE TABLE Tags_temp AS SELECT * FROM Tags;
                DROP TABLE Tags;
                ");

Setup info,

  <package id="EntityFramework" version="6.1.3" targetFramework="net461" />
  <package id="System.Data.SQLite" version="1.0.108.0" targetFramework="net461" />

[otherdave]

@jintomenachery did you ever figure this out? I'm getting similar behavior and I'm not sure what I'm doing wrong.

[jintomenachery]

@otherdave Nope, I couldn't resolve this :-(

[jojopoco]

I wrote a class extending SQLiteMigrationSqlGenerator for implementing migrations with drop column instructions. I'm posting it here in case it helps.

`public class CustomSQLiteMigrationSqlGenerator : SQLiteMigrationSqlGenerator { const string BATCHTERMINATOR = ";\r\n";

    /// <summary>
    /// Convierte una serie de operaciones de migración a sentencias SQL
    /// </summary>
    /// <param name="migrationOperations">Las operaciones a convertir.</param>
    /// <param name="providerManifestToken">Token que representa la versión de la base de datos.</param>
    /// <returns>
    /// Una lista de sentencias SQL a ejecutar para realizar la migración.
    /// </returns>
    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        List<MigrationStatement> migrationStatements = new List<MigrationStatement>();

        foreach (MigrationOperation migrationOperation in migrationOperations)
        {
            if (migrationOperation.GetType() == typeof(DropColumnOperation))
                migrationStatements.Add(GenerateStatement(migrationOperation));
            else
            {
                List<MigrationOperation> singleMigration = new List<MigrationOperation>();
                singleMigration.Add(migrationOperation);
                IEnumerable<MigrationStatement> migrationStatementsAux = new List<MigrationStatement>();
                migrationStatementsAux = base.Generate(singleMigration, providerManifestToken);
                migrationStatements.AddRange(migrationStatementsAux);
            }
        }
        return migrationStatements;
    }

    private MigrationStatement GenerateStatement(MigrationOperation migrationOperation)
    {
        MigrationStatement migrationStatement = new MigrationStatement();
        migrationStatement.BatchTerminator = BATCHTERMINATOR;
        migrationStatement.Sql = GenerateSqlStatementConcrete((DropColumnOperation)migrationOperation);
        return migrationStatement;
    }

    /// <summary>
    /// Realiza los cambios necesarios en bd para una migración de tipo DROP COLUMN (SQLite no soporta esta operación)
    /// </summary>
    /// <param name="migrationOperation">La migración a realizar</param>
    /// <returns></returns>
    private string GenerateSqlStatementConcrete(DropColumnOperation migrationOperation)
    {
        List<string> columnas = new List<string>();

        //Obtenemos el nombre de la tabla y la columna
        string nombreTablaVieja = migrationOperation.Table.ToLower().StartsWith("dbo.") ? migrationOperation.Table.Substring(4) : migrationOperation.Table;
        string nombreTabla = nombreTablaVieja;
        nombreTablaVieja = nombreTablaVieja + "_old";

        nombreTablaVieja = "\"" + nombreTablaVieja.Replace("\"", "\"\"") + "\"";
        nombreTabla = "\"" + nombreTabla.Replace("\"", "\"\"") + "\"";

        string nombreColumna = migrationOperation.Name.ToLower().StartsWith("dbo.") ? migrationOperation.Name.Substring(4) : migrationOperation.Name;
        nombreColumna = "\"" + nombreColumna.Replace("\"", "\"\"") + "\"";

        String cmd = "pragma table_info(" + nombreTabla + ");";

        string connectionString = ConfigurationManager.ConnectionStrings["MyDbContext"].ConnectionString;

        using (SQLiteConnection db = new SQLiteConnection(connectionString))
        {
            db.Open();
            using (var transaction = db.BeginTransaction())
            {
                using (SQLiteCommand comando = db.CreateCommand())
                {
                    //Obtenemos el nombre de las columnas excepto la que queremos borrar
                    comando.CommandText = cmd;
                    SQLiteDataReader reader = comando.ExecuteReader();

                    while (reader.Read())
                    {
                        if (reader["name"].ToString() != migrationOperation.Name)
                            columnas.Add(reader["name"].ToString());
                    }
                    reader.Close();

                    //Obtenemos la sentencia para crear la tabla
                    cmd = "SELECT sql FROM `sqlite_master` WHERE `type` = 'table' AND `name` = " + nombreTabla;

                    comando.CommandText = cmd;
                    reader = comando.ExecuteReader();

                    string crearTabla = "";
                    if (reader.Read())
                        crearTabla = reader["sql"].ToString();

                    reader.Close();

                    //Quitamos la columna de la query de creacion de la tabla nueva
                    string oldColumnsSql = crearTabla.Substring(crearTabla.IndexOf("(") + 1, crearTabla.LastIndexOf(")") - crearTabla.IndexOf("("));
                    List<string> columnasCreate = new List<string>();
                    columnasCreate = oldColumnsSql.Split(',').ToList();

                    columnasCreate = columnasCreate.Where(w => !w.Contains(nombreColumna)).ToList();

                    oldColumnsSql = string.Join(",", columnasCreate);

                    string sqlCrear = crearTabla.Substring(0, crearTabla.IndexOf("(") + 1) + oldColumnsSql + ")";

                    //Renombramos la tabla antigua
                    cmd = "ALTER TABLE " + nombreTabla + " RENAME TO " + nombreTablaVieja;
                    comando.CommandText = cmd;
                    comando.ExecuteNonQuery();

                    //Creamos la tabla nueva
                    comando.CommandText = sqlCrear;
                    comando.ExecuteNonQuery();

                    //Insertamos los datos de la tabla vieja en la nueva
                    cmd = "INSERT INTO " + nombreTabla + "(" + string.Join(",", columnas) + ") SELECT " + string.Join(",", columnas) + " FROM " + nombreTablaVieja;
                    comando.CommandText = cmd;
                    comando.ExecuteNonQuery();

                    //Borramos la tabla vieja
                    cmd = "DROP TABLE " + nombreTablaVieja;
                    comando.CommandText = cmd;
                    comando.ExecuteNonQuery();
                }

                //Si todo ha ido bien hacemos el commit de la transacción. Ante cualquier error o excepción SQLite hace el rollback de forma automática
                transaction.Commit();
            }

            db.Close();
        }
        return "";
    }
}`

You will need to replace SetSqlGenerator("System.Data.SQLite", new SQLiteMigrationSqlGenerator()); with SetSqlGenerator("System.Data.SQLite", new CustomSQLiteMigrationSqlGenerator()); in your ContextMigrationConfiguration class.

I also use the connection string declared in my app.config. string connectionString = ConfigurationManager.ConnectionStrings["MyDbContext"].ConnectionString;

The connection string in my app.config: `

`

[bubibubi]

Thanks for sharing the code. I'm not planning to include it in mainstream because of the "unsafe" statements required to do it.