Feature suggestion: Migrations

[code1line]

What do you think about migrations-support?

[msallin]

Hi Michael

Of course migrations support would be great.
I have not much Experience with entity framework, but as fare as I know the EF Migrations mechanismus consists of two parts.

1. MigrationSqlGenerator
   This class must be implemented. It will generate the SQL statements based on the commands. Which means this class needs to know how to tread a CreateTable() which is used in a migration.
2. MigrateDatabaseToLatestVersion<TContext, TMigrationsConfiguration> Class
   Normally you would use this initializer to use migrations. But as the code first stuff is done in a own initializer, this class could not be used. That means a) a own migrations class needs to be written or b) we find out what exactly the MigrateDatabaseToLatestVersionclass does and then just implement the necessary stuff (like create the History Table) so that the functionality of the MigrateDatabaseToLatestVersionclass could be reused.

It seems very complicated to me and may there is another way to do this.

[code1line]

I agree - it would be great, but it does not seem to be an easy task. It may be planned for a future release...

[GaryWolfe]

Migrations would be ideal, as that seems to be how EF is typically presented with Code First.

Is there some documentation on how to use EF with Code First but not with migrations? Everything I find seems to discuss migrations (which requires the above-mentioned sql generator class). When I try running "update-database", it asks for the sql generator. I'm sorry if this isn't the right forum for this question, but how do I use this project and SQLite without migrations?

I have this working as code-first (with migration) in SQL Server, but wanted to try SQLite, as the db is small and SQL Server is overkill.

[msallin]

Hi Gary

1. You can use EF without migrations. Have a look at the Code in this Repo, there is a sample Application.
2. You can not run "update-database" because there is no implementation for the SQL Generator.
3. See 1. Just delete your migrations may be an option, because the Model (Code) should be up to date.

I once tried to dive into EF and find out how the migrations are implemented.
But I was not able to create a own sql generator.

