Entity Framework 6.0.0 Using Stored Procedure In ASP.NET MVC

Introduction

Entity Framework is an Object Relational Mapping (ORM) framework. ORM framework automatically makes classes based on database tables, and makes database tables based on classes. That is, it can also automatically generate necessary SQL to create database tables based on classes.

Patterns In Entity Framework

Data providers are source specific providers which abstract the ADO.NET interfaces to connect to the database when programming against the conceptual schema. It translates the common SQL languages such as LINQ via command tree to native SQL expression and executes it against the specific DBMS system. Entity Client layer exposes the entity layer to the upper layer. Entity client provides the ability for developers to work against entities in the form of rows and columns using Entity SQL queries without the need to generate classes to represent conceptual schema. Entity Client shows the entity framework layers which are the core functionality. These layers are called as Entity Data Model.

The Storage Layer contains the entire database schema in XML format.

The Entity Layer which is also an XML file defines the entities and relationships.

The Mapping layer is an XML file that maps the entities and relationships defined at conceptual layer with actual relationships and tables defined at logical layer.

The Metadata services which are also represented in Entity Client provide centralized API to access metadata stored Entity Mapping and Storage layers.

Object Services layer is the Object Context that represents the session of interaction between the applications and the data source. The main use of the Object Context is to perform different operations like add, delete instances of entities and to save the changed state back to the database with the help of queries. It is the ORM layer of Entity Framework that represents the data result to the object instances of entities. This service allows developers to use some of the rich ORM features like primary key mapping, change tracking, etc. by writing queries using LINQ and Entity SQL.

Entity Framework has the following three approaches,

1. Schema First Approach
2. Model First Approach
3. Code First Approach

Schema First Approach

It can automatically generate necessary SQL to create database tables based on classes. Entity Framework can do all of the above automatically, if we provide it with the database schema.

The Database First Approach provides an alternative to the Code First and Model First approaches to the Entity Data Model.

It creates model codes like classes, properties, DbContext etc. from the database in the project and those classes become the link between the database and controller. The Database First approach creates the EF from an existing database. We use all the other functionalities, such as the model/database sync and the code generation, in the same way we used them in the Model First approach.

Model First Approach

In the Model First Approach, we first create the Entity Model.

1. Entities
2. Relationships between entities
3. Inheritance hierarchies etc.

We implement all this directly on the design surface of the EDMX file.

The model is stored in an EDMX file and can be viewed and edited in the Entity Framework Designer. In Model First, you define your model in an Entity Framework designer then generate SQL, which will create database schema to match your model and then you execute the SQL to create the schema in your database. The classes that you interact with in your application are automatically generated from the EDMX file.

Code First Approach

Code-first approach allows us to create the custom classes first and based on these custom classes entity framework can generate database automatically for us successfully. Code First adds a model builder that inspects your classes that the context is managing, and then uses a set of rules or conventions to determine how those classes and the relationships describe a model, and how that model should map to your database. Code First has the ability to use that model to create a database if required. It can also update the database if the model changes, using a feature called Code First Migrations.

What is a EDMX

An .edmx file is an XML file that defines a conceptual model, a storage model, and the mapping between these models. An .edmx file also contains information that is used by the ADO.NET Entity Data Model Designer (Entity Designer) to render a model graphically.

Transaction

Whenever you run SaveChanges() to insert, update or delete the database, the framework will put that operation in a transaction. When you invoke SaveChanges, the context automatically starts a transaction and commits or rolls it back. Starting a transaction requires that the store connection is open. So calling "Database.BeginTransaction()" will open the connection if it is not already opened. If DbContextTransaction opened the connection, then it will close it when Dispose() is called.

Types of Entity Framework

In Entity Framework, there are two types of entities that use their own custom data classes together with data model without making any modifications to the data classes themselves.

• POCO entities
• Dynamic Proxy

POCO Entities

