Entity Framework - Basic Guide To Perform Fetch, Filter, Insert, Update And Delete

Previously, I wrote an article regarding how to perform basic database operations using LINQ to SQL here: Basic Guide To Perform Fetch, Filter, Insert, Update And Delete.

In this article, we’re going to see the basics on how to work with data using MS Entity Framework with practical examples in ASP.NET Web Forms. If you are looking for examples in ASP.NET MVC, then head over to my series of articles about: Building Web Application Using Entity Framework And MVC 5.

Or download my eBook here: ASP.NET MVC 5: A Beginner’s Guide

As an overview, ADO.NET Entity Framework (EF) is an object-relational mapping (ORM) framework for the .NET Framework. EF enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

• Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
  
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
  
• Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
  
• Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
  
• Multiple conceptual models can be mapped to a single storage schema.
  
• Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

For details about ADO.NET Entity Framework, see: The ADO.NET Entity Framework Overview

Let’s Get Started!

Now, let’s go ahead and fire up Visual Studio and then select File -> New Project -> Visual C# -> Web - ASP.NET Web Application. Enter the name of the application as you would please, and then click OK to generate the default files.

Adding the .EDMX File

Now, add a new folder under the root of the application and name it as "Model". Within that folder add another folder and name it as "DB". The application structure should now look something like the below:



After that, right click on the "DB" folder and select Add -> New Item -> Data -> ADO.NET Entity Data Model just like in the figure below,



Notice that I have named the entity as "SampleModel" just for the purpose of this demo. You may want to name it to a more appropriate name based on your requirement, but for this example, let's just use "SampleModel". Now, click Add to continue and on the next step select "Generate from database" and click Next. On the next step you can connect or browse to the database that you want to use in the application and test the connection string by clicking on the "Test Connection" button. If it succeeds, then you can continue by clicking OK and then Next.

Note that in this example, I created a simple database called "DeveloperReport.mdf" and added it into the application's App_Data folder. We will be using the aforementioned database for this entire article.

Here’s the screenshot after browsing the database,



On the next step, we can add the table(s), views or stored procedures that we want to use in the application by selecting the checkbox, just like in the figure shown below,



Notice that we’ve only selected the "SysUser" table. This is because we are going to use this table for doing insert and we don't need anything else. Now click on the Finish button to generate the following entity model below:



What happened there is that EF will automatically generate the business object (classes) for us within the Entity Data Model (EDM) that we have just created earlier, and let you query against it. The EDM is the main gateway by which you retrieve objects from the database and resubmit changes. If you’re still confused, don’t worry as we will be seeing how it actually works soon enough.

Setting Up the Form

At this point, we already have our model in place. Now, it’s time for us to set up our GUI. Let's add a new WebForm page. To do this, just right click on the project and select Add -> New Item -> Web -> Webform using Master Page, and then click Add. For the simplicity of this demo, we’ll just set up the GUI like this:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebAppDemo.WebForm1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <h1>Add New</h1>
    <table>
        <tr>
            <td>First Name</td>
            <td><asp:TextBox ID="tbFirstName" runat="server" /></td>
        </tr>
        <tr>
            <td>Last Name</td>
            <td><asp:TextBox ID="tbLastName" runat="server" /></td>
        </tr>
        <tr>
            <td>Contact Number</td>
            <td><asp:TextBox ID="tbContactNumber" runat="server" /></td>
        </tr>
        <tr>
            <td>Login ID</td>
            <td><asp:TextBox ID="tbLoginID" runat="server" /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><asp:TextBox ID="tbPassword" runat="server" TextMode="Password" /></td>
        </tr>
    </table>
    <br />
    <asp:Label ID="lblMessage" runat="server" />
    <br />
    <asp:Button ID="btnAdd" runat="server" Text="Add" />
</asp:Content>

Nothing fancy there. The markup above just contains some basic server controls to compose the page.

Creating the Object Manager Class

The next step that we are going to do is to create an object manager class that handles the CRUD operations. The purpose of this class is to separate the actual data operations from our code behind, and to have a central class for handling Insert, Update, Fetch and Delete operations.

