Using Entity Framework In ASP.NET Web API

Here, I’m continuing with ASP.NET Web API. You can follow my ASP.NET Web API series from the following links –

In this series of articles, we’ll work with the entity framework, which is an Object/Relational Mapping (ORM) framework, that is enabled for us to work with the relational database. So this framework has many features such as performance, maintainability and productivity etc.

Hence, let’s start creating ASP.NET Web API with the entity framework. In this, I will be using a three tier application, so we need to add three projects in my solution- one is Service layer, the second is data layer, and the last one will be a Web API project. Here, I’m using Visual Studio 2013.

Let’s create a project for Web API. Open Visual Studio 2013, go to new and choose the project.

Expand Installed => Templates => Visual C# and choose an ASP.NET Web Application from the menu, give a name to your Web API project with which you want to do work and finally click “OK” button.

Select a template. I am going to create a Web API. Therefore, select Web API template from the list of the templates. Click OK button to create a Web API project, including MVC features.

Our Web API project has been created. Now, I need to add a service layer project in my solution. Go ahead and right click onto solution and a new project.

Choose ASP.NET Web Application from the menu, give it a name and finally create it by pressing OK button.

This figure is for the templates. In the Service layer project we’ll add a class and an interface. Hence, select an empty template and click the OK button to create it.

In the following screenshot, our two projects have been created. In the end, I need to add a project, which will be responsible for our data.

Create a ASP.NET Web Application for the Data layer and give it a name; whatever you want to do.

Select an empty template for our project, create a project by hitting OK button.

Hence, I have now three projects in my solution, shown below:

First of all, I want to take entity framework's latest version from the NuGet Package Manager. For this, right click on the references in the Data layer. Test the project and select manage NuGet Packages.

Here, I have the entity framework. If you don’t find it easily, you can searc for it from the top right search box. Click the install button to install it.

Hence, the latest version of entity framework is installing.

You can see in the screenshot, that we’ve a Green check mark.

We have installed the entity framework to our selected project.

The DataLayerTest project also has a reference of System.Data.Entity. Right click the references and add a reference.

Check System.Data.Entity from the menu namespaces and click OK button to add it.

All is set for the DataLayerTest project. Now, our Service layer is dependent on the data project, as you know if you have ever worked with the three tier architecture. Right click references under ServiceLayerTest project and add a reference.

Scroll the solution and select the projects. You’ll find DataLayerTest project. Check it and add it to the Service Layer by hitting OK button.

Screenshot shows I’ve added a reference of DataLayerTest in the ServiceLayerTest project.

Afterwards, I need to add an entity framework from NugGet Package Manger in the ServiceLayerTest project. Right click the references and add NuGet.

Click the Install button to install it.

Here an entity framework is installing.

Entity framework is installed successfully, as shown in following screenshot:

In the solution, as you can see, I have a project of Web API, which is dependent on the Data layer and Service layer. Add a service for this.

Check both the layers and click OK button to add it in the Web API project.

As you can see, DataLayerTest and ServiceLayerTest reference have been added successfully into our WebAPITest project.

Now, I need to create a database. Our Data layer will be responsible for an access to the database, which I’ll create. Just open Microsoft SQL Server and connect it. Here, I’m using SQL Server 2014.

Create a database by right clicking on the databases folder and select create a new database.

Give a name to your database. I have given it a name; Employee, click the OK button to create it.

In the database, we need to create a table so right click on the tables under your database and create a table.

Here, I have five columns in my table.

Set Employee ID as a primary key, right click EmpId and select set the primary key option.

All is set, now save your table.

Give a name to your table. Here, I given it Employee, as shown below:

You must fill some details in the table which we can use in our Service layer to show the data, as we expected in the end on the Browser. We can call the methods in our project with these details.

Here, I have two employees' IDs with their details.

Now, I have to add an edmx file in the Data layer to access the database which I’ve just created. Right click the Data layer test project and a new item.

In the Data panel, choose ADO.NET Entity Data Model and give a name to it.

Choose EF Designer from the database model and click Next button.

Click the new connection string to retrieve it from the database.

After clicking the connection string, you have to choose SQL remote Server name from the dropdown list and choose an appropriate name.

After selecting the Server name, the database can be accessible, give authentication detail and select a database which you’ve created.

After completing all the necessary details, click OK button to generate a database.

After clicking the next button, you will see the table which you’ve created in the SQL Server 2014.

Table is listed here in the following screenshot; select and add the table as an entity. I have only one table that is Employee; select it and click finish.

Now, this is our Employee entity.

Add a folder in ServiceLayerTest project. In this folder, we’ll create a class and an interface.

Give a name to your folder, I have given it Tests. Hence, in this folder, let's create a class.

Give a name to your class, add it by clicking the add button.

Your class is ready, where name is EmployeTest.

Further, I need to add an Interface along with the class within the Tests folder. Right click Tests folder and choose an interface.

Give a name to your Interface and click OK button to add it.

