Two Ways To Get Data From Entity Framework

In this article, you will learn about two basic ways to get data from Entity Framework and display data in Angular UI-Grid using MVC application with Web API.

• LINQ
• Stored Procedure

In this article, I will show how to get data using LINQ query and stored procedure using Entity Framework.

Read the articles given below first to understand the AngularJS UI-grid.

• UI-Grid With AngularJS And WebAPI
• Export Data In Angular-UI-Grid Using WebAPI
• Filtering In UI-Grid With AngularJS And WebAPI
• Custom Scroll In AngularJS-UI-Grid With Web API
• Expandable grid in AngularJS-UI-Grid with Web API

Here are my tables.
 
This is my stored procedure for testing.

USE [NORTHWND]
GO
/****** Object: StoredProcedure [dbo].[GetEmployee] Script Date: 9/15/2017 11:20:14 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetEmployee]
AS
SELECT EmployeeID,
FirstName,
LastName,
City,
Region,
PostalCode,
Country,
Notes
FROM Employees
ORDER BY EmployeeID DESC

Note

When you add a model from the database, make sure to add a stored procedure also; so that a result class can also be added.

Here is my employee result class for your reference.

//------------------------------------------------------------------------------  // <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace crud_angularjs_entityframework_webapi.Models {
    using System;
    public partial class GetEmployee_Result {
        public int EmployeeID {
            get;
            set;
        }
        public string FirstName {
            get;
            set;
        }
        public string LastName {
            get;
            set;
        }
        public string City {
            get;
            set;
        }
        public string Region {
            get;
            set;
        }
        public string PostalCode {
            get;
            set;
        }
        public string Country {
            get;
            set;
        }
        public string Notes {
            get;
            set;
        }
    }
}

Add a new builder class. Let’s get the data using LINQ query first.

//Entity set
public NORTHWNDEntities db = new NORTHWNDEntities();
/// <summary>
/// get employee list using linq
/// </summary>
/// <returns></returns>
public async Task < IEnumerable < GetEmployee_Result >> GetEmployee1() {
    try {
        return await (from n in db.Employees select new GetEmployee_Result {
            EmployeeID = n.EmployeeID,
                FirstName = n.FirstName,
                LastName = n.LastName,
                City = n.City,
                Region = n.Region,
                PostalCode = n.PostalCode,
                Country = n.Country,
                Notes = n.Notes
        }).OrderByDescending(n => n.EmployeeID).ToListAsync();
    } catch (Exception ex) {
        throw ex;
    }
}

And, add one more function to get the data using stored procedure.

/// <summary>
/// Get Employee using stored procedure
/// </summary>
/// <returns></returns>
public async Task < IEnumerable < GetEmployee_Result >> GetEmployee() {
    try {
        return await db.Database.SqlQuery < GetEmployee_Result > ("GetEmployee").ToListAsync();
    } catch (Exception ex) {
        throw ex;
    }
}
/// <summary>
/// Get Employee by ID using stored procedure
/// </summary>
/// <param name="EmployeeID"></param>
/// <returns></returns>
public async Task < IEnumerable < GetEmployee_Result >> GetEmployeeByID(string EmployeeID) {
    try {
        var employeeID = new SqlParameter("EmployeeID", EmployeeID == null ? (object) DBNull.Value : EmployeeID);
        return await db.Database.SqlQuery < GetEmployee_Result > ("GetEmployeeByID @EmployeeID", employeeID).ToListAsync();
    } catch (Exception ex) {
        throw ex;
    }
}

Now, add a new API Controller and add the following code.

[RoutePrefix("api/EmployeeAPI")]
public class EmployeeAPIController: ApiController {
    private readonly EmployeeVMBuilder _employeeVMBuilder = new EmployeeVMBuilder();
    // GET api/<controller>
    [Route("GetEmployee")]
    public async Task < IEnumerable < GetEmployee_Result >> GetEmployee() {
            return await _employeeVMBuilder.GetEmployee();
        }
        [Route("GetEmployee1")]
    public async Task < IEnumerable < GetEmployee_Result >> GetEmployee1() {
        return await _employeeVMBuilder.GetEmployee1();
    }
}

Let’s add a new Angular service and call APIs.

app.service('employeeService', function($http) {
    this.getEmployees = function() {
        var req = $http.get('api/EmployeeAPI/GetEmployee');
        return req;
    };
    this.getEmployees1 = function() {
        var req = $http.get('api/EmployeeAPI/GetEmployee1');
        return req;
    };
});

Now, add an Angular Controller.

app.controller("employeeController", function($scope, $filter, employeeService, $window, $http, $log) {
    init();
    //get employe
    // employeeService.getEmployees().then(function (result) {
    // debugger;
    // angular.forEach(result.data, function (value, index) {
    // $scope.Employees.push({ id: value.employeeID.trim(), label: value.firstName });
    // });
    // console.log($scope.Employees);
    // }, function (error) {
    // $window.alert('Something went wrong while fetching the Network data.');
    //});
    function init() {
        $scope.employees = [];
        $scope.employees1 = [];
        employeeService.getEmployees().then(function(result) {
            $scope.employees = result.data;
            console.log($scope.Employees);
        }, function(error) {
            $window.alert('Oops! Something went wrong while fetching employee data.');
        });
        employeeService.getEmployees1().then(function(result) {
            $scope.employees = result.data;
            console.log($scope.Employees);
        }, function(error) {
            $window.alert('Oops! Something went wrong while fetching employee data.');
        });
        $scope.gridOptions = {
            enableRowSelection: true,
            selectionRowHeaderWidth: 35,
            enableRowHeaderSelection: true,
            paginationPageSizes: [10, 20, 30, 40],
            paginationPageSize: 10,
            enableSorting: true,
            columnDefs: [{
                name: '',
                field: 'EmployeeID',
                enableColumnMenu: false,
                enableHiding: false,
                exporterSuppressExport: true,
                enableSorting: false,
                enableFiltering: false,
                visible: false
            }, {
                name: 'First Name',
                field: 'FirstName',
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true,
                enableCellEdit: true,
            }, {
                name: 'Last Name',
                field: 'LastName',
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true,
                enableCellEdit: true,
            }, {
                name: 'City',
                field: 'City',
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true,
                enableCellEdit: true,
            }, {
                name: 'Region',
                field: 'Region',
                enableCellEdit: false,
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true
            }, {
                name: 'Postal Code',
                field: 'PostalCode',
                enableCellEdit: false,
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true
            }, {
                name: 'Country',
                field: 'Country',
                enableCellEdit: false,
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true
            }, {
                name: 'Notes',
                field: 'Notes',
                enableCellEdit: false,
                headerCellClass: 'tablesorter-header-inner',
                enableFiltering: true
            }],
            //This code used for export grid data in csv file
            enableGridMenu: true,
            enableSelectAll: true,
            exporterMenuPdf: false,
            enableFiltering: true,
            exporterCsvFilename: 'EmployeeList_' + $filter('date')(new Date(), 'MM/dd/yyyy') + '.csv',
            exporterCsvLinkElement: angular.element(document.querySelectorAll(".custom-csv-link-location")),
            onRegisterApi: function(gridApi) {
                $scope.gridApi = gridApi;
                gridApi.selection.on.rowSelectionChanged($scope, function(row) {
                    var msg = 'row selected ' + row.isSelected;
                    $log.log(msg);
                    console.log(msg);
                });
                gridApi.selection.on.rowSelectionChangedBatch($scope, function(rows) {
                    var msg = 'rows changed ' + rows.length;
                    $log.log(msg);
                    console.log(msg);
                });
            },
            //end here
            //data for grid
            data: 'employees'
        };
    }
    $scope.addEmployee = function() {
        var n = $scope.gridOptions.data.length + 1;
        $scope.employees.push({
            //"FirstName": "Raj " + n,
            //"LastName": "Kumar " + n,
            "FirstName": "Raj",
            "LastName": "Kumar",
            "City": "Noida",
            "Region": "UP",
            "PostalCode": "201301",
            "Country": "India",
            "Notes": "This is test note"
        });
    };
    $scope.deleteSelected = function() {
        angular.forEach($scope.gridApi.selection.getSelectedRows(), function(data, index) {
            $scope.employees.splice($scope.employees.lastIndexOf(data), 1);
        });
    }
});

At last, add your Angular ui-grid, Controller, Services, and module references in Bundle.Config.

bundles.Add(new StyleBundle("~/Content/css").Include(
"~/Content/bootstrap.css",
"~/Content/site.css",
"~/Content/ui-grid.min.css"));
bundles.Add(new ScriptBundle("~/bundles/angular").Include(
"~/Scripts/angular.js",
"~/Scripts/angular-route.js",
"~/Scripts/ui-grid.js"));
bundles.Add(new ScriptBundle("~/bundles/employee").Include(
"~/Angular/app.js",
"~/Angular/Services/employeeService.js",
"~/Angular/Controller/employeeController.js"));

 Add script references in _Layout.cshtml.

@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
@Scripts.Render("~/bundles/angular")
@Scripts.Render("~/bundles/employee")
@RenderSection("scripts", required: false)

You need to add base href also in _Layout.cshtml.

@using System.Web.Configuration;
@{
   string appRoot = WebConfigurationManager.AppSettings["AppRoot"];
}
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<base href="@appRoot" />
<title>@ViewBag.Title - My ASP.NET Application</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")

And, add the Web.config key.

<add key="AppRoot" value="/" />

Index.cshtml.

@ {
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
} < h2 > Employee < /h2> < div ng - controller = "employeeController" > < div ui - grid = "gridOptions"
ui - grid - pagination
ui - grid - selection
ui - grid - exporter
ui - grid - resize - columns
ui - grid - auto - resize
class = "grid" > < /div> < /div>

Now, run the application.



Conclusion

In this article, I have explained the two ways to get data from Entity Framework. If you have any question or comment, drop me a line in the comments section.