• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2560
  • Last Modified:

calling a stored procedure of complex type in ASP.NET MVC

I have a ASP.NET MVC 4 application. I'm using Sql Server 2008 R2.
I'm using C#, Razor Syntax and using Entity Framework 6.
I'm using the Customers and Orders table from the Northwind database.

In my example application I use a stored procedure called GetCustomerOrders
I am binding the result set returned from this stored procedure to a flexigrid in my view.

The result set this stored procedure returns looks like this:


result set of my stored procedure
This is my stored procedure:

CREATE PROCEDURE [dbo].[GetCustomerOrders]
AS
BEGIN
SELECT O.[OrderID]
      ,O.[CustomerID]
      ,O.[EmployeeID]
      ,O.[OrderDate]
      ,O.[RequiredDate]
      ,O.[ShippedDate]
      ,O.[ShipVia]
      ,O.[Freight]
      ,O.[ShipName]
      ,O.[ShipAddress]
      ,O.[ShipCity]
      ,O.[ShipRegion]
      ,O.[ShipPostalCode]
      ,O.[ShipCountry]
      ,C.[CompanyName]
      ,C.[Phone]
FROM [Northwind].[dbo].[Orders] O
INNER JOIN [Northwind].[dbo].[Customers] C
    ON O.CustomerID = C.CustomerID
END
GO

Open in new window


So my application directory looks like this:

my application directory
I created a entity model called northwindEF.edmx

My model looks like this:

my entity database model
If you notice I also added my stored procedure called GetCustomerOrders

In my model browser in the Function Imports folder i have the following for my GetCustomerOrders function:

my GetCustomerOrders Function

Model

So in my models folder I have my class called All.cs where I reference my model, this my code for it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
// added to use sproc only for ef6
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;

namespace FlexigridUsingMvc.Models
{

    // ********       to use sproc  - GetCustomerOrders     ********    
    public class EntContext : DbContext
    {
        // this is the name of the connection string
        public EntContext()
            : base("name=EntContext")
        {
        }

        public virtual DbSet<GetCustomerOrders_Result> GetCustomerOrders_Results { get; set; }

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

    }
    // *******************************************


    //// ********       to use sproc - FETCHEMPLOYEES       ********    
    //public class EntContext : DbContext
    //{
    //    // this is the name of the connection string
    //    public EntContext()
    //        : base("name=EntContext")
    //    {
    //    }

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

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

    //}
    //// *******************************************

}

Open in new window



Controller

This is my code for my controller called HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using FlexigridUsingMvc.Models;
using FlexigridUsingMvc.Extensions;

namespace FlexigridUsingMvc.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<GetCustomerOrders_Result>> EmployeeFiltered = () =>
                    db.GetCustomerOrders_Results
                    .Where(x =>
                            (
                                (
                                    !string.IsNullOrEmpty(letter_pressed)
                                    && x.CustomerID.StartsWith(letter_pressed)
                                )
                                ||
                                string.IsNullOrEmpty(letter_pressed)
                            )
                            &&
                            (
                                (
                                    !string.IsNullOrEmpty(query)
                                    &&
                                    (
                                        (qtype == "OrderID" && x.OrderID.ToString().Contains(query))
                                        ||
                                        (qtype == "CustomerID" && x.CustomerID.Contains(query))
                                        ||
                                        (qtype == "EmployeeID" && x.EmployeeID.ToString().Contains(query))
                                        ||
                                        (qtype == "ShipVia" && x.ShipVia.ToString().Contains(query))
                                        ||
                                        (qtype == "Freight" && x.Freight.ToString().Contains(query))
                                        ||
                                        (qtype == "ShipName" && x.ShipName.Contains(query))
                                        ||
                                        (qtype == "ShipAddress" && x.ShipAddress.Contains(query))
                                        ||
                                        (qtype == "ShipCity" && x.ShipCity.Contains(query))
                                        ||
                                        (qtype == "ShipRegion" && x.ShipRegion.Contains(query))
                                        ||
                                        (qtype == "ShipPostalCode" && x.ShipPostalCode.Contains(query))
                                        ||
                                        (qtype == "ShipCountry" && x.ShipCountry.Contains(query))
                                        ||
                                        (qtype == "CompanyName" && x.CompanyName.Contains(query))
                                        ||
                                        (qtype == "Phone" && x.Phone.Contains(query))
                                          
                                    )
                                )
                                ||
                                string.IsNullOrEmpty(query)
                            )
                        );


                return Json(
                    new
                    {
                        page = page,
                        total = EmployeeFiltered().Count(),
                        rows =
                            EmployeeFiltered()
                            .OrderBy(x => x.OrderID)
                            .LimitAndOffset(pageSize: rp, pageOffset: page)
                            .ToList()
                            .Select(x =>
                                new
                                {
                                    id = x.OrderID,
                                    cell = new string[] { x.OrderID.ToString(), x.CustomerID, x.EmployeeID.ToString(), x.ShipVia.ToString(), x.Freight.ToString(), x.ShipName, x.ShipAddress, x.ShipCity, x.ShipRegion, x.ShipPostalCode, x.ShipCountry, x.CompanyName, x.Phone }
                                })
                    });

            } //using
        } // end of List Function

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

    } // end of HomeController class
}  // end of namespace

