Entity Framework : How to create code using Existing Database

This is a step-by-step walkthrough which will use existing database to create the model. We will create one console Application which will generate the model using existing databases.

Pre-Requisities

We need to have Visual Studio 2013 installed to complete this walkthrough.(You can use older version too like 2010 or 2012 to complete this tutorial.)

Apart from that, You will also need to have NuGet installed on your Visual Studio and the 6.1 or later of the Entity Framework Tools.

1. Connect to an Existing Database

Check here to see how can you connect to existing database. I have already created one database in my previous blog. I ll use the same database(CreateNewDatabase.ManagingContext) to generate the model.

2. Create the Application

• Open Visual Studio, Click File → New → Project
• Click Console Application under templates → Visual C# → Windows
• Enter Name as CreateModelUsingExistingDatabase and Select OK

3. Reverse Engineering Model

Here I am going to make use of the Entity Framework Tools for Visual Studio to help us generate some initial code to map to the database.

• In Solution Explorer, Right Click on your Project and then Add New Item...
• Select Data from the left menu and then ADO.NET Entity Data Model
• Enter Name as Managing Context and click OK
• Now Select Code First From Database from Entity Data Model Wizard and then click Next.

• Select the connection to the database you created and set Entities name too and click Next.

• From choose your version, Select Entity Framework 6.0 and click Next.
• Click the check box next to Tables to import all tables and click Finish

Once you are completed, you will see a number of items that have been added to the project.

App.Config : This file contains the connection string to the existing database.

<connectionStrings>

<add name="ManagingContext" connectionString="metadata=res://*/ManagingContext.csdl|res://*/ManagingContext.ssdl|res://*/ManagingContext.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MYNA\SQLEXPRESS;initial catalog=CreateNewDatabase.ManagingContext;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>

ManagingContext : A ManagingContext class has been added to your project. This represents a session with the database, allowing us to query and save data. In the default constructor there is name=ManagingContext. This tells Application that the connection string to use for this context should be loaded from the configuration file.




public partial class ManagingContext : DbContext

{

public ManagingContext()

: base("name=ManagingContext")

{

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

throw new UnintentionalCodeFirstException();

}

public virtual DbSet<Employee> Employees { get; set; }

public virtual DbSet<Manager> Managers { get; set; }

}

Model classes :

You will also observer Manger and Employee class are also added to the project. These are the domain classes of the Application

4. Reading & Writing Data

Now its the time to implement the Main method in Program.cs. It will create a new instance of our context and then uses it to insert a new Manager. I have used LINQ query to retrieve all Manager entry from the database.

class Program

{

static void Main(string[] args)

{

using (var db = new ManagingContext())

{

// Create and save a new Blog

Console.Write("Enter a name for a new Manager: ");

var name = Console.ReadLine();

var manager = new Manager { ManagerName = name };

db.Managers.Add(manager);

db.SaveChanges();

// Display all Blogs from the database

var query = from b in db.Managers

orderby b.ManagerName

select b;

Console.WriteLine("All Managers in the database:");

foreach (var item in query)

{

Console.WriteLine(item.ManagerName);

}

Console.WriteLine("Press any key to exit...");

Console.ReadKey();

}

}

}

Now Run the Application :

How to connect to database using Server Explorer in Visual Studio

Steps to connect to your database using Server Explorer in Visual Studio

• Open Visual Studio, Select View → Server Explorer
  
• Right Click on Data Connections and select Add Connection...
  

If you are not connected to a database from Server Explorer then you will need to select Microsoft Server as the data source.

You can connect to either localDb((localdb)\v11.0) or SQL Express (./SQLEXPRESS), In my case I am connecting to SQLEXPRESS which is hosted my machine(MYNA) so the name will be “MYNA\SQLEXPRESS” and then select your existing database or you can define new database which will get created.

Once you are done , You can check the database in the Server Explorer 

Entity Framework : How to create database using code

This is a step-by-step walkthrough which will create a new database using C# code. We will create one console Application which will create an empty database and will also add new tables too.

Pre-Requisities

We need to have Visual Studio 2013 installed to complete this walkthrough.(You can use older version too like 2010 or 2012 to complete this tutorial.)

Apart from that, You will also need to have NuGet installed on your Visual Studio.

Steps :
1. Create new Application

• Open Visual Studio, Click File -> New -> Project
• Click Console Application under templates -> Visual C# -> Windows
• Enter Name as CreateNewDatabase and Select OK

2. Create the Model

Now we will create two simple model(Employee, Manager) using classes. As a part of demo I'll define both in Program.cs but in a real word application we should define in two separate file/classes.

Add the following two classes in Program.cs.