In the (Issue 16)[https://github.com//issues/16] HareIM posted a link with an implementation for such an sql generator. May you can try to use this: https://drive.google.com/file/d/0B-NCqWnMQpBrQjkxMkxnVlRzb00/view?pli=1

[GaryWolfe]

Thanks for the fast response, Marc! I think I can probably live without the migrations with my small project, but the Sql Generator sounds intriguing. I'll have a look at that and the sample code you mention as time permits.

Working on this last night, I kept getting a zero-byte SQLite database generated, so I must be doing something wrong; it did not re-build a freshly seeded db as I would have expected. Since I have been using SQL Server, it seems to hang on to that information somewhere. When I run, it creates a zero byte SQLite db, but then runs off of the SQL Server db! (I've commented-out the connection strings in the web.config for SQL Server, so not sure how it does that!) I like how EF automates things, but I guess it also hides what it's doing. (In other work, I use DB-first, and I think that's best for larger projects. I've had success working with that, so I've been tempted to go that route.) If I continue to be stuck, I'll just start over with a "fresh" project and copy files over, to ensure that it only uses SQLite.

Thanks for clarifying things.

[HarelM]

+1

[tg73]

I've got a working prototype for migrations. It was originally based on the prototype put together by Julio C. Borges in 2013, although significant further work was required. Several migration operations are currently not implemented, notably those ALTER TABLE operations which are not natively supported by SQLite (see https://www.sqlite.org/lang_altertable.html).

The code is not as clean or well-formatted as I'd like for issuing a pull request, so for the time being you can get it here:

https://github.com/zanyants/SQLiteCodeFirst/tree/feature/migrations

To use:

To your migrations Configuration class ctor, add:

SetSqlGenerator( "System.Data.SQLite", new SQLite.CodeFirst.SqliteMigrationSqlGenerator() );

And in your context's OnModelCreating add:

Database.SetInitializer( new System.Data.Entity.MigrateDatabaseToLatestVersion<MyContext, Migrations.Configuration>( true ) );

[msallin]

Hello Tom

Thanks for your work!
Currently I'm very busy and have not the chance to look through it in detail.
The formatting should not be the problem, R# will to the hard work ;)
It needs some refactoring but I'm glad to work together with you on this code.

I created a branch https://github.com/msallin/SQLiteCodeFirst/tree/Issue-4 you can create a PR to push your changes in this branch and then we can work on it.

Greetings Marc

[tg73]

Hi Marc,

I'm also rather busy - I should have time to do as you ask early next week.

Tom

[CADbloke]

If it is any consolation, EF7 hasn't gotten very far with this ... https://github.com/aspnet/EntityFramework/blob/dev/src/Microsoft.EntityFrameworkCore.Sqlite/Migrations/SqliteMigrationsSqlGenerator.cs#L149
http://ef.readthedocs.org/en/latest/providers/sqlite/limitations.html#migrations-limitations

[msallin]

@CADbloke thanks for the hint!
@tg73 What do you think, could we benefit from that?

[CADbloke]

More (actually, less progress but more discussion) from the EF team not getting far with SQLite migrations. This issue has links to a lot of other related issues in the EF7 project. It seems to be the main issue they are tracking on the matter.

dotnet/efcore#329

[HarelM]

Any updates on this? I think it's almost the only open issue left... :-)

[HarelM]

I was wondering how the fact that SQLite is supported using .Net Core and EF.Core would affect this issue. see here: https://docs.microsoft.com/en-us/ef/core/get-started/netcore/new-db-sqlite

[msallin]

I while ago I looked through the code (as its OSS :)) but I noticed thats not just "take and use".
There is still the started work from @tg73 (https://github.com/zanyants/SQLiteCodeFirst/tree/feature/migrations).

I think this is a lot of work to do and I'm currently not planning to work on it by my self.
I'm looking forward to invest some more time in spare time projects after I graduated as BSc. Still one year to go and I need my free time for the projects there.

[digocesar]

I'm using @tg73 work and is working fine, with some adjusts on index names.

[tg73]

Apologies for my slow reply. The migrations work I did was done as part of my employed work. It was a limited project and I've since moved on to completely unrelated work, and unfortunately have no time allocated for further SQLite/migrations work. I was keen to get my original work contributed back to the community in case it was of use, and I hope it can be taken further. I seem to remember getting to a point where the limitations of SQLite required quite complex workarounds, possibly requiring a lexer/parser of SQLite flavour SQL to properly support some part of the migrations workflow.

…

On 1 March 2017 at 11:37, digocesar ***@***.***> wrote: I'm using @tg73 <https://github.com/tg73> work and is working fine, with some adjusts on index names. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#4 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABDPfRb5Brn_1t7d3cKM9r22Bv0KvIzNks5rhViSgaJpZM4D1sY-> .

[msallin]

Maybe worth to have a look:
https://github.com/ericschultz/SQLiteParser

[theShiva]

Hi @msallin

Excellent work on this Library! Love it!! I just tried to do an Add-Migration ... and realized that Migrations is not supported by this Library.

What's the point of using this for Code-First, if migrations is not supported. In any practical real-life project, the database is not just created 1 time and forgotten. It will undergo many changes during it's lifetime.

Any chance anyone else will be able to take this on?

Thank you!
-Shiva

[CADbloke]

The ASP.NET team are still onto this: dotnet/efcore#7969 as well as the previously mentioned dotnet/efcore#329

I have been thoroughly distracted by client work, those pesky rascals, so have been unable to dive into this. Yet. :(

[tuanth89]

Hi @Rgl88 ,
I have same problem, which way to resolve that ?
Thanks

[tuanth89]

Hi @AndreaMarchetto ,
I tried and have a problem about loss data:
I have a Db, include table Test (Id, Name, Desc) and records

• Change model Test (ex : add field Status), run first app migration success (include field Status and table have record),
• Close app and run again : include field Status but old data loss (table empty)
  I think problem at class SqliteDropCreateAndMigrateDatabaseWhenModelChanges ==> InitializeDatabase (method) ==> base.InitializeDatabase(context), after migration success and at second run, it still drop and create again.
  Somehow to resolve this??
  Thanks

UPDATE
I resolved by comment line code in SqliteDropCreateAndMigrateDatabaseWhenModelChanges.cs

string tbl_name = string.Format("History_{0}", History.TableNamePostfix);
List fld_list = new List() { "Hash", "Context", "CreateDate" };
ImportTable(from_hash, to_hash, cmd, tbl_name, fld_list);

=> keep data and add new field

[Rgl88]

Hello @tuanth89
Unfortunately I could not find a solution!
It seems like you have been able to solve the problem 👍

[AndreaMarchetto]

Hello @tuanth89
You are right...
Sorry, I founded this bug some time ago but I forgot to update my post!

[CADbloke]

Just leaving this here so I don't lose it - looks useful ...
https://github.com/praeclarum/sqlite-net/wiki/AutomaticMigrations
https://github.com/praeclarum/sqlite-net/blob/master/src/SQLite.cs#L489 <== code that maps an existing table to a new table during CreateTable

[JobaDiniz]

SetSqlGenerator( "System.Data.SQLite", new SQLite.CodeFirst.SqliteMigrationSqlGenerator() );
This line doesn't work anymore in EF 6.2.0, the Generate method on the CodeFirst.SqliteMigrationSqlGenerator doesn't get called.

Anyone have this issue?

[digocesar]

Hi @JobaDiniz .
Did you changed the Database Initializer?
See if this information can help you.

[JobaDiniz]

Yes, I've changed. It works for EF 6.1.x but not for 6.2.x

…

On qui, 10 de mai de 2018 at 15:14 digocesar ***@***.***> wrote: Hi @JobaDiniz <https://github.com/JobaDiniz> . Did you changed the Database Initializer? See if this <https://github.com/digocesar/SQLiteCodeFirst#sqlitemigrationsqlgenerator-sample> information can help you. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#4 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAKFqpAKgZEXvvMURWshusr4QDQHjl7eks5txIOUgaJpZM4D1sY-> .

[msallin]

https://github.com/msallin/SQLiteCodeFirst/tree/%234

[JobaDiniz]

https://github.com/msallin/SQLiteCodeFirst/tree/%234

@msallin is this link correct? it goes to the home page of this repository...

[msallin]

Points to the branch #4, into which I merged your PR. I'll need some time to look through the code and I'll merge it into the master as soon as EF 6.3 with the fix was released and I integrated it into SQLite.CodeFirst.

[Jiraiyah]

Points to the branch #4, into which I merged your PR. I'll need some time to look through the code and I'll merge it into the master as soon as EF 6.3 with the fix was released and I integrated it into SQLite.CodeFirst.

I integrated your library using nuget package manager, have two questions :
1- did you merge the branch in latest nuget package?
2- suppose i have the dbcontext, and the configuration with some seed, now, what should i do to make sure the db is created? I used create if not exists, would only call for the db in a winform just generate the db now for me? and with the package form nuget.... question 1.....?

[Jiraiyah]

Points to the branch #4, into which I merged your PR. I'll need some time to look through the code and I'll merge it into the master as soon as EF 6.3 with the fix was released and I integrated it into SQLite.CodeFirst.

I integrated your library using nuget package manager, have two questions :
1- did you merge the branch in latest nuget package?
2- suppose i have the dbcontext, and the configuration with some seed, now, what should i do to make sure the db is created? I used create if not exists, would only call for the db in a winform just generate the db now for me? and with the package form nuget.... question 1.....?

Ok, I'm sure missing few things :
1- [Key] annotation, there is no [Key] annotation under your package's name space?
2- take a look at these snippets :

`public class BHDBContext : DbContext
{
public string ConnectionString { get; set; }
public DbSet GlobalSettings { get; set; }

    public BHDBContext () : base("SettingsConnectionString")
    { }

    protected override void OnModelCreating (DbModelBuilder modelBuilder)
    {
        var sqlite = new SqliteCreateDatabaseIfNotExists<BHDBContext>(modelBuilder);
        Database.SetInitializer(sqlite);
    }
}

internal sealed class Configuration : DbMigrationsConfiguration<BHDBContext>
{
    public Configuration ()
    {
        AutomaticMigrationsEnabled = true;
    }
    protected override void Seed (BHDBContext context)
    {
        var s = context.Set<GlobalSettings>().Add(new GlobalSettings());
        s.OverloadRetry = true;
        s.RetryAttempWaitTime = 500;
        s.OverloadRetryAttempts = 1;
        s.Network = "Test";
        s.MainDomain = @"Blah";
        s.TestDomain = @"Blah2";
        s.FirstApplicationRun = true;
        s.Leverage = 10;
        s.Password = "admin".EncryptDouble(true);
    }`

and

public SettingsForm () { InitializeComponent(); Utils.SetWindowTitle (this, "Settings"); Utils.SetConnectionString (); DB = new BHDBContext (); }

so, as much as i thought, it should create the data base if it does not exist when ever i am calling the dbcontext for an instantiation?

guess what, nothing is happening, no data base at all !

[msallin]

Points to the branch #4, into which I merged your PR. I'll need some time to look through the code and I'll merge it into the master as soon as EF 6.3 with the fix was released and I integrated it into SQLite.CodeFirst.

I integrated your library using nuget package manager, have two questions :
1- did you merge the branch in latest nuget package?
2- suppose i have the dbcontext, and the configuration with some seed, now, what should i do to make sure the db is created? I used create if not exists, would only call for the db in a winform just generate the db now for me? and with the package form nuget.... question 1.....?

EF 6.3 with the necessary fix isn't yet released.

[msallin]

Points to the branch #4, into which I merged your PR. I'll need some time to look through the code and I'll merge it into the master as soon as EF 6.3 with the fix was released and I integrated it into SQLite.CodeFirst.

I integrated your library using nuget package manager, have two questions :
1- did you merge the branch in latest nuget package?
2- suppose i have the dbcontext, and the configuration with some seed, now, what should i do to make sure the db is created? I used create if not exists, would only call for the db in a winform just generate the db now for me? and with the package form nuget.... question 1.....?

Ok, I'm sure missing few things :
1- [Key] annotation, there is no [Key] annotation under your package's name space?
2- take a look at these snippets :

`public class BHDBContext : DbContext
{
public string ConnectionString { get; set; }
public DbSet GlobalSettings { get; set; }

    public BHDBContext () : base("SettingsConnectionString")
    { }

    protected override void OnModelCreating (DbModelBuilder modelBuilder)
    {
        var sqlite = new SqliteCreateDatabaseIfNotExists<BHDBContext>(modelBuilder);
        Database.SetInitializer(sqlite);
    }
}

internal sealed class Configuration : DbMigrationsConfiguration<BHDBContext>
{
    public Configuration ()
    {
        AutomaticMigrationsEnabled = true;
    }
    protected override void Seed (BHDBContext context)
    {
        var s = context.Set<GlobalSettings>().Add(new GlobalSettings());
        s.OverloadRetry = true;
        s.RetryAttempWaitTime = 500;
        s.OverloadRetryAttempts = 1;
        s.Network = "Test";
        s.MainDomain = @"Blah";
        s.TestDomain = @"Blah2";
        s.FirstApplicationRun = true;
        s.Leverage = 10;
        s.Password = "admin".EncryptDouble(true);
    }`

and

public SettingsForm () { InitializeComponent(); Utils.SetWindowTitle (this, "Settings"); Utils.SetConnectionString (); DB = new BHDBContext (); }

so, as much as i thought, it should create the data base if it does not exist when ever i am calling the dbcontext for an instantiation?

guess what, nothing is happening, no data base at all !

Please create a new issue for this and upload the code somewhere (only what's necessary to reproduce), then I'll have a look at it.
Thank you.

[CADbloke]

I haven't followed this up but apparently SQLite v 3.25.0 introduced renaming columns and fixed some table renaming problems: https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table/52346199#52346199

[tiancai4652]

System.Data.SQLite.EF6.Migrations comes out.

[msallin]

Did anybody use System.Data.SQLite.EF6.Migrations? If yes, does it completely replace this library or is there anything you can't do? I'm not very emotional to deprecate, if there is a better option.

[CADbloke]

I found System.Data.SQLite.EF6.Migrations at https://github.com/bubibubi/sqliteef6migrations. It doesn't seem to replace your library, I didn't look at it closely. I need to continue to use .NET framework for most of my projects so even if you deprecate this I will stay with it.

I use a manual SQL / Reflection thing based on .. https://github.com/semashkinvg/SQLiteCodeFirst/blob/master/SQLite.CodeFirst.Console/FootballDbInitializer.cs ... well, sort of. Wow, I can't figure out where all that came from and it was only 9 months ago. My Trello card for it has lots of links and notes.

It makes a lot o use of EdmType. It also has provision for schema-number based manual SQL migraions. It is all in the InitializeDatabase([NotNull] DbContext dbContext) method. It ain't pretty but it has been reliable so far. Let me know if you want to see it.

[msallin]

I rebased and squashed the branch containing the migrations code from @digocesar and dropped net4.0 support. I didn't test it but if anybody wants to continue, feel free.

Artifacts for testing are here: https://ci.appveyor.com/project/msallin/sqlitecodefirst/build/job/gy8s20q6fak8804r/artifacts
Branch: https://github.com/msallin/SQLiteCodeFirst/tree/%234

[sunghwan2789]

I rebased and squashed the branch containing the migrations code from @digocesar and dropped net4.0 support. I didn't test it but if anybody wants to continue, feel free.

Artifacts for testing are here: https://ci.appveyor.com/project/msallin/sqlitecodefirst/build/job/gy8s20q6fak8804r/artifacts
Branch: https://github.com/msallin/SQLiteCodeFirst/tree/%234

I had to add this code to SQLite.CodeFirst.MigrationConsole.csproj so that I could resolve MissingManifestfastResourceException:

  <!-- Bug with the SDK for .NET Framework projects. The resx migrations are not included
  See: https://github.com/dotnet/ef6/issues/1258#issuecomment-531355034 -->
  <PropertyGroup>
    <EmbeddedResourceUseDependentUponConvention>true</EmbeddedResourceUseDependentUponConvention>
  </PropertyGroup>

exception thrown before adding the code:

System.Resources.MissingManifestResourceException: '지정한 문화권 또는 중립 문화권에 적합한 리소스를 찾을 수 없습니다. 
컴파일 타임에 "SQLite.CodeFirst.MigrationConsole.Migrations.InitialCreate.resources"이(가)
 "SQLite.CodeFirst.MigrationConsole" 어셈블리에 제대로 포함 또는 링크되어 있는지 확인하고,
필요한 위성 어셈블리가 로드 가능한지와 완전히 서명되어 있는지 확인하십시오.'
   at System.Resources.ManifestBasedResourceGroveler.HandleResourceStreamMissing(String fileName)
   at System.Resources.ManifestBasedResourceGroveler.GrovelForResourceSet(CultureInfo culture, Dictionary`2 localResourceSets, Boolean tryParents, Boolean createIfNotExists, StackCrawlMark& stackMark)
   at System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo requestedCulture, Boolean createIfNotExists, Boolean tryParents, StackCrawlMark& stackMark)
   at System.Resources.ResourceManager.InternalGetResourceSet(CultureInfo culture, Boolean createIfNotExists, Boolean tryParents)
   at System.Resources.ResourceManager.GetString(String name, CultureInfo culture)
   at SQLite.CodeFirst.MigrationConsole.Migrations.InitialCreate.System.Data.Entity.Migrations.Infrastructure.IMigrationMetadata.get_Target() in C:\Users\Administrator\Source\repos\SQLiteCodeFirst\SQLite.CodeFirst.MigrationConsole\Migrations\201805181917550_InitialCreate.Designer.cs:line 26
   at System.Data.Entity.Migrations.DbMigration.GetModel(Func`2 modelAccessor)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)