Open in new window



View


The code for my view Index.cshtml 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: 'OrderID', name: 'OrderID', width: 60, sortable: true, align: 'center' },
                { display: 'CustomerID', name: 'CustomerID', width: 70, sortable: true, align: 'left' },
                { display: 'EmployeeID', name: 'EmployeeID', width: 60, sortable: true, align: 'left' },
                { display: 'ShipVia', name: 'ShipVia', width: 120, sortable: true, align: 'left' },
                { display: 'Freight', name: 'Freight', width: 80, sortable: true, align: 'center' },
                { display: 'ShipName', name: 'ShipName', width: 80, sortable: true, align: 'center' },
                { display: 'ShipAddress', name: 'ShipAddress', width: 140, sortable: true, align: 'left' },
                { display: 'ShipCity', name: 'ShipCity', width: 60, sortable: true, align: 'left' },
                { display: 'ShipRegion', name: 'ShipRegion', width: 50, sortable: true, align: 'center' },
                { display: 'ShipPostalCode', name: 'ShipPostalCode', width: 70, sortable: true, align: 'left' },
                { display: 'ShipCountry', name: 'ShipCountry', width: 50, sortable: true, align: 'center' },
                { display: 'CompanyName', name: 'CompanyName', width: 80, sortable: true, align: 'center' },
                { display: 'Phone', name: 'Phone', width: 80, sortable: true, align: 'center' }
                ],
                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: 'OrderID', name: 'OrderID' },
                { display: 'CustomerID', name: 'CustomerID', isdefault: true },
                { display: 'EmployeeID', name: 'EmployeeID' },
                { display: 'ShipVia', name: 'ShipVia' },
                { display: 'Freight', name: 'Freight' },
                { display: 'ShipName', name: 'ShipName' },
                { display: 'ShipAddress', name: 'ShipAddress' },
                { display: 'ShipCity', name: 'ShipCity' },
                { display: 'ShipRegion', name: 'ShipRegion' },
                { display: 'ShipPostalCode', name: 'ShipPostalCode' },
                { display: 'ShipCountry', name: 'ShipCountry' },
                { display: 'CompanyName', name: 'CompanyName' },
                { display: 'Phone', name: 'Phone' }
                ],
                singleSelect: true,
                sortname: "CustomerID",
                sortorder: "asc",
                usepager: true,
                title: 'Northwind Customer Orders',
                useRp: true,
                rp: 10,
                showTableToggleBtn: true,
                width: 980,
                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>Northwind Customer Orders - Flexigrid</title>

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

</html>

Open in new window


When I run my application I get this error message in my controller

my error message

I have another stored procedure in my entity model called FETCHEMPLOYEES. That stored procedure just does a select * from employees  . When I use that stored procedure I can get my flexigrid to work fine with that one, because in the Function Imports settings for that function I just tell it I'm using an Employee entity.

But for this example I'm trying to use the stored procedure GetCustomerOrders which does a join of the Customer and Orders tables. So in the Function Imports settings in my entity model it is set to Return a Collection of Complex type.

You can download my Visual Studio 2013 project for my example here:
Download Visual Studio 2013 Project

Anyone know how I can fix this error I'm getting in my controller?
0
maqskywalker
Asked:
maqskywalker
  • 4
  • 3