public class Manager

{

public int ManagerId { get; set; }

public int ManagerName { get; set; }

public virtual List<Employee> Employees { get; set; }

}

public class Employee

{

public int EmployeeId { get; set; }

public String EmployeeName { get;set; }

public String EmployeeDesignation { get; set; }

public int ManagerId {get;set;}

public virtual Manager Manager { get; set; }

}

If you have notice I have made the two navigation properties (Manager.Employee and Employee.Manager) virtual. This enables the Lazy Loading feature of Entity Framework. Lazy Loading means that the contents of these properties will be automatically loaded from the database when you try to access them.

3. Create a Context

Before creating a context, first we will install the EntityFramework Nuget package.

• Click Project -> Manage NuGet Packacges
• Select the online tab and then select the EntityFramework Package and Install.

Now we need to create a session so that we can query and save data into the database, we need to define a derived context (System.Data.Entity.DbContext) and exposes a typed DbSet<Entity> for each class in our model.

Add following derived context in the the Program.cs file.

public class ManagingContext : DbContext

{

public DbSet<Manager> Blogs { get; set; }

public DbSet<Employee> Posts { get; set; }

}

Program.cs :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.Entity;

namespace CreateNewDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }

    public class Manager
    {
        public int ManagerId { get; set; }
        public String ManagerName { get; set; }
        public String ManagerAddress { get; set; }
        public virtual List<Employee> Employees { get; set; }
    }

    public class Employee
    {
        public int EmployeeId { get; set; }
        public String EmployeeName { get;set; }
        public String EmployeeDesignation { get; set; }
        public int ManagerId {get;set;}
        public virtual Manager Manager { get; set; }
    }

    public class ManagingContext : DbContext
    {
        public DbSet<Manager> Managers { get; set; }
        public DbSet<Employee> Employees { get; set; }
    }
}

4. Reading and Writing Data

Now its the time to implement the Main method in Program.cs. It will create a new instance of our context and then uses it to insert a new Manager. I have used LINQ query to retrieve all Manager entry from the database.

class Program

{

static void Main(string[] args)

{

using (var db = new ManagingContext())

{

// Create and save a new Manager entry

Console.Write("Enter a name for a new Manager: ");

var name = Console.ReadLine();

var manager = new Manager { ManagerName = name };

db.Managers.Add(manager);

db.SaveChanges();

// Display all Managers from the database

var query = from b in db.Managers

orderby b.ManagerName

select b;

Console.WriteLine("All Managers in the database:");

foreach (var item in query)

{

Console.WriteLine(item.ManagerName);

}

Console.WriteLine("Press any key to exit...");

Console.ReadKey();

}

}

}

Now Run the Application :




5. Where is your Data ??
DbContext has created a database for you. You need to check it into your SQL Express instance or If you have installed it then it will be in your LocalDb. (Both are installed by default with Visual Studio 2013). Click here if you don't know how to connect to database.
The database is named after the fully qualified name of the derived context, In our case that is CreateNewDatabase.ManagingContext

What is NuGet and how to install it on Visual Studio 2013

NuGet is the package manager for the Microsoft development platform including .NET. The NuGet client tools provide the ability to produce and consume packages. The NuGet Gallery is the central package repository used by all package authors and consumers.

How to install NuGet using Visual Studio Extension Manager :

• Open Visual Studio, Click Tools and then Extension Manager.
• Navigate to Online, Find NuGet Manager Extension and click Download
• In the Installer dialog box, click Install.
• When installation is complete, close and re-open Visual Studio.

NuGet is now ready to use.

What is Entity Framework ?

Entity framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database and working with the results in addition to DataReader and DataSet. It eliminates the need for most of the data-access code that developers usually need to write.

It allows you to create a model by writing code or using boxes and lines in the EF Designer. Both of these approaches can be used to target an existing database or create a new database. It also provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

Following figure shows the overall architecture of the Entity Framework (Source : http://www.codeproject.com/)

Application : Application which is going to use Entity Framework.

EDM (Entity Data Model): EDM consist three main parts- Conceptual model, Mapping and Storage model.

• Conceptual Model: Defines model classes and their relationships.
• Storage Model: It is your database design model which includes tables, views, stored procedures and their relationships and keys.
• Mapping: Mapping consist information about how your conceptual model is mapped to storage model.

ADO.Net Provider: This layer communicates with database using standard ADO.Net.

Data Store : Type of database that you are going to use.

Below are the few Step-by-Step tutorials which will give you the better understanding of Entity Framework :

1. Entity Framework : How to create database using code.
2. Entity Framework : How to create code using Existing Database
3. Entity Framework : How to generate model using Entity Framework Design