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.

 

 

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.

 

 

Ensure that the reference has been successfully added into the DataAccessLayer.

 

 

Step 3

 

This is a crucial step, we are going to map DB into DataAccessLayer. Do as suggested in the following image.

 

 

Step 3.1

 

Select Ado.Net Entity Data Model from available options. Name it as per your desire.

 

 

 

Step 4

 

Now select EF Designer from database options.

 

 

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. 

<connectionStrings>
  <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" />
</connectionStrings>

 

Step 6

 

Select what would you like to map into DB. Tables, Views, Stored Procedures, etc.

 

 

Let it load then you will be able to see the following screen:

 

 

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.

 

 

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.

 

 

Let's see what data we have in our EmployeeDB database.

 

 

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:

using (var db = new EmployeeDBEntities())
{
    var query = from emp in db.Employees
                orderby emp.Salary
                select emp;

    Console.WriteLine("Employess as sorted as per their Salary");

    foreach (var item in query)
    {
        Console.WriteLine("Name: "+item.FirstName + ",     Salary: " + item.Salary);
    }
 }

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.

 

 

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.

 

 

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:

 

 

If the server has user credentials then:

 

 

 

catalog = DBname:

 

 

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 @

• Twitter
  
• LinkedIn
• GitHub