Now, right click on the "Model" folder and add a new class by selecting Add -> Class. Since we are going to manipulate the SysUser table then we will name the class as "UserManager". Here's the code block for the "UserManager.cs" class,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebAppDemo.Model.DB;

namespace WebAppDemo.Model
{
    public class UserManager
    {

        private DeveloperReportEntities dre = new DeveloperReportEntities();

        public void Add(string firstName, string LastName, string contactNumber, string loginID, string password)
        {
            SysUser user = new SysUser();
            user.FirstName = firstName;
            user.LastName = LastName;
            user.ContactNumber = contactNumber;
            user.SysUserLoginID = loginID;
            user.SysPassword = password;

            dre.AddToSysUsers(user);
            dre.SaveChanges();
        }
    }
}

The code above handles the insertion of data to our database. The DeveloperReportEntities is the name of our object entity, which is the gateway to communicate with the data from the database. The SysUser is a class generated by EF which houses some properties. Calling the AddToSysUsers() method will add the new SysUser object that we have created. The AddToSysUsers() method is automatically created by EF for us. Invoking the SaveChanges() method automatically inserts the data to our database.

Performing Insert

Let’s start by performing an insert operation to our database. Now switch to WebForms’ code behind file, and create a new method that would call the UserManager class to do insertion. Here's the code block below:

using System;
using WebAppDemo.Model;

namespace WebAppDemo
{
    public partial class WebForm1 : System.Web.UI.Page
    {

        private void InsertUserInfo(string firstName, string LastName, string contactNumber, string loginID, string password)
        {
            UserManager userMgr = new UserManager();
            userMgr.Add(firstName, LastName, contactNumber, loginID, password);
        }

        protected void btnAdd_Click(object sender, EventArgs e)
        {
            //You may need to validate the data first here

            InsertUserInfo(tbFirstName.Text,
                           tbLastName.Text,
                           tbContactNumber.Text,
                           tbLoginID.Text,
                           tbPassword.Text);

            lblMessage.Text = "Data Inserted!";

        }
    }
}

Notice that we never included some basic validations in the form such as data inputs and data formats because we want to keep this demo as simple as possible. In real projects, you should ensure that all data inputs are validated before submitting it to the database, including the login ID and password validations.

Running the page will result to something like this:



And here's the screenshot of the SysUser table after the data is inserted:



Performing Fetch and Filter

In this section, we’re going to see how to fetch the data from the database and populate the form fields with Entity Framework.

Setting Up the Form

Okay. Let's go ahead and add a new WebForm page. Then setup the ASPX markup to something like this:

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <asp:DropDownList ID="ddlUser" runat="server" AppendDataBoundItems="true">
        <asp:ListItem Value="0">--Select--</asp:ListItem>
    </asp:DropDownList>
    <br />First Name:
    <br />
    <asp:TextBox ID="tbFirstName" runat="server" />
    <br />Last Name:
    <br />
    <asp:TextBox ID="tbLastName" runat="server" />
    <br />Contact Number:
    <br />
    <asp:TextBox ID="tbContactNumber" runat="server" />
</asp:Content>

Again, there’s nothing fancy in the markup above. What we want to do here is we are going to populate the DropDownList "ddlUser" with the list of names from the database, and then populate the remaining textbox with the details based on the selected name from the DropDownList. Since we only want to display the information in the page, so we set the ReadOnly attribute in the TextBox to true.

Creating the View Model

If you remember, we created an Object Manager Class for handling any data operations in the database. Since we are going to fetch the list of names from the database, then we will add a new method in UserManager class.
As a recap, Entity Framework will generate the business objects and manage Data Access within the application. As a result, the class SysUser is automatically created by EF and it features all the fields in the database table as properties of the class.

We don't want to use this class for listing all names of the users. It would be wasteful as EF will bring back all information in the table including the LastName, ContactNumber, SysUserLogIn and Password. All we need for populating the DropDownList is the SysUserID and FirstName. Having that said, we need to create a separate class that just holds these properties:

