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.

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.