1 Solution
 
Bob LearnedCommented:
That code dump is very confusing.  

What is the error?

Which controller method are you talking about?

Assuming MVC 5.
0
 
maqskywalkerAuthor Commented:
The code with the error in the picture above is from this file HomeController.cs

This is my code for HomeController.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
// mq
using FlexigridUsingMvc.Models;
using FlexigridUsingMvc.Extensions;

namespace FlexigridUsingMvc.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<GetCustomerOrders_Result>> EmployeeFiltered = () =>
                    db.GetCustomerOrders_Results
                    .Where(x =>
                            (
                                (
                                    !string.IsNullOrEmpty(letter_pressed)
                                    && x.CustomerID.StartsWith(letter_pressed)
                                )
                                ||
                                string.IsNullOrEmpty(letter_pressed)
                            )
                            &&
                            (
                                (
                                    !string.IsNullOrEmpty(query)
                                    &&
                                    (
                                        (qtype == "OrderID" && x.OrderID.ToString().Contains(query))
                                        ||
                                        (qtype == "CustomerID" && x.CustomerID.Contains(query))
                                        ||
                                        (qtype == "EmployeeID" && x.EmployeeID.ToString().Contains(query))
                                        ||
                                        (qtype == "ShipVia" && x.ShipVia.ToString().Contains(query))
                                        ||
                                        (qtype == "Freight" && x.Freight.ToString().Contains(query))
                                        ||
                                        (qtype == "ShipName" && x.ShipName.Contains(query))
                                        ||
                                        (qtype == "ShipAddress" && x.ShipAddress.Contains(query))
                                        ||
                                        (qtype == "ShipCity" && x.ShipCity.Contains(query))
                                        ||
                                        (qtype == "ShipRegion" && x.ShipRegion.Contains(query))
                                        ||
                                        (qtype == "ShipPostalCode" && x.ShipPostalCode.Contains(query))
                                        ||
                                        (qtype == "ShipCountry" && x.ShipCountry.Contains(query))
                                        ||
                                        (qtype == "CompanyName" && x.CompanyName.Contains(query))
                                        ||
                                        (qtype == "Phone" && x.Phone.Contains(query))
                                          
                                    )
                                )
                                ||
                                string.IsNullOrEmpty(query)
                            )
                        );


                return Json(
                    new
                    {
                        page = page,
                        total = EmployeeFiltered().Count(),
                        rows =
                            EmployeeFiltered()
                            .OrderBy(x => x.OrderID)
                            .LimitAndOffset(pageSize: rp, pageOffset: page)
                            .ToList()
                            .Select(x =>
                                new
                                {
                                    id = x.OrderID,
                                    cell = new string[] { x.OrderID.ToString(), x.CustomerID, x.EmployeeID.ToString(), x.ShipVia.ToString(), x.Freight.ToString(), x.ShipName, x.ShipAddress, x.ShipCity, x.ShipRegion, x.ShipPostalCode, x.ShipCountry, x.CompanyName, x.Phone }
                                })
                    });

            } //using
        } // end of List Function

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

    } // end of HomeController class
}  // end of namespace

Open in new window

0
 
Bob LearnedCommented:
Does the model have public virtual properties?  If I understand your problem, I think that error is because of virtual properties.  You can remove the "virtual", and use annotations instead.

Code First Data Annotations
http://msdn.microsoft.com/en-us/data/jj591583.aspx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
maqskywalkerAuthor Commented:
Yes it is using virtual properties.

My model code in my file All.cs looks like this.:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
// mq
using System.Data.Entity;
// mq added to use sproc only for ef6
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;

namespace FlexigridUsingMvc.Models
{

    public class EntContext : DbContext
    {
        // this is the name of the connection string
        public EntContext()
            : base("name=EntContext")
        {
        }

        public virtual DbSet<GetCustomerOrders_Result> GetCustomerOrders_Results { get; set; }

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

    }

}

Open in new window



The error shown in the picture above that I'm getting is on HomeController.cs

This is the code in HomeContoller.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using FlexigridUsingMvc.Models;
using FlexigridUsingMvc.Extensions;