namespace WebAppDemo.Model.ViewModel
{
    public class UserName
    {
        public int SysUserID { get; set; }
        public string FirstName { get; set; }
    }
}

We also need to create a separate class for listing the user detail information, and create the necessary fields that we need to use like below:

public class UserDetail
{
    public int SysUserID { get; set; }
    public string FirstName { get; set; }
    public string Lastname { get; set; }
    public string ContactNumber { get; set; }
}

Both classes above are stored under Model -> ViewModel folder within the application, as you can probably guess from the namespaces defined ;).

At this point, we already have the required fields for populating the controls in the form. Let now add a couple of new methods in UserManager class: (1) for fetching the list of names and, (2) for fetching the user details. Our UserManager class would now become like this:

using System.Collections.Generic;
using System.Linq;
using WebAppDemo.Model.DB;
using WebAppDemo.Model.ViewModel;

namespace WebAppDemo.Model
{
    public class UserManager
    {

        private DeveloperReportEntities dre = new DeveloperReportEntities();

        public void Add(string firstName, string LastName, string contactNumber, string loginID, string password)
        {
            SysUser user = new SysUser();
            user.FirstName = firstName;
            user.LastName = LastName;
            user.ContactNumber = contactNumber;
            user.SysUserLoginID = loginID;
            user.SysPassword = password;

            dre.AddToSysUsers(user);
            dre.SaveChanges();
        }

        public IEnumerable<UserName> GetUserFirstName()
        {
            var user = from o in dre.SysUsers
                       select new UserName
                       {
                           SysUserID = o.SysUserID,
                           FirstName = o.FirstName
                       };
            return user.ToList();
        }

        public IEnumerable<UserDetail> GetUserDetail(int userID)
        {
            var user = from o in dre.SysUsers
                       where o.SysUserID == userID
                       select new UserDetail
                       {
                           SysUserID = o.SysUserID,
                           FirstName = o.FirstName,
                           LastName = o.LastName,
                           ContactNumber = o.ContactNumber
                       };
            return user.ToList();
        }

    }
}

The code above used LINQ syntax for querying data. As you can see, we query the SysUser object based on the parameter we passed on the GetUserFirstName() method. Once we invoke the LINQ ToList() function, this LINQ query will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the Entity Model.

The GetUserFirstName() is a method that gets all the User FirstName along with the SysUserID. This method returns an IEnumrable of UserName class. The GetUserDetail() is a method that gets the user detail information based on the
ID passed on to the query. This method returns a List of UserDetail class.

One of the cool things about EF is we don’t need to worry about how the query is being constructed because EF will take care of that for you, including mapping of the data types from your table columns, mapping relationships between tables, etcetera and etcetera. Always keep in mind that EF is an ORM (Object Relational Mapper) and so we don’t need to deal directly with databases, tables and columns but instead we deal with the objects that is in the Entity and query the data against it using LINQ syntax.

Populating the DropDownList

Now let’s switch to the code behind file of the WebForm and populate the DropDownList. Here's the code block for binding the DropDownList control in the webform. Typically we do this at Page_Load event within Not IsPostBack block like below:

using System;
using WebAppDemo.Model;

namespace WebAppDemo
{
    public partial class WebForm2 : System.Web.UI.Page
    {

        private void BindUserNames()
        {
            UserManager userMgr = new UserManager();
            ddlUser.DataSource = userMgr.GetUserFirstName();
            ddlUser.DataTextField = "FirstName";
            ddlUser.DataValueField = "SysUserID";
            ddlUser.DataBind();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                BindUserNames();
        }
    }
}

Running the page will result to something like this:



Populating the Form with the User Details

Now that we already have the list of first names in the DropDownList, the next step is to populate the TextBox in the form with other user information based on the selected item from the DropDownList. Here's the code block below:

