Feature suggestion: Migrations

What do you think about migrations-support?

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.

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.
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.

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

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.

Hi Gary

You can use EF without migrations. Have a look at the Code in this Repo, there is a sample Application.
You can not run "update-database" because there is no implementation for the SQL Generator.
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

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.

+1

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

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

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.....?

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 !

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.

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.

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

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

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.

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.

#4: Migrations support

@digocesar

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

@digocesar

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)

#4: Migrations support

Navigation Menu

Repository navigation

Feature suggestion: Migrations #4

29 remaining items

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Feature suggestion: Migrations #4

Description

Activity

msallin commented on Mar 28, 2015

code1line commented on Mar 28, 2015

GaryWolfe commented on Jun 24, 2015

msallin commented on Jun 24, 2015

GaryWolfe commented on Jun 24, 2015

HarelM commented on Jan 18, 2016

tg73 commented on Feb 12, 2016

msallin commented on Feb 17, 2016

29 remaining items

Jiraiyah commented on Dec 4, 2018

Jiraiyah commented on Dec 4, 2018

msallin commented on Dec 8, 2018

msallin commented on Dec 8, 2018

CADbloke commented on Dec 18, 2018

tiancai4652 commented on May 14, 2019

msallin commented on Apr 25, 2020

CADbloke commented on Apr 26, 2020

msallin commented on May 2, 2020

sunghwan2789 commented on Aug 8, 2020

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions