Entity Framework DBFirst Approach - Step-By-Step Guide

Introduction

 
Most of us are familiar with the EF: Entity Framework. It is an ORM tool used as a:
  • DDL
    Data Definition Language to perform (CREATE, ALTER, DROP => Table)

  • DML
    Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE => Records inside a table)
 When one has good knowledge of SQL or RDBMS, they most likely follow the DBFirst approach.
  • DBFirst
    One follows this approach when he has a clear understanding of the requirements of the project in the beginning & starts creating DB as per requirements. The DBFirst approach creates an entity framework from an existing database.
  • CodeFirst
    One follows this approach when he does not have a clear understanding of the requirements of the project in the beginning & starts creating C# classes first as he gets new requirements and connect the dots looking forward. The CodeFirst approach creates model Classes, properties, DbContext etc. first, then create a new database or updated database based on these models/entities and their relationships.
In this article, we are going to focus on the DBFirst approach and how to configure an existing DB in your ongoing project.
 
Step 1
 
Say you have a Presentation layer, It could WPF or Website or Console application. Now we have to integrate DB into our project. It is always a good practice to keep the DB layer separate from the presentation layer so that these 2 modules maintain loose coupling.
 
So go ahead and add a new DLL in your project. Name that class library DataAccessLayer.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Step 2
 
Now we need to add Entity Framework's reference into DataAccessLayer: Right Click on DataAccessLayer -> Click on Manage Negue Packages and search for Entity Framework, then hit Install.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Ensure that the reference has been successfully added into the DataAccessLayer.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Step 3
 
This is a crucial step, we are going to map DB into DataAccessLayer. Do as suggested in the following image.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Step 3.1
 
Select Ado.Net Entity Data Model from available options. Name it as per your desire.
 
Entity Framework DBFirst Approach Step By Step Guide 
 
Step 4
 
Now select EF Designer from database options.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Step 5
 
This is where you will get ConnectionString. Check: Save ConnectionString in App.config: This will automatically create <connectionStrings> tag in App.config
 
This will also create an EmployeeDBEntities class under models.
 
EmployeeDBEntitie
 
This class exposes DbSet properties that represent collections of entites. Entites are nothing but tables from DB. 
  1. <connectionStrings>    
  2.   <add name="EmployeeDBEntities" connectionString="metadata=res://*/EmployeeModel.csdl|res://*/EmployeeModel.ssdl|res://*/EmployeeModel.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=EmployeeDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />    
  3. </connectionStrings>     
Entity Framework DBFirst Approach Step By Step Guide
 
Step 6
 
Select what would you like to map into DB. Tables, Views, Stored Procedures, etc.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Let it load then you will be able to see the following screen:
 
Entity Framework DBFirst Approach Step By Step Guide
 
Step 7
 
We are going to use this data inside the Presentation module. In order to have access to their classes, we need to add a reference of DataAccessLayer to Presentation Layer.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Step 7
 
Now the presentation module is dealing with data which is coming from DataAccessLayer. Now even the Presentation module needs to have an Entity framework installed to process data.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Let's see what data we have in our EmployeeDB database.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Let's try to fetch these records from DB and try to display it in the output window. which will confirm if we have successfully configured DBFirst.
 
Following code is written in a presentaion module (Entry point of the application)
 
Fetching all employees and sorting them as per their salaries:
  1. using (var db = new EmployeeDBEntities())  
  2. {  
  3.     var query = from emp in db.Employees  
  4.                 orderby emp.Salary  
  5.                 select emp;  
  6.   
  7.     Console.WriteLine("Employess as sorted as per their Salary");  
  8.   
  9.     foreach (var item in query)  
  10.     {  
  11.         Console.WriteLine("Name: "+item.FirstName + ",     Salary: " + item.Salary);  
  12.     }  
  13.  } 
Now let's run the project. We encountered an error. Specifying that we need to add Connection string into Presentation module's App.config as well.
 
Entity Framework DBFirst Approach Step By Step Guide
 
Solution
 
Copy ConnectionStrings tag from DataAccessLayer's App.config and paste inside Presentation module's App.config.
 
Once you're done with that, try to run the project again.
 
Entity Framework DBFirst Approach Step By Step Guide
 
There you go, we have output as expected, all the employees sorted as per the lowest to highest salaries.
 
Congratulations, you have successfully configured DBFirst into your project. Now we can perform all kinds of data manipulations using entity framework.
 
The following are a few things you should know while mapping the connection string.
 
If your server has windows authentication then:
 
Entity Framework DBFirst Approach Step By Step Guide
 
If the server has user credentials then:
 
Entity Framework DBFirst Approach Step By Step Guide 
 
catalog = DBname:
 
Entity Framework DBFirst Approach Step By Step Guide
 
Well, that's all for today.
 

Conclusion

 
I hope this article is capable enough to give you everything you need for the DBFirst approach configuration.
 
We learned
  • How to add exiting DB into the project
  • How to add libraries & project references.
  • How to figure out different properties in the connection string.
Thank you all.
 
Feel free to connect @