First of all, add the methods in the IEmployeTest interface that we have implemented in the EmployeTest. Hence, get a list of Employees as a GetEmployes and get a product by passing EmpId name. An interface will be public for access to all.

We have to add a namespace for the Employee, right click near list of Employees and resolve it by DataLayerTest namespace.

using DataLayerTest;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ServiceLayerTest.Test {
public interface IEmployeTest {
List < Employe > GetEmployes();
Employe GetEmploye(int empID);
}
}

In EmployeTest class, I need to add two methods according to our database table. Therefore, our EmployeTest class will inherit from the Interface. Hence, go ahead and implement IEmployeTest interface and also we have to resolve it.

After implementing the interface, you’ll just get two methods in this class, where one is for the list of employees and another is for only one employee. Now, create a object for the model. Get EmployeeEntites which is our context.

I need to resolve our context (EmployeeEntites) using DataLayerTest namespace.

Let’s create an object for the context by which, we can return a list of employees, using GetEmployes() method.

namespace ServiceLayerTest.Test {
public class EmployeTest: IEmployeTest {
private EmployeeEntities db = new EmployeeEntities();
public List < DataLayerTest.Employe > GetEmployes() {
return db.Employes.ToList();
}
public DataLayerTest.Employe GetEmploye(int empID) {
throw new NotImplementedException();
}
}
}

Now, all is set in the Service layer test project. Our class is ready and our interface is ready as well. Now I'm going to work on Web API controller. In the Web API project, I have three controllers by default. Hence, just delete it. I want to create a new controller.

For creating a new controller, click on the controller’s folder under WebAPITest project and add a new controller.

Choose Web API 2 Controller. Empty it from the controllers list and add it.

Give a name to your controller. Here, I am giving it Employee.

First of all, we need an instance of IEmployeTest interface and click to resolve it to from Service layer.

Afterwards, I want to get IEnumerable list of Employees from the Get() method. I need to add a namespace of Employee list.

Finally, I want to call GetEmployes() method with the help of empTest.

namespace WebAPITest.Controllers {
public class EmployeController: ApiController {
private IEmployeTest empTest = new EmployeTest();
public IEnumerable < Employe > Get() {
return empTest.GetEmployes();
}
}
}

Now, I need to create a connection string in WebConfig file of my WebAPITest project. In the WebConfig of Data layer test, you can get a connection string. If you don’t want to write hardcode this connection string in the WebAPITest project’s WebConfig, then you can copy from here.

Paste this copied connection string in the WebConfig of WebAPITest project.

Now, debug and run your project by clicking F5. Call your controller in the Browser Window’s URL. Hence, we successfully got our ID result as XML representation of my data.

Now, I want to get Employe by ID. Hence for this, go ahead to EmployeTest class and make some changes. Hence, return employees, using lambda expression,which means, if there are multiple rows for that this ID will take first row, if there is no record; it will return null.

using DataLayerTest;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace ServiceLayerTest.Test {
public class EmployeTest: IEmployeTest {
private EmployeeEntities db = new EmployeeEntities();
public List < Employe > GetEmployes() {
return db.Employes.ToList();
}
public Employe GetEmploye(int empID) {
return db.Employes.Where(x => x.EmpID == empID).FirstOrDefault();
}
}
}

We need to make some changes in the controller as well. I have to implement the GetEmploye() method here, where we can get employee by EmpID.

using ServiceLayerTest.Test;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
namespace WebAPITest.Controllers {
public class EmployeController: ApiController {
private IEmployeTest empTest = new EmployeTest();
public IEnumerable < Employe > Get() {
return empTest.GetEmployes();
}
public Employe GetEmploye(int id) {
return empTest.GetEmploye(id);
}
}
}

Now all is set, save your project and run it by clicking F5. I want to return employee by Empid so pass the ID such as one in the URL, so we can see the EmpId 1.

Similarly, pass EmpID 2 in URL, which is held by Employee 2, as expected, we got Employee 2 in XML representation.

If I pass something else in URL such as id=22, then we get nothing, because of which, we called null value, that’s it.

That’s it for the Web API, using the entity framework. Thanks for reading this article. Stay tuned with us for more on ASP.NET Web API.

14 Comments

Join the conversation! Your thoughts help the community grow.

Mark Key10y ago
Very good article. Thanks for covering EF Database-first!
Rajeev Punhani10y ago
Nice Article.
Santhakumar Munuswamy10y ago
Good work. Keep it up
Amit Mishra10y ago
thanks to all
Pradeep Sahoo10y ago
Nice share ...very helpful for beginners
Kuppurasu Nagaraj10y ago
Nice Sharing..
Prasanna Murali10y ago
Nice one....
Vignesh Mani10y ago
nice
Gowtham K10y ago
Good One, Thanks for sharing:)
Debasis Saha10y ago
Good One..
Manas Mohapatra10y ago
Screenshots are more helpful for Beginners.
Bhavik Patel10y ago
good
Raja10y ago
Good one...
kalu singh rao10y ago
Nice...