Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

MVC 3 Project

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.

Entity Data Model

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.

Entity Framework Step 1

AdventureWorks is already selected. You may need to create a New Connection if it is not available in the drop down list. Click Next.

Entity Framework Select Data Source

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.

Entity Framework Select Database Objects

The edmx file is created with the tables and their relationships (primary and foreign keys) are set up.

EDMX File Table Relations

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.

Adding the DbContext

Select EF 4.x DbContext Generator from the list of online templates available and click Add.

Entity Framework DbContext Generator

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 Controller

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 = new 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.

Add ViewModel

In the pop up, accept the defaults and click Add.

View Model Definition

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.

Page Test

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;

   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.

MVC Entity Data Model with Custom Class