MVC 3 ASP .Net Site Using Entity Framework with LINQ
Working with Entity Framework in MVC 3
This article describes how to implement the Entity Framework in an ASP .Net MVC 3 Project. I will be adding the Entity Data Model to the project and will use the DbContext Code Generator to create a strongly typed class to retrieve data from a single table. I will also use a custom class and LINQ to query multiple tables from the DbContext Code.
Creating the MVC 3 Project for Entity Framework
Open Visual Studio 2010 and go to File, New Project and select the ASP .Net MVC 3 Web Application.
Adding the Entity Data Model
Right click on the Models Folder and select Add, New Item and select ADO.Net Entity Data Model. If it is not in the list under Visual C#, click on the Online Templates to find it. It will need to install when you click Add to create the Entity Data Model file.
This article will use a Entity Data Model (edmx file) created from the AdventureWorks database. Generate from Database is selected and we click Next to proceed.
AdventureWorks is already selected. You may need to create a New Connection if it is not available in the drop down list. Click Next.
The next step will be to select from the list of database objects. Employee, EmployeeAddress and Contact have been selected from the list. Click Next at the bottom of the pop up box.
The edmx file is created with the tables and their relationships (primary and foreign keys) are set up.
If we look at the EmployeeData.Designer.cs file created for the Entity Data Model, a class and constructors are created using the ObjectContext.
public partial class AdventureWorksEntities : ObjectContext
Adding the DbContext Class for the Entity Framework
The ViewModel and Controller for the MVC 3 project will require the data to be returned as a List or IEnumerable data. Adding a DbContext Class to the project based on the tables in the Entity Data Model will provide the means to deliver the data to the ViewModel. By Right Clicking on the Entity Data Model and selecting Add Code Generation Item, we can select the DbContext Generator.
Select EF 4.x DbContext Generator from the list of online templates available and click Add.
The class created implements the DbContext class.
public partial class AdventureWorksEntities : DbContext
Adding the Controller to Retrieve Data from the Entity Data Model
Without any additional code added at this point, a Controller can be created.
Right click the Controllers folder, select Add Controller. Leave the Template as an Empty Controller and click Add.
Add the following code to the Index method of the EmployeesController. It will create a variable based on the AdventureWorksEntities class created by the DbContext Generator, add a List object that will accept the Employees and return them to the ViewModel to be created.
namespace EntityFrameworkTest.Controllers
{
public class EmployeesController : Controller
{
public ActionResult Index()
{
var db = new AdventureWorksEntities();
List
vwContacts = db.Employees.ToList();
return View(vwContacts);
}
}
}
Adding the ViewModel to Display Data from the Entity Data Model
Right click the code for the controller on the word Index and select Add View.
In the pop up, accept the defaults and click Add.
Add the code to the ViewModel to modify the model type to Employee from AdventureWorksEntities to Employee and add case it to the IList type. Add the table and foreach loop to get the EmployeeID and Title from the Employees table returned to the page.
@model IList<EntityFrameworkTest.Models.Employee>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<table>
<tr>
<th>EmpID</th>
<th>Title</th>
</tr>
@foreach (var item in Model) {
<tr>
<td>@Html.DisplayFor(model => item.EmployeeID)</td>
<td>@Html.DisplayFor(model => item.Title)</td>
</tr>
}
</table>
Test the page to see that the Employee data was returned in the ViewModel.
Creating a Strongly Typed Class and Using LINQ to Retrieve Data from Entity Data Model
The code created so far is fine, if you only want data from a single table. If I need a set of data from multiple tables, I could have created a View and pointed to that to return the Employee ID and Title from the Employees table with the related First and Last Names of the Employees from the Contact table. Or, I can create a custom class that uses a LINQ statement joining the 2 tables using an AdventureWorksEntity object from my DbContext class. I can then create a Controller that will use my custom class to return the data to a View that receives a model also of the enumerated custom class.
First, code for the custom class. I want to return the EmployeeID, Title, FirstName, and LastName from the Entity Data Model, so I have public fields to get and set the data for each. I create a function that returns an enumerated collection of my class. The function, GetMyData(), creates an AdventureWorksEntities object which is used in my LINQ query. Note the class name in the select statement and the assignment of the LINQ query data to the objects in the class. This is how the data will be returned to the calling Controller and finally as the model in the View.
namespace EntityFrameworkTest.Models
{
public class EmpContact
{
public int EmployeeID { get; set; }
public string Title { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
//strongly typed set of data from the EF using Linq
public System.Collections.Generic.IEnumerable<EmpContact> GetMyData()
{
AdventureWorksEntities db = new AdventureWorksEntities();
var newemps = from p in db.Employees
join c in db.Contacts
on p.ContactID equals c.ContactID
select new EmpContact
{
EmployeeID = p.EmployeeID,
Title = p.Title,
FirstName = c.FirstName,
LastName = c.LastName
};
return newemps;
}
}
}
Add the Controller to the Project as an Empty Controller. Add code to create an EmpContact object (the class that implements the DbContext Object) and an IEnumerable object. Set the IEnumerable object to the function GetMyData and return this IEnumerable object to the View.
public class EmployeeContactController : Controller
{
public ActionResult Index()
{
EmpContact em = new EmpContact();
IEnumerable
vwList = (em.GetMyData().AsEnumerable
return View(vwList);
}
}
Create the View and modify the code to add the IEnumerable type to cast the EmpContact model. Add the table and the foreach loop to retrieve the data from the model.
@model IEnumerable<EntityFrameworkTest.Models.EmpContact>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Employee Contact Data</h2>
<table>
<tr>
<th>EmployeeID</th>
<th>Title</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
@foreach (var item in Model) {
<tr>
<td>@Html.DisplayFor(model => item.EmployeeID)</td>
<td>@Html.DisplayFor(model => item.Title) </td>
<td>@Html.DisplayFor(model => item.FirstName) </td>
<td>@Html.DisplayFor(model => item.LastName) </td>
</tr>
}
</table>
Run the application to test it.