• POCO stands for "plain-old" CLR objects. POCO data classes which are mapped to entities are defined in a data model.
• It also supports most of the same query, insert, update, and delete behaviors as entity types that are generated by the Entity Data Model tools.

Dynamic Proxy

While creating instances of POCO entity types , the Entity Framework often creates instances of a dynamically generated derived type that acts as a proxy for the entity. IT can also be said that it is a runtime proxy classes.

• You can override some properties of the entity for performing actions automatically when the property is accessed.
• This technique used to those models which are created with Code First and EF Designer.

Relationships In Entity Framework

There are three types of Relationships,

• One-to-Many Relationship
• Many-to-Many Relationship
• One-to-One Relationship

One-to-Many Relationship

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table X can have many matching rows in table Y, but a row in table Y can have only one matching row in table X. The foreign key is defined in the table that represents the many ends of the relationship.

Many-to-Many Relationship

In many-to-many relationship, a row in table X can have many matching rows in table Y, and vice versa. You can create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table X and table Y.

One-to-One Relationship

In a one-to-one relationship, a row in table X can have no more than one matching row in table Y, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints. In a one-to-one relationship, the primary key acts additionally as a foreign key and there is no separate foreign key column for either table.

DbContext In Entity Framework

Multiple DbContext was first revealed in Entity Framework 6.0. Multiple context classes may belong to a single database or two different databases.

Steps to create a simple application

Step 1

First create an MVC application named “MvcApplication2”.


Step 2

Then, create one table named “tblCustomers”.

Script for tblCustomers 

CREATE TABLE [dbo].[tblCustomers](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [Country] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Enter some dummy records into this table.

Step 3

Then, create one stored procedure named “Sp_SearchCustomers”.

Script for Sp_SearchCustomers 

CREATE PROCEDURE Sp_SearchCustomers
@City NVARCHAR(70)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT * FROM tblCustomers
      WHERE City LIKE '%' + @City + '%'
END

Here, I filtered the records based on City Column values.

Now, execute this stored procedure to filter records based on City Column values. 

exec Sp_SearchCustomers 'Bangalore'
exec Sp_SearchCustomers 'Bhubaneswar'

Step 4

Follow these steps to create Entity Data Model.

So, create an ADO.NET Entity Data Model named “satyamodel.edmx”.

Then

Then, create a new connection using your SQL Server name and credentials using database name. You should select database where you created earlier created table and sp.

Now, select this table name as mentioned above.

You should now have the Entity Data Model ready with the tblCustomers Table.

Once the Entity Framework is configured, the next step is to import the Stored Procedure in the Entity Framework model. In order to do so, you will need to Right Click the Entity Model and select Update Model from Database option as shown below.

The above step will open the Update Wizard where you will need to select the above mentioned Stored Procedure and click Finish.

Now we will need to import the Stored Procedure into the Entity Framework so that it can be called as a Function using Entity Framework. Thus you will need to Right Click the Entity Model, click Add and then click Function Import.

This will open the Add Function Import dialog window. Here first you need to specify the Function Import Name which is the name of the function used to call the Stored Procedure and then select the Stored Procedure that will be executed when the function is called. The Return Type is selected as Entities which is the Customer Entity class.

Here the function import name is “SearchCustomers” and select your appropriate sp name in stored procedure/function name section. In Returns a collection of section , select your appropriate table name in entities selection option.

Click on Get column Information button in stored procedure/function column information section , It will show all related columns biodata as used in table and stored procedure.

After completed all above steps successfully , You can see the structure of “satyamodel.edmx” in your application as all code and related class file auto generate by system regarding connection properties and entities of Table and Stored procedure.

Also you can see the changes of some entity framework configuration related information made in “Web.Config” file.

Step5

In “packages.config” , Check your Entity Framework Version Information.

Step6

Create a controller class file named “HomeController.cs”.

Code Ref 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcApplication2.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            SatyaEntities entities = new SatyaEntities();
            return View(entities.SearchCustomers(""));
        }

        [HttpPost]
        public ActionResult Index(string City)
        {

            //if (Request.Form["text"] == null)
            //{
            //    TempData["SelectOption"] = -1;
            //}
            SatyaEntities entities = new SatyaEntities();
            return View(entities.SearchCustomers(City));
        }

    }
}

