Code First with Existing Database

In this tutorial, we will show how to use Code First with the existing database in entity framework.

In our last tutorials, we explained how to define your model using POCO classes. You can fine-tune your models using the Code First Conventions, Data Annotations attribute, and Fluent API.

What if you are asked to implement Entity Framework Code First on existing database ?. In such scenario, you have two options. One option is to write all your POCO classes by hand. The second option is to Reverse Engineer the existing database using some external tools.

Table of Contents

Reverse Engineer Code First with Existing Database

Reverse Engineer Code First with Existing Database

The Entity Framework Version 6.1 and later provides us with an easy option to Reverse Engineer the existing Database. In this step by step walkthrough, we will show you how to reverse engineer an existing database

Existing Database

First, we need an existing database. You can choose any database. Let us use the AdventureWorks database. You can download it from the link http://msftdbprodsamples.codeplex.com/releases/view/59211.

Once downloaded, Restore the database to SQL Server

Entity Framework 6 Tools for Visual Studio 2012 & 2013

Download and install the Entity Framework 6 Tools from the link Provided. This tool has the option to Reverse Engineer Code First.

Create the Project

Open the Visual Studio 2013 or 2012 and perform the following tasks

Select File -> New -> Project
Choose C#
Select Console Application
Name the Application as EFCodeFirstExistingDatabase
Click on OK

Install Entity Framework

Go to Tools -> Library Package Manager -> Package Manager Console

Run the following command in the Package Manager Console

Install-package entityframework

ADO.NET Entity Model Wizard

Right Click on Project.
Click on Add -> New Folder.
Name the folder as Models.
All our models will go into this folder
Now right click on the Models.
Click on Add -> New Item
Under data choose ADO.NET Entity Model
Name it as AdventureWorks
Click on Add

You will be taken to the Entity Data Model Wizard dialogue box. Choose Code First from Database Option. Click on Next

The Next dialog box is Choose your data connection. Click on New Connection

In Choose Data Source Dialog box select Microsoft SQL Server and click on Continue

In Connection Properties Dialog box to enter

Server Name
Log On Credentials
Select the Database Name
Click on OK once finished

This will take you to Choose your data connection wizard. The Connection we just created now appears in the drop-down. Check the “Save the Connection settings in App.Config as” box. Click on Next

You are taken to the Choose your database objects and settings dialogue box as shown in the image below

Select the tables & Views you want to be in your model and click on Finish. Build the solution to check if there are any errors

Reverse Engineer Code First

The Reverse engineer process generates all the required plumbings to get us started with the existing database. It creates the Connection string, models, and context classes. Let us examine each of them in details

The Connection string in App.Config

The Code First Reverse engineer wizard will generate the Connection string and adds it your App.Config file. If you open your App.Config you will see the following connection string. Connection string may differ depending on the version of SQL Server being used.

</connectionStrings>

Models

The Models class are generated one for each table under the folder models. Data Annotations attributes are also applied to these classes. The following is the generated code for the Customer model

[Table("Sales.Customer")]

public partial class Customer

{

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]

public Customer()

{

SalesOrderHeaders = new HashSet<SalesOrderHeader>();

}

public int CustomerID { get; set; }

public int? PersonID { get; set; }

public int? StoreID { get; set; }

public int? TerritoryID { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

[Required]

[StringLength(10)]

public string AccountNumber { get; set; }

public Guid rowguid { get; set; }

public DateTime ModifiedDate { get; set; }

public virtual Person Person { get; set; }

public virtual SalesTerritory SalesTerritory { get; set; }

public virtual Store Store { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]

public virtual ICollection<SalesOrderHeader> SalesOrderHeaders { get; set; }

}

Context

You will also find the context class AdventureWorks.cs is added to the folder models. Fluent API is used in OnModelCreating method to set up relationships between entities

Test the Application

Finally, we can test our application by querying the person table and listing out its content. The code is as shown below.

class Program

{

static void Main(string[] args)

{

using (var ctx = new models.AdventureWorks())

{

var People = ctx.People.ToList();

foreach (var person in People)

{

Console.WriteLine(string.Format("{0} {1}", person.FirstName, person.LastName));

}

Console.ReadLine();

}

Run this program and you should see the list of all the person

Conclusion

The Above process demonstrates how we can create the code first entity models from the existing database. In case if your database changes then you can manually change the classes or run the reverse engineer process again to generate the model classes

Source Code

Seed Database in EF

DBContext in EF