Can I migrate to database created without migration in Entity framework?

Question

I had created a desktop application which uses SQL CE 4.0 and Entity framework with Code First mode. So, Initially I had installed application on a system, it worked fine.

Now I added few properties to model class, so for this purpose I enabled migrations using Enable-Migrations and added new migration using Add-Migration "MyMigration". This was also successfull and I was able to install new version and upgrade database without any issue.

But now when I install this setup on a system with no existing database then it fails, so after studying I realized that I need to add Initial Migration So, I revoked changes in model and also deleted my SQLCE database file, added initial migration, then Update-Database. So if I try to Update-Database using old-database which was created without enabling migrations then it fails, it tries to apply initial migration to database which it shouldn't according to me.

Now if I delete old-database file and then Update-Database then new db is created and then once again added my changes to model and added expected migration.

So after this, turnaround I am able to install my application on blank system but it fails on a system with old database i.e. it doesn't upgrade database.

It tries to apply Initial-Migration also, i.e. creating all the tables once again and fails saying that, The table already exists which isn't expected.

Answer 1

The thing is that it checks a table called Migrations, where I think it get information of the current squema and changes. If you make a lof of changes of the DB without using EF, somehow you'll get to a point when a lot of clonflicts would happen.

You can use fluent migrations to do everything yourself and have more control of the changes and the rollbacks

Example:

 [Migration(201610250659)]
public class _201610250659_AddedMinimumValue_Prices : Migration
{
    public override void Up()
    {
        Alter.Table("Prices")
            .AddColumn("MinimunValue").AsInt32().NotNullable().WithDefaultValue(1);
    }

    public override void Down()
    {
        Delete.Column("MinimunValue")
            .FromTable("Prices");
    }
}

Answer 2

OK, so if you have, say, 2 migrations: Initial and MyMigration and now you have an old database that has the changes represented by the first migration, but not the second and it keeps trying to apply the unneeded first migration - then you can resolve it this way:

1) Generate a complete migration script (BTW, this can be used to create a blank database of your model):

Update-Database -Script -SourceMigration: $InitialDatabase

2) The code at the top of this script will check for the existance of __MigrationHistory and create it if it is missing.

3A) Next, it will check for each migration with code like below. Since your database already has these you should comment those lines out.

IF @CurrentMigration < '201702231958592_Initial'
BEGIN
     -- objects already exist, but EF doesn't know this
     -- CREATE TABLE, etc.
END

3B) Entity framework will insert a record into __MigrationHistory so it knows this migration has been applied. Do not comment this line out! (The long hex string is a representation of your model).

INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'201702231958592_Initial', N'MyApp.Data.Migrations.Configuration',  0x1F8B08000000

4) Steps 3A and 3B will be repeated for each migration, so leave them in there if they have not been applied. Comment out the object changes if they do.

Moving forward if you haven't messed with migrations EF will be smart enough to apply the changes to various deployments in different states of applied migrations.

As to your installer issue, you could either call this script via ADO.NET or you could use DbMigrator to do migrations in code.

Answer 3

you need to check if into initial scripts, if table already exist or not. Its not EF issue, even if your try to simply run create database scripts with table definition without checking existing one, it will fail.

For Example,

I don't have any database installed into my machine and I'm having script for create new with some table into it..

so this is script for creating table

    USE [TestStudent]
GO
/****** Object:  Table [dbo].[EmployeeInformation]    Script Date: 18-03-2017 19:12:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeInformation](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Designation] [nvarchar](50) NULL,
 CONSTRAINT [PK_EmployeeInformation] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[sample]    Script Date: 18-03-2017 19:12:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sample](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TableId] [int] NULL,
 CONSTRAINT [PK_sample] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Notice that I'm just creating the tables not checking for object existence, now if I run this on new machine with blank database it will run without error..

Now, I try to run same script on existing database with existing objects may be for updating some tables like in your case but here's the trick, your update script will be at the bottom of this... so it will first try to create the tables and if existed it will throw you error..

so you need to check the object existence before you create tables, like in this script...

    USE [TestStudent]
GO
/****** Object:  Table [dbo].[EmployeeInformation]    Script Date: 18-03-2017 19:22:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeInformation]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EmployeeInformation](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Designation] [nvarchar](50) NULL,
 CONSTRAINT [PK_EmployeeInformation] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[sample]    Script Date: 18-03-2017 19:22:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sample]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sample](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TableId] [int] NULL,
 CONSTRAINT [PK_sample] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

so when you try to run this, it will skip the already existing objects that are in table will proceed to your update scripts...

hope this helps...

Answer 4

What you need is

update-database 

this will update the database accordingly. Just remember everytime you add-migration you need to update-database, and when you install the system somewhere else, all you need is to do a update-database to get the older database on par with entity framework.