Code Description

The Controller consists of two Action methods.

Action method for manage GET operation.

Inside the Index Action method, the Stored Procedure is called using the SearchCustomers function created using the Function Import procedure done earlier.

The parameter City is passed as empty string and hence it gets all records from the tblCustomers table.

Finally the list of Customers Entity is returned to the View. 

public ActionResult Index()
  {
      SatyaEntities entities = new SatyaEntities();
      return View(entities.SearchCustomers(""));
  }

Action method for manage POST operation.

When the Form is submitted, the value of the City Name TextBox is submitted this Action method and the value is passed as parameter to the SearchCustomers function and the list of tblCustomers Entity is returned to the View. 

[HttpPost]
public ActionResult Index(string City)
{

    //if (Request.Form["text"] == null)
    //{
    //    TempData["SelectOption"] = -1;
    //}
    SatyaEntities entities = new SatyaEntities();
    return View(entities.SearchCustomers(City));
}

Here I added some commented code for City name textbox validation if it was empty. 

//if (Request.Form["text"] == null)
   //{
   //    TempData["SelectOption"] = -1;
   //}

Here textbox type is “text” as mentioned in view.

if (Request.Form["text"] == null)

Here “SatyaEntities” the name of the entity when creation of “Ado.net entity data model” i.e. “satyamodel.edmx”

Step7

Create a view file named “Index.cshtml”.

Code Ref 

@model IEnumerable<MvcApplication2.tblCustomer>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style>
        table {
            font-family: arial, sans-serif;
            border-collapse: collapse;
            width: 100%;
        }

        td, th {
            border: 1px solid #dddddd;
            text-align: left;
            padding: 8px;
        }

        tr:nth-child(even) {
            background-color: #dddddd;
        }

        .button {
            background-color: #4CAF50;
            border: none;
            color: white;
            padding: 15px 32px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 4px 2px;
            cursor: pointer;
        }

        .button4 {
            border-radius: 9px;
        }

        input[type=text], select {
            width: 60%;
            padding: 12px 20px;
            margin: 8px 0;
            display: inline-block;
            border: 1px solid #ccc;
            border-radius: 4px;
            box-sizing: border-box;
        }
    </style>
    </head>
    <body>
        <strong style="background-color: Yellow;color: Blue; text-align: center; font-style: oblique">SATYA'S ENTITY FRAMEWORK SEARCH BY CITY</strong>
        <fieldset>
            <legend style="font-family:Arial Black;color:blue">SEARCH BY INDIAN METRO CITY</legend>
            @using (Html.BeginForm("Index", "Home", FormMethod.Post))
        {
            <div align="center">
                    @Html.TextBox("City", "", new { placeholder = "Enter Your City Here...." })
                    <input type="submit" class="button button4" value="Search" />
                </div>
            <br />
            <br />
            <table align="center" border="1" cellpadding="4" cellspacing="4">
                    <tr>
                        <th style="background-color: Yellow;color: blue">Customer ID</th>
                        <th style="background-color: Yellow;color: blue">Customer Name</th>
                        <th style="background-color: Yellow;color: blue">City</th>
                        <th style="background-color: Yellow;color: blue">Country</th>
                    </tr>
                    @foreach (MvcApplication2.tblCustomer customer in Model)
                {
                    <tr>
                            <td>@customer.ID</td>
                            <td>@customer.Name</td>
                            <td>@customer.City</td>
                            <td>@customer.Country</td>
                        </tr>
                }
                </table>
        }
            </fieldset>

              @*@if (TempData["SelectOption"] != null)
              {
                <script type="text/javascript">
                        alert("Enter Only One City To Filter Records");
                </script>
              }*@

            <footer>
                <p style="background-color: Yellow; font-weight: bold; color:blue; text-align: center; font-style: oblique">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
            </footer>
