calling a Stored Procedure instead of a table in MVC Entity Framework

I'm using Visual Studio 2013.
I have a asp.net mvc application and using sql server 2008 R2.
I'm using the Employees table of the Northwind database for my example.
In my example application I bind the Employee table to a FlexiGrid in my View.

So right now my application project directory looks like this:

my asp.net mvc directory
Model

I created a model using the entity model. My model is called northwindEF.edmx

My model diagram looks like this. Although it shows 4 tables I'm only really using the Employee table.

entity model diagram
In my Model Browser this is what it looks like:my entity model's model browser

Right now in the Models folder of my application I have a file called All.cs which looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using FlexigridUsingMvc2.Models;

namespace FlexigridUsingMvc2.Models
{
    
    public class EntContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
        
    }

}

Open in new window




Controller

So right now in my Controllers folder I have a controller called HomeController.cs
This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using FlexigridUsingMvc2.Models;

using FlexigridUsingMvc2.Extensions;

namespace FlexigridUsingMvc2.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        [HttpPost]
        public JsonResult List(int page, int rp, string qtype, string letter_pressed, string query)
        {

            using (var db = new EntContext())
            {
                if (letter_pressed == "ALL") letter_pressed = "";

                Func<IQueryable<Employee>> EmployeeFiltered = () =>
                    db.Employees
                    .Where(x =>
                            (
                                (
                                    !string.IsNullOrEmpty(letter_pressed)
                                    && x.LastName.StartsWith(letter_pressed)
                                )
                                ||
                                string.IsNullOrEmpty(letter_pressed)
                            )
                            &&
                            (
                                (
                                    !string.IsNullOrEmpty(query)
                                    &&
                                    (
                                        (qtype == "LastName" && x.LastName.Contains(query))
                                        ||
                                        (qtype == "FirstName" && x.FirstName.Contains(query))
                                        ||
                                        (qtype == "Title" && x.Title.Contains(query))
                                    )
                                )
                                ||
                                string.IsNullOrEmpty(query)
                            )
                        );


                return Json(
                    new
                    {
                        page = page,
                        total = EmployeeFiltered().Count(),
                        rows =
                            EmployeeFiltered()
                            .OrderBy(x => x.LastName)
                            .LimitAndOffset(pageSize: rp, pageOffset: page)
                            .ToList()
                            .Select(x =>
                                new
                                {
                                    id = x.LastName,
                                    cell = new string[] { x.LastName, x.FirstName, x.Title }
                                })
                    });


            }//using
        }//List

        public ActionResult Index()
        {
            return View();
        }


    }
}

Open in new window



If you noticed the code for my controller, I have a JSON function which calls the EntContext from my model.


View

So right now my view Index.csthml code looks like this:

@{ 
    ViewBag.Title = "Index"; 
 }

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>


    <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
    <script src="@Url.Content("~/Scripts/jquery-1.8.2.min.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/flexigrid/flexigrid.pack.js")" type="text/javascript"></script>
    <link href="@Url.Content("~/Scripts/flexigrid/css/flexigrid.css")" rel="stylesheet" type="text/css" />

    <script type="text/javascript">
        $(document).ready(function () {

            $("#fff").flexigrid
            (
            {
                url: '/Home/List/',
                dataType: 'json',
                colModel: [
                { display: 'LastName', name: 'LastName', width: 200, sortable: false, align: 'left' },
                { display: 'FirstName', name: 'FirstName', width: 280, sortable: false, align: 'left' },
                { display: 'Title', name: 'Title', width: 220, sortable: false, align: 'left' }
                ],
                buttons: [
                { name: 'Add', bclass: 'add', onpress: add },
                { name: 'Edit', bclass: 'edit', onpress: edit },
                { name: 'Delete', bclass: 'delete', onpress: del },
                { separator: true },
                { name: 'A', onpress: sortAlpha },
                { name: 'B', onpress: sortAlpha },
                { name: 'C', onpress: sortAlpha },
                { name: 'D', onpress: sortAlpha },
                { name: 'E', onpress: sortAlpha },
                { name: 'F', onpress: sortAlpha },
                { name: 'G', onpress: sortAlpha },
                { name: 'H', onpress: sortAlpha },
                { name: 'I', onpress: sortAlpha },
                { name: 'J', onpress: sortAlpha },
                { name: 'K', onpress: sortAlpha },
                { name: 'L', onpress: sortAlpha },
                { name: 'M', onpress: sortAlpha },
                { name: 'N', onpress: sortAlpha },
                { name: 'O', onpress: sortAlpha },
                { name: 'P', onpress: sortAlpha },
                { name: 'Q', onpress: sortAlpha },
                { name: 'R', onpress: sortAlpha },
                { name: 'S', onpress: sortAlpha },
                { name: 'T', onpress: sortAlpha },
                { name: 'U', onpress: sortAlpha },
                { name: 'V', onpress: sortAlpha },
                { name: 'W', onpress: sortAlpha },
                { name: 'X', onpress: sortAlpha },
                { name: 'Y', onpress: sortAlpha },
                { name: 'Z', onpress: sortAlpha },
                { name: 'ALL', onpress: sortAlpha }
                ],
                searchitems: [
                { display: 'LastName', name: 'LastName', isdefault: true },
                { display: 'FirstName', name: 'FirstName' },
                { display: 'Title', name: 'Title' }
                ],
                singleSelect: true,
                sortname: "LastName",
                sortorder: "asc",
                usepager: true,
                title: 'Employees',
                useRp: true,
                rp: 10,
                showTableToggleBtn: false,
                width: 800,
                height: 255
            }
            );

        });
        function sortAlpha(com) {
            jQuery('#fff').flexOptions({ newp: 1, params: [{ name: 'letter_pressed', value: com }, { name: 'qtype', value: $('select[name=qtype]').val()}] });
            jQuery("#fff").flexReload();
        }

        function edit(com, grid) {

            // alert(com); // Edit

            if ( $('.trSelected',grid).length == 1 )
            {
                var items = $('.trSelected',grid);
                alert(com + ' ' + items[0].id.substr(3));
            }
        }

        function add(com, grid) {

            alert(com);

            /*if ($('.trSelected', grid).length == 1) {
                var items = $('.trSelected', grid);
                alert(items[0].id.substr(3));
            }*/
        }

        function del(com, grid) {

            // alert(com); // Delete

            if ($('.trSelected', grid).length == 1) {
                var items = $('.trSelected', grid);
                alert(com + ' ' + items[0].id.substr(3));
            }
        }

    </script>

    <title>Index</title>

</head>
<body>
    <div>
        <table id="fff" style="display:none"></table>
    </div>
</body>
</html>

Open in new window


When I run my application it works fine and I get this:

my working grid view

So right now in my Model I'm just calling the Employee table from my Entity Model.

Now I'm trying to do the same example but just trying to learn the syntax to call a Stored Procedure in my model instead of just calling the table like in my example above.

So I created a simple Stored Procedure called FETCHEMPLOYEES that just returns the Employees table.
This is my code for my stored procedure:
CREATE PROCEDURE [dbo].[FETCHEMPLOYEES]
AS
BEGIN
SELECT * FROM [Northwind].[dbo].[Employees]
END

Open in new window


So in my entity model I showed above when I created that model, I also added this stored procedure to it. If you look at the image of my model browser above, if you look at the Function Imports folder, it's the one called FETCHEMPLOYEES.

This is what FETCHEMPLOYEES looks like:

my FETCHMPLOYEES stored procedure function
Essentially I just need to revise my Model and Controller to call my stored procedure instead of calling the employees table.

So then in my Models folder I created EmpModel.cs this is my code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
// mq
using System.Data.Objects;

namespace FlexigridUsingMvc2.Models
{
    public class EmpModel
    {

        NorthwindEntities empdb = new NorthwindEntities();

        public List<Employee> GetEmployees()
        {
            return empdb.FETCHEMPLOYEES().ToList();

        }

    }
}

Open in new window


So this is my question:

How would I revise my model and controller to call my Stored Procedure that returns the Employees table instead of calling the Employees table which it is currently doing?
LVL 1
maqskywalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi maqskywalker;

Modify your DbContext to look like this. You will need the default constructor as it is below and the two using statements
using System.Data.Entity.Core.Objects;  
using System.Data.Entity.Infrastructure;

public class EntContext : DbContext
{
    public EntContext()
        : base("name=NorthwindEntities")
    {
    }

    public virtual DbSet<Employee> Employees { get; set; }

    public virtual ObjectResult<Employee> FETCHEMPLOYEES()
    {
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Employee>("FETCHEMPLOYEES");
    }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.