private void PopulateFormFieds(int userID)
{
    UserManager userMgr = new UserManager();
    var result = userMgr.GetUserDetail(userID);
    if (result.Count > 0)
    {
        var user = result.First();
        tbFirstName.Text = user.FirstName;
        tbLastName.Text = user.LastName;
        tbContactNumber.Text = user.ContactNumber;
    }
    else
    {
        //NO RECORDS FOUND.
        tbFirstName.Text = string.Empty;
        tbLastName.Text = string.Empty;
        tbContactNumber.Text = string.Empty;
    }
}

protected void ddlUser_SelectedIndexChanged(object sender, EventArgs e)
{
    PopulateFormFieds(Convert.ToInt32(ddlUser.SelectedItem.Value));
}

As you can see the code above is very straight forward. The PopulateFormFieds() is obviously a method for populating the TextBox with the user detail information. The first line in the method creates an instance of the UserManager class and calls the method GetUserDetail() by passing the userID as the parameter. We then stored the results in the result variable and check if the result count is greater than 0. If it is greater than 0 then that means there are data returned based on the query we issued, and fill the TextBox with the corresponding data.

Here's the output below after selecting an item from the DropDownList:



Performing Edit and Update

In this section, we are going to see how to Edit and Update the form with Entity Framework. Since this is a continuation of the previous section, then we’ll be using the same layout and extend it a bit by adding some server controls: Few Buttons and a Label control for displaying a message. Aside from that we also need to move the form fields inside a Panel control for validation purposes, and remove the ReadOnly attribute for each TextBox. Here's the updated mark-up below:

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <asp:DropDownList ID="ddlUser" runat="server" AppendDataBoundItems="true"
                      AutoPostBack="true" onselectedindexchanged="ddlUser_SelectedIndexChanged">
        <asp:ListItem Value="0">--Select--</asp:ListItem>
    </asp:DropDownList>
    <asp:Panel ID="pnlUserDetail" runat="server" Enabled="false">
        <br />First Name:
        <br /><asp:TextBox ID="tbFirstName" runat="server" />
        <br />Last Name:
        <br /><asp:TextBox ID="tbLastName" runat="server" />
        <br />Contact Number:
        <br /><asp:TextBox ID="tbContactNumber" runat="server" />
        <br />
        <br />
    </asp:Panel>
    <asp:Label ID="lblMessage" runat="server" ForeColor="Green" />
    <br />
    <asp:Button ID="btnEdit" runat="server" Text="Edit" onclick="btnEdit_Click" />
    <asp:Button ID="btnUpdate" runat="server" Text="Update" Visible="false" onclick="btnUpdate_Click" />
    <asp:Button ID="btnCancel" runat="server" Text="Cancel" Visible="false" onclick="btnCancel_Click" />
    <asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" />
</asp:Content>

Now, add the following method below in the UserManager class:

public void UpdateUser(UserDetail userDetail)
{
    var user = (from o in dre.SysUsers
                where o.SysUserID == userDetail.SysUserID
                select o).First();
    user.FirstName = userDetail.FirstName;
    user.LastName = userDetail.LastName;
    user.ContactNumber = userDetail.ContactNumber;

    dre.SaveChanges();
}

 

The UpdateUser() method handles the updating of data. This method takes the UserDetail class as the parameter. If you can still remember in previous example, the UserDatail is a class that holds some properties such as the SysUserID, FirstName, LastName and ContactNumber.

The first line within the method gets the SysUser object based on the userID using LINQ syntax and assigned the result into a variable user. The LINQ FirstOrDefault() method is an eager function which returns the first element of a sequence that satisfies a specified condition. Once the FirstOrDefault() function is invoked, then EF will automatically issue a parameterize SQL query to the database in which the SQL Server can understand, and then bring back the result to the Entity Model.

We then assigned the new values to each field and then call SaveChanges() method to update the database with the changes.

Now let's go to the code behind file of the WebForm and perform the update by calling the method UpdateUser() from the UserManager class. Here are the code blocks below:

