Code-First with existing database

Some online resources indicate that code first technique is for green field project only. However, in the fact that we still can use code first technique with existing database. This note will show how to do it.

First, create a database called “OldDatabase”, and run the following scripts.

/* Table */
CREATE TABLE Region (
RegionId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL
);
GO
CREATE TABLE Office (
OfficeId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL
);
GO
CREATE TABLE Election (
ElectionId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL,
Date date NOT NULL,
RegionId int NOT NULL,
FOREIGN KEY (RegionId) REFERENCES Region(RegionId)
);
GO
CREATE TABLE ElectionOffice
(
 ElectionId int NOT NULL,
 OfficeId int NOT NULL,
 CONSTRAINT PK_ElectionOffice PRIMARY KEY
 (
 ElectionId,
 OfficeId
 ),
 FOREIGN KEY (ElectionId) REFERENCES Election (ElectionId),
 FOREIGN KEY (OfficeId) REFERENCES Office (OfficeId)
)
GO
/* Insert Data */
INSERT INTO [dbo].[Region]
           ([RegionId]
           ,[Name])
     VALUES
           (1
           ,'Region 1')
GO
INSERT INTO [dbo].[Region]
           ([RegionId]
           ,[Name])
     VALUES
           (2
           ,'Region 2')
GO
INSERT INTO [dbo].[Region]
           ([RegionId]
           ,[Name])
     VALUES
           (3
           ,'Region 3')
GO
INSERT INTO [dbo].[Office]
           ([OfficeId]
           ,[Name])
     VALUES
           ('1'
           ,'Office 1')
GO
INSERT INTO [dbo].[Office]
           ([OfficeId]
           ,[Name])
     VALUES
           ('2'
           ,'Office 2')
GO
INSERT INTO [dbo].[Office]
           ([OfficeId]
           ,[Name])
     VALUES
           ('3'
           ,'Office 3')
GO
INSERT INTO [dbo].[Election]
           ([ElectionId]
           ,[Name]
           ,[Date]
           ,[RegionId])
     VALUES
           ('1'
           ,'Election 1'
           ,'2018-01-01'
           ,'1')
GO
INSERT INTO [dbo].[Election]
           ([ElectionId]
           ,[Name]
           ,[Date]
           ,[RegionId])
     VALUES
           ('2'
           ,'Election 2'
           ,'2018-01-02'
           ,'1')
GO
INSERT INTO [dbo].[Election]
           ([ElectionId]
           ,[Name]
           ,[Date]
           ,[RegionId])
     VALUES
           ('3'
           ,'Election 3'
           ,'2018-01-01'
           ,'2')
GO
INSERT INTO [dbo].[Election]
           ([ElectionId]
           ,[Name]
           ,[Date]
           ,[RegionId])
     VALUES
           ('4'
           ,'Election 4'
           ,'2018-01-01'
           ,'3')
GO

INSERT INTO [dbo].[ElectionOffice]
           ([ElectionId]
           ,[OfficeId])
     VALUES
           ('1'
           ,'1')
GO
INSERT INTO [dbo].[ElectionOffice]
           ([ElectionId]
           ,[OfficeId])
     VALUES
           ('1'
           ,'2')
GO
INSERT INTO [dbo].[ElectionOffice]
           ([ElectionId]
           ,[OfficeId])
     VALUES
           ('2'
           ,'2')
GO
INSERT INTO [dbo].[ElectionOffice]
           ([ElectionId]
           ,[OfficeId])
     VALUES
           ('2'
           ,'3')
GO

Second, install Entity Framework to your project (You can refer to previous note to know how to do it)

Third, add the entity model from existing database

    1. On the Project, add new item (right click -> Add -> New Item), and select ADO.NET Entity Data Model, and click “Add”

   

    2. In the next step, select “Code First from database” option

    

    3. Setup the connection to your database

    

    4. Then, select the tables from database you want to include to your model

    

    5. Then you will find that a file called “CodeFirstTestContext” has been created. 

    

    From this file, we can see that it used fluent API to override the code first convention.

    Also, under project folder, we can find Entity Framework successfully generate the relating models for us.

    

Forth, we want to add migration to this project in order easily control database changes by code, not manually change database.

    1. Run the following command on Package Manager Console

        Enable-migrations

        Then you can see the new folder called “Migrations”

        


    2. Add initial migration

     If you just use “add-migration initModel”, then you will find that entity framework will create duplicate table in snapshot for you. But that is not what we want.

    Refer to Click Me, there are two options to handle this case.

    If we use first option, we can use “Add-Migration initModel –IgnoreChanges” to create an empty migration with current model as a snapshot.

    Then, we can see it created empty "up" and "down" for initModel migration

    

    3. Then, run the following command to update database

        update-database

    After that, in your database, you can see the MigrationHistory table has been created.

    

According above, we learn that how to use existing database with code-first technique and migration to our project.