Existing Database using Entity Framework Code First Approach

Back to: Entity Framework Tutorials For Begineers and Professionals

Existing Database using Entity Framework Code-First Approach

Now, we will discuss How to Generate Context and Entity Classes from an Existing Database using Entity Framework Code-First Approach with Examples. The Entity Framework API provides an easy way to use the code-first approach with an existing database. In this case, the Entity Framework API will create the entity classes for all the tables and views for our existing database and configure them with data annotations attributes and Fluent API.

Required Database Tables, and Views:

We are going to create the database with the following tables. And then, I am going to show you how to generate the Entities using Code First Approach and configure them using Data Annotation Attributes and Fluent API.

Please use the below SQL Script to create the database, tables, and dummy data.

-- Create the EF_Demo_DB database.
CREATE DATABASE EF_Demo_DB;
GO

-- Use EF_Demo_DB
USE EF_Demo_DB;
GO

-- Create Standard Table
CREATE TABLE Standard(
 StandardId INT PRIMARY KEY IDENTITY(1,1),
 StandardName VARCHAR(100),
 Description VARCHAR(100)
)
GO
-- Standard table data
INSERT INTO Standard VALUES('STD1', 'Outstanding');
INSERT INTO Standard VALUES('STD2', 'Good');
INSERT INTO Standard VALUES('STD3', 'Average');
INSERT INTO Standard VALUES('STD4', 'Below Average');
GO

-- Create Teacher Table
CREATE TABLE Teacher(
 TeacherId INT PRIMARY KEY IDENTITY(1,1),
 FirstName VARCHAR(100),
 LastName VARCHAR(100),
 StandardId INT FOREIGN KEY REFERENCES Standard(StandardId)
)
GO
-- Teacher table data
INSERT INTO Teacher VALUES('Anurag', 'Mohanty', 1);
INSERT INTO Teacher VALUES('Preety', 'Tiwary', 2);
INSERT INTO Teacher VALUES('Priyanka', 'Dewangan', 3);
INSERT INTO Teacher VALUES('Sambit', 'Satapathy', 3);
INSERT INTO Teacher VALUES('Hina', 'Sharma', 2);
INSERT INTO Teacher VALUES('Sishanta', 'Jena', 1);
GO

-- Create Course Table
CREATE TABLE Course(
 CourseId INT PRIMARY KEY IDENTITY(1,1),
 CourseName VARCHAR(100),
 TeacherId INT FOREIGN KEY REFERENCES Teacher(TeacherId)
)
GO
-- Course table data
INSERT INTO Course VALUES('.NET', 1);
INSERT INTO Course VALUES('Java', 2);
INSERT INTO Course VALUES('PHP', 3);
INSERT INTO Course VALUES('Oracle', 4);
INSERT INTO Course VALUES('Android', 5);
INSERT INTO Course VALUES('Python', 6);
GO

-- Create Student Table
CREATE TABLE Student(
 StudentId INT PRIMARY KEY IDENTITY(1,1),
 FirstName VARCHAR(100),
 LastName VARCHAR(100),
 StandardId INT FOREIGN KEY REFERENCES Standard(StandardId)
)
GO
-- Student table data
INSERT INTO Student VALUES('Virat', 'Kohli', 1);
INSERT INTO Student VALUES('Rohit', 'Sharma', 2);
INSERT INTO Student VALUES('Lokesh', 'Rahul', 3);
INSERT INTO Student VALUES('Smriti', 'Mandana', 4);
GO

-- Create StudentAddress Table
CREATE TABLE StudentAddress(
 StudentId INT PRIMARY KEY FOREIGN KEY REFERENCES Student(StudentId),
 Address1 VARCHAR(100),
 Address2 VARCHAR(100),
 Mobile VARCHAR(10),
 Email VARCHAR(100)
)
GO
-- StudentAddress table data
INSERT INTO StudentAddress VALUES(1, 'Lane1', 'Lane2', '1111111111', '1@dotnettutorials.net');
INSERT INTO StudentAddress VALUES(2, 'Lane3', 'Lane4', '2222222222', '2@dotnettutorials.net');
INSERT INTO StudentAddress VALUES(3, 'Lane5', 'Lane6', '3333333333', '3@dotnettutorials.net');
INSERT INTO StudentAddress VALUES(4, 'Lane7', 'Lane8', '4444444444', '4@dotnettutorials.net');
GO

-- Create StudentCourse Table
CREATE TABLE StudentCourse(
    StudentId INT NOT NULL FOREIGN KEY REFERENCES Student(StudentId),
    CourseId INT NOT NULL FOREIGN KEY REFERENCES Course(CourseId)
    PRIMARY KEY (StudentId, CourseId)
)
GO
-- StudentCourse table data
INSERT INTO StudentCourse VALUES(1,1);
INSERT INTO StudentCourse VALUES(1,2);
INSERT INTO StudentCourse VALUES(2,3);
INSERT INTO StudentCourse VALUES(2,4);
INSERT INTO StudentCourse VALUES(3,1);
INSERT INTO StudentCourse VALUES(3,6);
INSERT INTO StudentCourse VALUES(4,5);
INSERT INTO StudentCourse VALUES(4,6);
GO

As we are going to work with the existing database, so, it might be possible that we need to use some existing views in our application. For this purpose, we are also going to include the following view in our database.

-- Student Course View
CREATE VIEW vwStudentCourse
AS
 SELECT	s.StudentId, 
   s.FirstName, 
   s.LastName,
   sc.CourseId, 
   c.CourseName
 FROM    Student s INNER JOIN StudentCourse sc
 ON		s.StudentId = sc.StudentId 
 INNER JOIN Course c ON sc.CourseId = c.CourseId
GO

Using Entity Framework Code First Approach with Existing Database

Now, we are going to create a Console Application and then we will see how to interact with the database that we just created using the Entity Framework Code First Approach.

Open Visual Studio and Create a Console Project

First, open Visual Studio (2012\2015\2017\2019\2022) and then create a new console application with the name “CodeFirstWithExistingDB” as shown in the below image.

Adding Entity Data Model

Now, let us see the step-by-step process to understand how to use Entity Framework Code First Approach with Existing Database. To use Entity Framework Code First Approach with Existing Database, right-click on your project in Visual Studio and then select Add => New Item from the context menu as shown in the below image.

This will open the Add New Item popup window. From this Add New Item window, select Data from the left panel and then select ADO.NET Entity Data Model from the middle panel and provide an appropriate name to the Entity Data Model (I am giving the name as StudentDataModel) and click on the Add button as shown in the below image.

Selecting Entity Data Model Wizard

Once you click on the Add button in the previous step it will open the Entity Data Model Wizard with four options. They are as follows:

1. EF Designer from database: Creates a model in the EF Designer based on an existing database. You can choose the database connection, settings for the model, and database objects to include in the model. The classes your application will interact with are generated from the model. 
2. Empty EF Designer model: Create an empty model in the EF Designer as a starting point for visually designing your model. Later, you can generate a database from your model. The classes your application will interact with are generated from the model. 
3. Empty Code First model: Creates an empty Code First model as a starting point for designing your model using code. Later, you can generate a database from your model.
4. Code First from database: Creates a Code First model based on an existing database. You can choose the database connection, settings for the model, and database objects to include in the model.

As we are going to use Entity Framework Code First approach with Existing Database, so we need to select the Code First from Database option and then click on the Next button as shown in the below image.

Choose your Data Connection

Once you click on the Next button in our previous step, it will open Choose your Data Connection wizard. Here, we need to create a new connection for our database if the dropdown does not include the connection to our existing database. In order to create a database connection, simply click on the New Connection button as shown below.

Once you click on the New Connection button, it will open the Choose Data Source popup. Here, you need to choose the Microsoft SQL Server data source and click on the continue button as shown in the below image.

Once you click on the Continue button, it will open the Connection Properties window. Here, you need to provide the server name. Select the Authentication type and finally select your database name and test the connection. If everything is well and fine, then you will get a message as “Test Connection Succussed”. Finally, click on the OK button as shown below.’

Once you click on the OK button, by default it will be going to add a connection string to your app.config (in case of console or windows application) or web.config (in case of any type of web application) file. In our case, we have provided the connection string name as EF_Demo_DB_Context. If you want then you can also go with the default name. Finally, click on the Next button as shown in the below image.

Choose Entity Framework Version

Once you click on the Next button in our previous step, it will open choose your entity framework version wizard. Here, you need to select Entity Framework 6.x and click the Next button as shown below.

Note: If you already have installed the latest version of Entity Framework using NuGet manager, then this step of the wizard will no longer appear.

Choose your Database Objects and Settings

