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.

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

  1. USE [NORTHWND]  
  2. GO  
  3. /****** Object: StoredProcedure [dbo].[GetEmployee] Script Date: 9/15/2017 11:20:14 AM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. ALTER PROCEDURE [dbo].[GetEmployee]  
  9. AS  
  10. SELECT EmployeeID,  
  11. FirstName,  
  12. LastName,  
  13. City,  
  14. Region,  
  15. PostalCode,  
  16. Country,  
  17. Notes  
  18. FROM Employees  
  19. 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.

  1. //------------------------------------------------------------------------------  // <auto-generated>  
  2. // This code was generated from a template.  
  3. //  
  4. // Manual changes to this file may cause unexpected behavior in your application.  
  5. // Manual changes to this file will be overwritten if the code is regenerated.  
  6. // </auto-generated>  
  7. //------------------------------------------------------------------------------  
  8. namespace crud_angularjs_entityframework_webapi.Models {  
  9.     using System;  
  10.     public partial class GetEmployee_Result {  
  11.         public int EmployeeID {  
  12.             get;  
  13.             set;  
  14.         }  
  15.         public string FirstName {  
  16.             get;  
  17.             set;  
  18.         }  
  19.         public string LastName {  
  20.             get;  
  21.             set;  
  22.         }  
  23.         public string City {  
  24.             get;  
  25.             set;  
  26.         }  
  27.         public string Region {  
  28.             get;  
  29.             set;  
  30.         }  
  31.         public string PostalCode {  
  32.             get;  
  33.             set;  
  34.         }  
  35.         public string Country {  
  36.             get;  
  37.             set;  
  38.         }  
  39.         public string Notes {  
  40.             get;  
  41.             set;  
  42.         }  
  43.     }  
  44. }  

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

  1. //Entity set  
  2. public NORTHWNDEntities db = new NORTHWNDEntities();  
  3. /// <summary>  
  4. /// get employee list using linq  
  5. /// </summary>  
  6. /// <returns></returns>  
  7. public async Task < IEnumerable < GetEmployee_Result >> GetEmployee1() {  
  8.     try {  
  9.         return await (from n in db.Employees select new GetEmployee_Result {  
  10.             EmployeeID = n.EmployeeID,  
  11.                 FirstName = n.FirstName,  
  12.                 LastName = n.LastName,  
  13.                 City = n.City,  
  14.                 Region = n.Region,  
  15.                 PostalCode = n.PostalCode,  
  16.                 Country = n.Country,  
  17.                 Notes = n.Notes  
  18.         }).OrderByDescending(n => n.EmployeeID).ToListAsync();  
  19.     } catch (Exception ex) {  
  20.         throw ex;  
  21.     }  
  22. }  

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

  1. /// <summary>  
  2. /// Get Employee using stored procedure  
  3. /// </summary>  
  4. /// <returns></returns>  
  5. public async Task < IEnumerable < GetEmployee_Result >> GetEmployee() {  
  6.     try {  
  7.         return await db.Database.SqlQuery < GetEmployee_Result > ("GetEmployee").ToListAsync();  
  8.     } catch (Exception ex) {  
  9.         throw ex;  
  10.     }  
  11. }  
  12. /// <summary>  
  13. /// Get Employee by ID using stored procedure  
  14. /// </summary>  
  15. /// <param name="EmployeeID"></param>  
  16. /// <returns></returns>  
  17. public async Task < IEnumerable < GetEmployee_Result >> GetEmployeeByID(string EmployeeID) {  
  18.     try {  
  19.         var employeeID = new SqlParameter("EmployeeID", EmployeeID == null ? (object) DBNull.Value : EmployeeID);  
  20.         return await db.Database.SqlQuery < GetEmployee_Result > ("GetEmployeeByID @EmployeeID", employeeID).ToListAsync();  
  21.     } catch (Exception ex) {  
  22.         throw ex;  
  23.     }  
  24. }  

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

  1. [RoutePrefix("api/EmployeeAPI")]  
  2. public class EmployeeAPIController: ApiController {  
  3.     private readonly EmployeeVMBuilder _employeeVMBuilder = new EmployeeVMBuilder();  
  4.     // GET api/<controller>  
  5.     [Route("GetEmployee")]  
  6.     public async Task < IEnumerable < GetEmployee_Result >> GetEmployee() {  
  7.             return await _employeeVMBuilder.GetEmployee();  
  8.         }  
  9.         [Route("GetEmployee1")]  
  10.     public async Task < IEnumerable < GetEmployee_Result >> GetEmployee1() {  
  11.         return await _employeeVMBuilder.GetEmployee1();  
  12.     }  
  13. }  

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

  1. app.service('employeeService'function($http) {  
  2.     this.getEmployees = function() {  
  3.         var req = $http.get('api/EmployeeAPI/GetEmployee');  
  4.         return req;  
  5.     };  
  6.     this.getEmployees1 = function() {  
  7.         var req = $http.get('api/EmployeeAPI/GetEmployee1');  
  8.         return req;  
  9.     };  
  10. });  

Now, add an Angular Controller.

  1. app.controller("employeeController"function($scope, $filter, employeeService, $window, $http, $log) {  
  2.     init();  
  3.     //get employe  
  4.     // employeeService.getEmployees().then(function (result) {  
  5.     // debugger;  
  6.     // angular.forEach(result.data, function (value, index) {   
  7.     // $scope.Employees.push({ id: value.employeeID.trim(), label: value.firstName });  
  8.     // });  
  9.     // console.log($scope.Employees);  
  10.     // }, function (error) {  
  11.     // $window.alert('Something went wrong while fetching the Network data.');  
  12.     //});  
  13.     function init() {  
  14.         $scope.employees = [];  
  15.         $scope.employees1 = [];  
  16.         employeeService.getEmployees().then(function(result) {  
  17.             $scope.employees = result.data;  
  18.             console.log($scope.Employees);  
  19.         }, function(error) {  
  20.             $window.alert('Oops! Something went wrong while fetching employee data.');  
  21.         });  
  22.         employeeService.getEmployees1().then(function(result) {  
  23.             $scope.employees = result.data;  
  24.             console.log($scope.Employees);  
  25.         }, function(error) {  
  26.             $window.alert('Oops! Something went wrong while fetching employee data.');  
  27.         });  
  28.         $scope.gridOptions = {  
  29.             enableRowSelection: true,  
  30.             selectionRowHeaderWidth: 35,  
  31.             enableRowHeaderSelection: true,  
  32.             paginationPageSizes: [10, 20, 30, 40],  
  33.             paginationPageSize: 10,  
  34.             enableSorting: true,  
  35.             columnDefs: [{  
  36.                 name: '',  
  37.                 field: 'EmployeeID',  
  38.                 enableColumnMenu: false,  
  39.                 enableHiding: false,  
  40.                 exporterSuppressExport: true,  
  41.                 enableSorting: false,  
  42.                 enableFiltering: false,  
  43.                 visible: false  
  44.             }, {  
  45.                 name: 'First Name',  
  46.                 field: 'FirstName',  
  47.                 headerCellClass: 'tablesorter-header-inner',  
  48.                 enableFiltering: true,  
  49.                 enableCellEdit: true,  
  50.             }, {  
  51.                 name: 'Last Name',  
  52.                 field: 'LastName',  
  53.                 headerCellClass: 'tablesorter-header-inner',  
  54.                 enableFiltering: true,  
  55.                 enableCellEdit: true,  
  56.             }, {  
  57.                 name: 'City',  
  58.                 field: 'City',  
  59.                 headerCellClass: 'tablesorter-header-inner',  
  60.                 enableFiltering: true,  
  61.                 enableCellEdit: true,  
  62.             }, {  
  63.                 name: 'Region',  
  64.                 field: 'Region',  
  65.                 enableCellEdit: false,  
  66.                 headerCellClass: 'tablesorter-header-inner',  
  67.                 enableFiltering: true  
  68.             }, {  
  69.                 name: 'Postal Code',  
  70.                 field: 'PostalCode',  
  71.                 enableCellEdit: false,  
  72.                 headerCellClass: 'tablesorter-header-inner',  
  73.                 enableFiltering: true  
  74.             }, {  
  75.                 name: 'Country',  
  76.                 field: 'Country',  
  77.                 enableCellEdit: false,  
  78.                 headerCellClass: 'tablesorter-header-inner',  
  79.                 enableFiltering: true  
  80.             }, {  
  81.                 name: 'Notes',  
  82.                 field: 'Notes',  
  83.                 enableCellEdit: false,  
  84.                 headerCellClass: 'tablesorter-header-inner',  
  85.                 enableFiltering: true  
  86.             }],  
  87.             //This code used for export grid data in csv file  
  88.             enableGridMenu: true,  
  89.             enableSelectAll: true,  
  90.             exporterMenuPdf: false,  
  91.             enableFiltering: true,  
  92.             exporterCsvFilename: 'EmployeeList_' + $filter('date')(new Date(), 'MM/dd/yyyy') + '.csv',  
  93.             exporterCsvLinkElement: angular.element(document.querySelectorAll(".custom-csv-link-location")),  
  94.             onRegisterApi: function(gridApi) {  
  95.                 $scope.gridApi = gridApi;  
  96.                 gridApi.selection.on.rowSelectionChanged($scope, function(row) {  
  97.                     var msg = 'row selected ' + row.isSelected;  
  98.                     $log.log(msg);  
  99.                     console.log(msg);  
  100.                 });  
  101.                 gridApi.selection.on.rowSelectionChangedBatch($scope, function(rows) {  
  102.                     var msg = 'rows changed ' + rows.length;  
  103.                     $log.log(msg);  
  104.                     console.log(msg);  
  105.                 });  
  106.             },  
  107.             //end here  
  108.             //data for grid  
  109.             data: 'employees'  
  110.         };  
  111.     }  
  112.     $scope.addEmployee = function() {  
  113.         var n = $scope.gridOptions.data.length + 1;  
  114.         $scope.employees.push({  
  115.             //"FirstName": "Raj " + n,  
  116.             //"LastName": "Kumar " + n,  
  117.             "FirstName""Raj",  
  118.             "LastName""Kumar",  
  119.             "City""Noida",  
  120.             "Region""UP",  
  121.             "PostalCode""201301",  
  122.             "Country""India",  
  123.             "Notes""This is test note"  
  124.         });  
  125.     };  
  126.     $scope.deleteSelected = function() {  
  127.         angular.forEach($scope.gridApi.selection.getSelectedRows(), function(data, index) {  
  128.             $scope.employees.splice($scope.employees.lastIndexOf(data), 1);  
  129.         });  
  130.     }  
  131. });  

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

  1. bundles.Add(new StyleBundle("~/Content/css").Include(  
  2. "~/Content/bootstrap.css",  
  3. "~/Content/site.css",  
  4. "~/Content/ui-grid.min.css"));  
  5. bundles.Add(new ScriptBundle("~/bundles/angular").Include(  
  6. "~/Scripts/angular.js",  
  7. "~/Scripts/angular-route.js",  
  8. "~/Scripts/ui-grid.js"));  
  9. bundles.Add(new ScriptBundle("~/bundles/employee").Include(  
  10. "~/Angular/app.js",  
  11. "~/Angular/Services/employeeService.js",  
  12. "~/Angular/Controller/employeeController.js"));  
 Add script references in _Layout.cshtml.
  1. @Scripts.Render("~/bundles/jquery")  
  2. @Scripts.Render("~/bundles/bootstrap")  
  3. @Scripts.Render("~/bundles/angular")  
  4. @Scripts.Render("~/bundles/employee")  
  5. @RenderSection("scripts", required: false)  

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

  1. @using System.Web.Configuration;  
  2. @{  
  3.    string appRoot = WebConfigurationManager.AppSettings["AppRoot"];  
  4. }  
  5. <meta charset="utf-8" />  
  6. <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  7. <base href="@appRoot" />  
  8. <title>@ViewBag.Title - My ASP.NET Application</title>  
  9. @Styles.Render("~/Content/css")  
  10. @Scripts.Render("~/bundles/modernizr")  
And, add the Web.config key.

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

Index.cshtml.

  1. @ {  
  2.     ViewBag.Title = "Index";  
  3.     Layout = "~/Views/Shared/_Layout.cshtml";  
  4. } < h2 > Employee < /h2> < div ng - controller = "employeeController" > < div ui - grid = "gridOptions"  
  5. ui - grid - pagination  
  6. ui - grid - selection  
  7. ui - grid - exporter  
  8. ui - grid - resize - columns  
  9. ui - grid - auto - resize  
  10. 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.