Post
Ask Question

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.


Similar Articles