Once you click on the Next button in our previous step, it will open Choose your Database Objects and Settings wizard. This wizard will display all the Tables, and Views that are available in the database. So, select the Tables, and Views, keep the default checkboxes selected and finally click on the Finish button as shown in the below image.

Once you click on the Finish button, it will generate all the entity classes for your Database tables and views along with the context class as shown in the below image.

It will create the following context class (StudentDataModel.cs) which uses Fluent API to configure entity classes as per your database.

namespace CodeFirstWithExistingDB
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;

    public partial class StudentDataModel : DbContext
    {
        public StudentDataModel()
            : base("name=EF_Demo_DB_Context")
        {
        }

        public virtual DbSet<Course> Courses { get; set; }
        public virtual DbSet<Standard> Standards { get; set; }
        public virtual DbSet<Student> Students { get; set; }
        public virtual DbSet<StudentAddress> StudentAddresses { get; set; }
        public virtual DbSet<Teacher> Teachers { get; set; }
        public virtual DbSet<vwStudentCourse> vwStudentCourses { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Course>()
                .Property(e => e.CourseName)
                .IsUnicode(false);

            modelBuilder.Entity<Course>()
                .HasMany(e => e.Students)
                .WithMany(e => e.Courses)
                .Map(m => m.ToTable("StudentCourse").MapLeftKey("CourseId").MapRightKey("StudentId"));

            modelBuilder.Entity<Standard>()
                .Property(e => e.StandardName)
                .IsUnicode(false);

            modelBuilder.Entity<Standard>()
                .Property(e => e.Description)
                .IsUnicode(false);

            modelBuilder.Entity<Student>()
                .Property(e => e.FirstName)
                .IsUnicode(false);

            modelBuilder.Entity<Student>()
                .Property(e => e.LastName)
                .IsUnicode(false);

            modelBuilder.Entity<Student>()
                .HasOptional(e => e.StudentAddress)
                .WithRequired(e => e.Student);

            modelBuilder.Entity<StudentAddress>()
                .Property(e => e.Address1)
                .IsUnicode(false);

            modelBuilder.Entity<StudentAddress>()
                .Property(e => e.Address2)
                .IsUnicode(false);

            modelBuilder.Entity<StudentAddress>()
                .Property(e => e.Mobile)
                .IsUnicode(false);

            modelBuilder.Entity<StudentAddress>()
                .Property(e => e.Email)
                .IsUnicode(false);

            modelBuilder.Entity<Teacher>()
                .Property(e => e.FirstName)
                .IsUnicode(false);

            modelBuilder.Entity<Teacher>()
                .Property(e => e.LastName)
                .IsUnicode(false);

            modelBuilder.Entity<vwStudentCourse>()
                .Property(e => e.FirstName)
                .IsUnicode(false);

            modelBuilder.Entity<vwStudentCourse>()
                .Property(e => e.LastName)
                .IsUnicode(false);

            modelBuilder.Entity<vwStudentCourse>()
                .Property(e => e.CourseName)
                .IsUnicode(false);
        }
    }
}

App.config file:

It also adds the provider and connection string settings in the App.config file as shown below:

That’s it. We have successfully generated Context and Entity Classes from an Existing Database using Entity Framework Code-First Approach.

Using Entity Framework in Code:

Let us see a simple example of how to use the entity framework code first approach. Let us modify the main method of the Program class as shown below. Here, we need to display all the student details in the console.

using System;
using System.Collections.Generic;
using System.Linq;
namespace CodeFirstWithExistingDB
{
    class Program
    {
        static void Main(string[] args)
        {
            using (StudentDataModel DBEntities = new StudentDataModel())
            {
                List<Student> listStudents = DBEntities.Students.ToList();
                Console.WriteLine();
                foreach (Student student in listStudents)
                {
                    Console.WriteLine($" Name = {student.FirstName} {student.LastName}, Email {student.StudentAddress?.Email}, Mobile {student.StudentAddress?.Mobile}");
                }
                Console.ReadKey();
            }
        }
    }
}

Output:

Note: In the same way, you can also perform the INSERT, UPDATE, and DELETE operations.

In the next article, I am going to discuss How to Perform CUD Operations using Stored Procedures in Entity Framework Code-First Approach with Examples. Here, in this article, I try to explain How to Generate Context and Entity Classes from an Existing Database using Entity Framework Code-First Approach with Examples. I hope you enjoyed this Generate Context and Entity Classes from an Existing Database using Entity Framework Code-First Approach article. Please give your valuable feedback and suggestions about this article.