private void ToggleButton(bool isEdit)
{
    if (isEdit)
    {
        btnEdit.Visible = false;
        btnDelete.Visible = false;
        btnUpdate.Visible = true;
        btnCancel.Visible = true;
    }
    else
    {
        btnEdit.Visible = true;
        btnDelete.Visible = true;
        btnUpdate.Visible = false;
        btnCancel.Visible = false;
    }

    pnlUserDetail.Enabled = isEdit;
}

private void UpdateUserDetail(UserDetail userDetail)
{
    UserManager userMgr = new UserManager();
    userMgr.UpdateUser(userDetail);
}

protected void btnEdit_Click(object sender, EventArgs e)
{
    if (ddlUser.SelectedItem.Value != "0")
    {
        ToggleButton(true);
        lblMessage.Text = string.Empty;
    }
    else
    {
        lblMessage.Text = "Please select name from the list first.";
        lblMessage.ForeColor = System.Drawing.Color.Red;
    }
}

protected void btnUpdate_Click(object sender, EventArgs e)
{

    UserDetail userDetail = new UserDetail();
    userDetail.SysUserID = Convert.ToInt32(ddlUser.SelectedItem.Value);
    userDetail.FirstName = tbFirstName.Text.TrimEnd();
    userDetail.LastName = tbLastName.Text.TrimEnd();
    userDetail.ContactNumber = tbContactNumber.Text.TrimEnd();

    UpdateUserDetail(userDetail);
    lblMessage.Text = "Update Successful!";
    ToggleButton(false);
}

protected void btnCancel_Click(object sender, EventArgs e)
{
    ToggleButton(false);
}

The ToggleButton() method returns a boolean type which is responsible for toggling the visibility of each buttons in the form during Edit, Update, Cancel and Delete mode.

The UpdateUserDetail() method takes a UserDetail as the parameter. This method calls the UpdateUser() method from the UserManager class.

At btnUpdate_Click event, we created an instance of the UserDetail class and assign the values from the form to the corresponding fields. After that, we call the method UpdateUserDetail() and pass the UserDetail object as the parameter.

Here's the output below when running it on the browser:

On initial load,



After selecting an item from the DropDownList,

  

On Edit Mode (after clicking the edit button) ,



After Update,



Performing Delete

Last but not the least; let’s implement the deletion part of the program. In the UserManager class, add the following method below:

public void DeleteUser(int userID)
{
    var user = (from o in dre.SysUsers
                where o.SysUserID == userID
                select o).FirstOrDefault();

    dre.DeleteObject(user);
    dre.SaveChanges();
}

Just like the UpdateUser() method, we query the SysUser object based on the SysUserID and then assign the result in the variable user. After that, we called the DeleteObject() method to perform delete and then call the SaveChanges
() method to reflect the changes to the database.

Here's the code behind for the deletion:

protected void btnDelete_Click(object sender, EventArgs e)
{
    if (ddlUser.SelectedItem.Value != "0")
    {
        //Perform the Delete
        UserManager userMgr = new UserManager();
        userMgr.DeleteUser(Convert.ToInt32(ddlUser.SelectedItem.Value));

        //Re-bind the DropDownList
        ddlUser.Items.Clear();
        BindUserNames();

        //Clear the form fields
        tbFirstName.Text = string.Empty;
        tbLastName.Text = string.Empty;
        tbContactNumber.Text = string.Empty;

        lblMessage.Text = "Delete Successful!";

    }
    else
    {
        lblMessage.Text = "Please select name from the list first.";
        lblMessage.ForeColor = System.Drawing.Color.Red;
    }
}

Since we don’t want users to delete the information right away, we need to prompt them a confirmation message if they wish to continue the deletion or not. To do this, we could simply hook up the JavaScript confirm function to the delete button. Take a look at the highlighted code below:

<asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" OnClientClick="return confirm('The selected user information will be deleted. Do you wish to continue?');return false;" />

Here's the output below when running the page and perform the delete:

On Delete



After Deletion



That’s it! I hope someone find this article useful.

Summary

In this article, we have learned about the basics of how to perform Insert, Fetch, Filter, Edit, Update and Delete using ADO.NET Entity Framework.