</body>

</html>

Code Description

Inside the View, in the first line the tblCustomer Entity is declared as IEnumerable which specifies that it will be available as a Collection.

@model IEnumerable<MvcApplication2.tblCustomer>

The View consists of an HTML Form which has been created using the Html.BeginForm method with the following parameters.

@using (Html.BeginForm("Index", "Home", FormMethod.Post))

ActionName – Name of the Action. In this case the name is Index.

ControllerName – Name of the Controller. In this case the name is Home.

FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.

Inside the View, a TextBox is created using the Html.TextBox HTML Helper function and there is a Submit button which when clicked, the Form gets submitted. 

<div align="center">
             @Html.TextBox("City", "", new { placeholder = "Enter Your City Here...." })
             <input type="submit" class="button button4" value="Search" />
</div>

For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the tblCustomer records.

@foreach (MvcApplication2.tblCustomer customer in Model)

Here I have applied some css style sheet for design Table whole. 

table {
          font-family: arial, sans-serif;
          border-collapse: collapse;
          width: 100%;
      }

      td, th {
          border: 1px solid #dddddd;
          text-align: left;
          padding: 8px;
      }

      tr:nth-child(even) {
          background-color: #dddddd;
      }

Here I have applied some css style sheet for design Button whole. 

.button {
            background-color: #4CAF50;
            border: none;
            color: white;
            padding: 15px 32px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 4px 2px;
            cursor: pointer;
        }


.button4 {
            border-radius: 9px;
        }

Here I have applied some css style sheet for design TextBox whole. 

input[type=text], select {
            width: 60%;
            padding: 12px 20px;
            margin: 8px 0;
            display: inline-block;
            border: 1px solid #ccc;
            border-radius: 4px;
            box-sizing: border-box;
        }

I added some placeholder in textbox.

@Html.TextBox("City", "", new { placeholder = "Enter Your City Here...." })

Apply css in button control.

<input type="submit" class="button button4" value="Search" />

Mention some table header names. 

<tr>
 <th style="background-color: Yellow;color: blue">Customer ID</th>
 <th style="background-color: Yellow;color: blue">Customer Name</th>
 <th style="background-color: Yellow;color: blue">City</th>
 <th style="background-color: Yellow;color: blue">Country</th>
</tr>

I added one foreach loop will be executed over the Model which will generate the HTML Table rows with the tblCustomer records using variable customer.

Using variable customer we can access all entity property values. 

@foreach (MvcApplication2.tblCustomer customer in Model)
                {
                    <tr>
                            <td>@customer.ID</td>
                            <td>@customer.Name</td>
                            <td>@customer.City</td>
                            <td>@customer.Country</td>
                    </tr>
                }

I added here some commented code for validation if empty city name textbox. 

@*@if (TempData["SelectOption"] != null)
              {
                <script type="text/javascript">
                        alert("Enter Only One City To Filter Records");
                </script>
              }*@

This one related to controller class file related code. 

//if (Request.Form["text"] == null)
            //{
            //    TempData["SelectOption"] = -1;
            //}

As per your requirement you can uncomment and add.

Step8

Set start page during first time load of your UI.

Code Ref 

public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
            )
}

Code Description

Here name of the controller name is : “Home” .

Here name of the controller action method name is : “Index” .

OUTPUT

The URL is : http://localhost:52827/Home/Index

Here, the name of the controller is : “Home” .

Here, the name of the controller action method is : “Index” .

Here, I searched for Bhubaneswar City related records.

Here, I searched for Bangalore City related records.

Here, I searched for invalid City Name by putting USA.

In footer section, it is showing Today’s Date Time.

SUMMARY

In this article, we learned the following. 

1. What Entity Framework is.
2. Different parts of Entity Framework.
3. How to create Ado.net Entity Data Model in Entity Framework.
4. How to implement Entity Framework using Stored Procedure in Asp.net MVC Application Real Time Scenario.