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. 

Database-first using Entity Framework

Entity Framework provides a way to create a model from the existing database. It’s called “Database First”. For this note, I will give a simple example to indicate how to do it.

First, create a database called “DatabaseFirstTest”, and run the following SQL scripts to create the testing data.

          ------------------------------------------------

/* Tables */
CREATE TABLE Brand(
BrandId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL
);
GO
CREATE TABLE Car(
CarId int NOT NULL PRIMARY KEY,
BrandId int NOT NULL REFERENCES Brand(BrandId),
);
GO
CREATE TABLE Person(
PersonId int NOT NULL PRIMARY KEY,
Name varchar(255) NOT NULL,
);
GO
CREATE TABLE PersonCar(
PersonId int NOT NULL EFERENCES Person(PersonId),
CarId int NOT NULL EFERENCES Car(CarId),
PRIMARY KEY(PersonId, CarId)
);
GO
/* Seeding */
INSERT INTO [dbo].[Brand]
           ([BrandId]
           ,[Name])
     VALUES
           ('1'
           ,'BMW')
INSERT INTO [dbo].[Brand]
           ([BrandId]
           ,[Name])
     VALUES
           ('2'
           ,'Jeep')
INSERT INTO [dbo].[Brand]
           ([BrandId]
           ,[Name])
     VALUES
           ('3'
           ,'Audi')
INSERT INTO [dbo].[Brand]
           ([BrandId]
           ,[Name])
     VALUES
           ('4'
           ,'GMC')
INSERT INTO [dbo].[Brand]
           ([BrandId]
           ,[Name])
     VALUES
           ('5'
           ,'Lexus')
INSERT INTO [dbo].[Brand]
           ([BrandId]
           ,[Name])
     VALUES
           ('6'
           ,'Mazda')
GO
INSERT INTO [dbo].[Car]
           ([CarId]
           ,[BrandId])
     VALUES
           ('1'
           ,'1')
INSERT INTO [dbo].[Car]
           ([CarId]
           ,[BrandId])
     VALUES
           ('2'
           ,'2')
INSERT INTO [dbo].[Car]
           ([CarId]
           ,[BrandId])
     VALUES
           ('3'
           ,'3')
INSERT INTO [dbo].[Car]
           ([CarId]
           ,[BrandId])
     VALUES
           ('4'
           ,'3')
INSERT INTO [dbo].[Car]
           ([CarId]
           ,[BrandId])
     VALUES
           ('5'
           ,'1')
INSERT INTO [dbo].[Car]
           ([CarId]
           ,[BrandId])
     VALUES
           ('6'
           ,'1')
GO
INSERT INTO [dbo].[Person]
           ([PersonId]
           ,[Name])
     VALUES
           ('1'
           ,'Frank')
GO
INSERT INTO [dbo].[Person]
           ([PersonId]
           ,[Name])
     VALUES
           ('2'
           ,'Jake')
GO
INSERT INTO [dbo].[Person]
           ([PersonId]
           ,[Name])
     VALUES
           ('3'
           ,'Mike')
GO
INSERT INTO [dbo].[PersonCar]
           ([PersonId]
           ,[CarId])
     VALUES
           ('1'
           ,'1')
INSERT INTO [dbo].[PersonCar]
           ([PersonId]
           ,[CarId])
     VALUES
           ('1'
           ,'2')
INSERT INTO [dbo].[PersonCar]
           ([PersonId]
           ,[CarId])
     VALUES
           ('2'
           ,'3')
INSERT INTO [dbo].[PersonCar]
           ([PersonId]
           ,[CarId])
     VALUES
           ('2'
           ,'4')
INSERT INTO [dbo].[PersonCar]
           ([PersonId]
           ,[CarId])
     VALUES
           ('3'
           ,'5')
INSERT INTO [dbo].[PersonCar]
           ([PersonId]
           ,[CarId])
     VALUES
           ('1'
           ,'6')
GO

/* Stored Procedure */
CREATE PROCEDURE spGetCarsInformationByPerson 
    @PersonName nvarchar(50)
AS 
SELECT
b.Name as CarBrandName,
Count(b.Name) as CountOfCars
FROM Person p
JOIN PersonCar pc on p.PersonId = pc.PersonId
JOIN Car c on pc.CarId = c.CarId
JOIN Brand b on c.BrandId = b.BrandId
WHERE p.Name = @PersonName
Group by b.Name
GO  

          ------------------------------------------------

Then there are four tables and one stored procedure added to our database.

From this SQL scripts, we can see that Car and Brand is 1-to-Many relationship. Also, it is Many-to-Many relationship between Car and Person. (we assume that many persons could share the same car…and person can own multiple cars)

Second, create a console application and install Entity Framework.

a. Select console App

b. On top menu bar, select Tools -> NuGet Package Manger -> Manage NuGet Packages for Solution…

c. Select EntityFramwork and install it to our project.

Third, we will add the Entity Data Model to our project

a. Right click the current project and select Add -> New Item

b. Then, select “EF Designer from database”

c. Click -> New connection to setup connection detail

d. Mark all tables and store procedures, and click finish

e. Then we can see a diagram created by EF.

Then, we can start to analyze what EF has done for us.

a. On our project folder, previous diagram is bound to EDMX file.

b. If you want to see the detail of this file, you can right-click that EDMX file, and open with XML format.

It seems scared for too much information on previous picture. I will explain it briefly.

a. Definition

SSDL: Store schema definition language

CSDL: Conceptual schema definition language

MSL(C-S mapping): Mapping specification language

b. To make it clear, you can think SSDL is the model of database, and CSDL is the model of entity. And MSL is used to describe how to map model of database and model of entity. If you still feel confused, click “HERE” to get more information.

c. Also, if you update model from database, and you have some changes, such as delete tables, delete fields, rename fields, or change attribute of fields, then SSDL will be update, but CSDL will not be updated. You need to modify CSDL manually.

We can do a quick experiment to see what happens for SSDl, CSDL, and MSL.

a. Let change the table name from Car to Vehicle. Also, change the CarId to VehicleId.

b. Then open EDMX file again.

c. For SSDL, there is no any changes.

d. For CSDL, we found that its name has been changed to Vehicle!

e. For MSL, we can find that it indicates that Entity Vehicle should bind to model Car. Also, VehicleId will bind to CarId as well.

It also provides a visual way to view models.

Just right click the EDMX diagram and select Model Browser. Then you can see the different models from entity or database separately.

Also, you can change the mapping by right click entity from EDMX file, and select “Table Mapping”

Now, we will give an example to show how to use our stored procedure.

a. Paste the following codes on Program.cs

          ------------------------------------------------

var dbContext = new DatabaseFirstTestEntities();
var list = dbContext.spGetCarsInformationByPerson("Frank");
 foreach (var item in list) {
    Console.Write("Brand: {0}, Count: {1}\n",

    item.CarBrandName,

    item.CountOfCars);
}

          ------------------------------------------------

b. Then, you can see the result as following.

     

c. If you execute the following command in database, you will get the same result.

          ------------------------------------------------

EXECUTE spGetCarsInformationByPerson @PersonName = N'Frank'

          ------------------------------------------------

d. Both results are the same!

However, you might notice that in C#, we do not name function starting with lower case. We also can change it.

a. On Model Browser, double click spGetCarsInformationByPerson function, then it will pop-up a window to let you edit its name or modify the binding.

b. After that, you can use dbContext.GetCarsInformationByPerson function and trigger the spGetCarsInformationByPerson on database.


According above, we learn how to use database-first to create model in our project!