namespace FlexigridUsingMvc.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<GetCustomerOrders_Result>> EmployeeFiltered = () =>
                    db.GetCustomerOrders_Results
                    .Where(x =>
                            (
                                (
                                    !string.IsNullOrEmpty(letter_pressed)
                                    && x.CustomerID.StartsWith(letter_pressed)
                                )
                                ||
                                string.IsNullOrEmpty(letter_pressed)
                            )
                            &&
                            (
                                (
                                    !string.IsNullOrEmpty(query)
                                    &&
                                    (
                                        (qtype == "OrderID" && x.OrderID.ToString().Contains(query))
                                        ||
                                        (qtype == "CustomerID" && x.CustomerID.Contains(query))
                                        ||
                                        (qtype == "EmployeeID" && x.EmployeeID.ToString().Contains(query))
                                        ||
                                        (qtype == "ShipVia" && x.ShipVia.ToString().Contains(query))
                                        ||
                                        (qtype == "Freight" && x.Freight.ToString().Contains(query))
                                        ||
                                        (qtype == "ShipName" && x.ShipName.Contains(query))
                                        ||
                                        (qtype == "ShipAddress" && x.ShipAddress.Contains(query))
                                        ||
                                        (qtype == "ShipCity" && x.ShipCity.Contains(query))
                                        ||
                                        (qtype == "ShipRegion" && x.ShipRegion.Contains(query))
                                        ||
                                        (qtype == "ShipPostalCode" && x.ShipPostalCode.Contains(query))
                                        ||
                                        (qtype == "ShipCountry" && x.ShipCountry.Contains(query))
                                        ||
                                        (qtype == "CompanyName" && x.CompanyName.Contains(query))
                                        ||
                                        (qtype == "Phone" && x.Phone.Contains(query))
                                          
                                    )
                                )
                                ||
                                string.IsNullOrEmpty(query)
                            )
                        );


                return Json(
                    new
                    {
                        page = page,
                        total = EmployeeFiltered().Count(),
                        rows =
                            EmployeeFiltered()
                            .OrderBy(x => x.OrderID)
                            .LimitAndOffset(pageSize: rp, pageOffset: page)
                            .ToList()
                            .Select(x =>
                                new
                                {
                                    id = x.OrderID,
                                    cell = new string[] { x.OrderID.ToString(), x.CustomerID, x.EmployeeID.ToString(), x.ShipVia.ToString(), x.Freight.ToString(), x.ShipName, x.ShipAddress, x.ShipCity, x.ShipRegion, x.ShipPostalCode, x.ShipCountry, x.CompanyName, x.Phone }
                                })
                    });

            } //using
        } // end of List Function

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

    } // end of HomeController class
}  // end of namespace

Open in new window



This is the error I'm getting:

"An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: The type 'GetCustomerOrders_Result' is mapped as a complex type. The Set method, DbSet objects, and DbEntityEntry objects can only be used with entity types, not complex types."


In my model northwindEF.edmx in the Function imports folder I have the function GetCustomerOrders pertaining to my stored procedure set to complex type. Because I'm setting it to complex type I believe I have to revise my code because I'm setting it to complex type.

But I don't know what I need to revise to accomodate the complex type stored procedure function.
0
 
maqskywalkerAuthor Commented:
I'm using MVC 5.
0
 
Bob LearnedCommented:
Did you use Code First, or did you generate the data context from an existing database?  

What is GetCustomerOrders_Result?
0
 
maqskywalkerAuthor Commented:
I'm using "EF Designer from Database".  So yes I  generated the data context from an existing database.

This is the steps I followed in my project to create the model

I right clicked my models folder and selected ADO.NET Entity Data Model and named it northwindEF and then clicked add.

step1
So now on the Choose Model Contents window I chose EF Designer from database and then clicked Next.

step 2

So then in the Choose Your Database Objects and Settings window i checked the Customers and Orders tables and the GetCustomerOrders stored procedure and then clicked Finish.

Step 3
After I click finish my model is created.

my model diagram for northwindEF.edmx
If you click on northwindEF.edmx in solution explorer and then click on the Model Browser you can see the properties for the model that was created by Visual Studio.

select model browser tab
If you open the function Imports folder you can see GetCustomerOrders in that folder.

Double click on that and you can see the function imports properties which look like this:

Function Imports window for GetCustomerOrders
GetCustomerOrders_Result was generated by Visual Studio.  

I believe it's the function containing the result set of my stored procedure.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now