Migration tries to create existing table

[Tahirhan]

Dear @msallin , thank you for your work. I am trying to implement migration branch into my project. I have added a new column to my existing table and used "SqliteMigrateDatabaseToLatestVersion" as an initializer. But when I use the DbContext I get an error like this:

System.Data.SQLite.SQLiteException: 'SQL logic error table Blocks already exists'

As I understand, MigrationBuilder tries to create an existing table although it has no change. I have only added one column to the "Parts" table.

Here is my DbContext class:

namespace MyProject.Model.Context
{
    public class MyProjectLANDb : DbContext
    {
        public MyProjectLANDb() : base(new SQLiteConnection("Data Source=" + Properties.Settings.Default.LANDbPath + "\\MyProjectLAN.sqlite;Persist Security Info=False;"), contextOwnsConnection: true)
        {
            Configure();
        }

        private void Configure()
        {
            Configuration.ProxyCreationEnabled = true;
            Configuration.LazyLoadingEnabled = true;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            var sqliteConnectionInitializer = new SqliteMigrateDatabaseToLatestVersion<MyProjectLANDb, Migrations.Configuration>(modelBuilder, true);
            Database.SetInitializer(sqliteConnectionInitializer);
        }

        public DbSet<Block> Blocks { get; set; }
        public DbSet<Part> Parts { get; set; }
    }
}

And Migrations Configuration:

namespace MyProject.Model.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration<MyProjectLANDb>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;

            // This command alter the class to support Migration to SQLite. 
            SetSqlGenerator("System.Data.SQLite", new SqliteMigrationSqlGenerator());
        }

        protected override void Seed(MyProjectLANDb context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data.
        }
    }
}

SQLite.CodeFirst reference runtime version: v4.0.30319 .NET Framework version: 4.8 Entity Framework version: 6.4.4

What I am missing? I just want to make the database updated with added column, Thanks!

[msallin]

Hi

I dont remember a initializer named "SqliteMigrateDatabaseToLatestVersion". Can you point me to it?

[Tahirhan]

You can see it under branch #4 -> SQLite.CodeFirst/Public/DbInitializers/SqliteMigrateDatabaseToLatestVersion.cs

[msallin]

Ah, I understand. I did not write that code and I did not integrate it into the master branch, because I do not support it.

[Tahirhan]

hmm, ok. Thank you!

[digocesar]

Hi @Tahirhan. Your database has the __MigrationHistory table with the correct migration names in it? Your migration Up function is correct, adding only the new column?

[Tahirhan]

Hi @digocesar, Is __MigrationHistory table gets created automatically or should I have to create it manually with other Entities(tables)? As I check my database with db viewer there is no __MigrationHistory table in it.

[Tahirhan]

I guess I should run "Add-Migration" cmd with the conn string of the local database and it will create the migration files.

[digocesar]

That's right. In "Add-Migration" you should inform the connection string to a previously created database. Or EF will criate a migration file to create all tables again. https://github.com/msallin/SQLiteCodeFirst/tree/%234#hints:~:text=When%20creating%20a%20new%20Migrations%20change

[Tahirhan]

I am working on a plugin project for Rhinoceros, when I try to use Add-Migration command with connection string and connection provider name, I face with below error message;

Could not load file or assembly 'RhinoCommon, Version=6.29.20238.11500, Culture=neutral, PublicKeyToken=552281e97c755530' or one of its dependencies. The system cannot find the file specified.

I have added reference to RhinoCommon.dll 6.29, Also used NuGet to specify but both didn't work. Do you have any suggestions? Thanks

[digocesar]

No idea. If the project is compiling, it should work.

[Tahirhan]

Yes, project is compiling without any issues.

[Tahirhan]

Hi, I have managed to solve the problem by deleting all NuGet packages for Rhino assemblies and referencing them from local files manually. Migration files are created. But unfortunately, now I am facing another weird issue. When I am using DbContext, I get this error:

System.EntryPointNotFoundException: 'Unable to find an entry point named 'SI04b638e115f7beb4' in DLL 'SQLite.Interop.dll'.'

I guess it is also a problem with referencing, I am working on it.

[Tahirhan]

I also figured out the reason for the above error, I have different plugins that load at the same time and they were using different versions of "Syste.Data.SQLite". When I set the same version (v1.0.117) for all of them the problem is solved.

But again I am getting this error:

System.Data.SQLite.SQLiteException: 'SQL logic error table Blocks already exists'

I have checked the migration file and As I see it tries to create all tables again. I guess it didn't realize the difference. I have only added one column to an existing table. @digocesar Is there any example of adding only a column to an existing table? so maybe I can take it as a reference and do manual changes on the migration file? Thanks

[digocesar]

When running Add-Migration you reference a pre-existing database in the connection string, right? If this referenced database does not have the __MigrationHistory table then it will have no way of knowing which migrations have already been executed, and will create a new migration to create everything. The question here is: Does your database have the __MigrationHistory table? If not, why not? Didn't you create the database using migration?

[Tahirhan]

I have created the database without using migration and I get your point. I have created a new database with migration and the initial migration (create tables) worked. But now I deleted columns from a table and created a new migration. This time I am getting this:

System.NotImplementedException: 'DropColumn is non-trivial and has not been implemented. See http://sqlite.org/lang_altertable.html'

MigrationBuilder created a DropColumn section in the file in order to delete the columns but it seems like it is not implemented?

Migration file for deleting not used columns:

namespace MyProject.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class DeleteNotUsedColumnsFromPartsTable : DbMigration
    {
        public override void Up()
        {
            DropColumn("dbo.Parts", "HasBendSchematic");
            DropColumn("dbo.Parts", "HasBendSchematic2");
        }

        public override void Down()
        {
            AddColumn("dbo.Parts", "HasBendSchematic2", c => c.Boolean(nullable: false));
            AddColumn("dbo.Parts", "HasBendSchematic", c => c.Boolean(nullable: false));
        }
    }
}

[digocesar]

Only recently after version 3.35.0 of Sqlite it became possible to do DROP COLUMN. And even then, Sqlite actually just hides the column, without physically removing it from the disk. More information: https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite

Maybe the componente should validade the Sqlite version in this statement generation.

But if you have the component code in your project you can try to implement this code in the drop column function: https://github.com/msallin/SQLiteCodeFirst/blob/3816796944c3a194890bdc3c8c358825560a77b9/SQLite.CodeFirst/Internal/Builder/MigrationBuilder.cs#L231

[Tahirhan]

Hmm, this solution can be implemented. Thanks @digocesar!