KB Article K18520
Visible to All Users

How to adjust GridView to work with Stored Procedures (EntityFramework)

Description:
Entity Framework (EF) is an object-relational mapping (ORM) framework for the .NET Framework. The article describes how to achieve the following tasks:

  1. Create an Entity DataContext object mapped to an existing database;
  2. GridView is bound to a result returned by a selection Stored Procedure;
  3. Inserting and Deleting operations are performed using Stored Procedures.

    Answer:
    The stored procedure is a subroutine, which is executed on the database server side. This is an important statement because this means that a result of the stored procedure cannot be modified on the application server side before the stored procedure execution. For example, the Server Mode functionality cannot be implemented. If the stored procedure returns a million of records, the grid will not be able to tell the database server to give it a small portion of data because these records are already fetched by the server.
    To create a small application and connect it with a database by using Entity Framework, you should perform the following steps:
  4. The MVC Project should be created: File -> New Project. It is easier to use the MVC Project Template for DevExpress MVC Extensions.
  5. If you do not have Entity Framework installed, you can do this via NuGet Package Manager:
  6. In the opened window select the EntityFramework package:
  7. When the library is installed, the EntityFramework.dll file will be copied to the Bin folder. To bind the available database schema to an object, the Entity Data Model should be created first (e.g. in the Models folder):

    Let us give this Model the SupportTeam name.
  8. When the .edmx file is created, entities should be created from a database by using the Generate from Database option:
    MSDN - How to: Generate a Database from a Conceptual Model (Entity Data Model Tools)
  9. During the schema import, it is possible to choose individual tables/views/stored procedures, which should be generated:
  10. After the Model is created, we should import stored procedures as functions in the Model Browser:

    In the context of this article a small database is used. It has a single table and three stored procedures (Select, Insert and Delete). Script files are attached to this article.
  11. A selection stored procedure should be imported in the order as illustrated in the image.

    Since the result of a stored procedure contains only two columns instead of three and the returned object is not an entity, we should create a complex object. The object has public properties, which represent returned records.
    P.S. If we check a result of the imported function, we will see that it is of the ObjectResult<T> type. This type does not implement the IQueryable interface. That is why, the Server Mode cannot be achieved using LINQ expressions.
  12. Insert and Delete stored procedures do not return collections and thus they are exported in a simpler way:
  13. When all functions are imported, they can be used with the GridView MVC extension. For example, the following code retrieves values from the SellectSupportTeam procedure and passes them to a View where the grid is:
C#
public ActionResult SupportGridPartial(){ StoredProcedureEntities context = new StoredProcedureEntities(); var model = context.SelectSupportTeam(); return PartialView("SupportGridPartial", model); }
Visual Basic
Public Function SupportGridPartial() As ActionResult Dim context As New StoredProcedureEntities() Dim model = context.SelectSupportTeam() Return PartialView("SupportGridPartial", model) End Function

In the same way values entered by a user can be obtained in a Controller's Action and passed to a stored procedure:

C#
[HttpPost] public ActionResult SupportGridInsert([ModelBinder(typeof(DevExpressEditorsBinder))] SelectSupportTeam_Result supportEngineer) { StoredProcedureEntities context = new StoredProcedureEntities(); var result = context.InsertSupportEngineer(supportEngineer.Name).First(); // inserted key value
Visual Basic
<HttpPost> _ Public Function SupportGridInsert(<ModelBinder(GetType(DevExpressEditorsBinder))> ByVal supportEngineer As SelectSupportTeam_Result) As ActionResult Dim context As New StoredProcedureEntities() Dim result = context.InsertSupportEngineer(supportEngineer.Name).First() ' inserted key value

Principles from this article are used in the Code Central example that works with Stored Procedures mapped to Entity Framework:
How to adjust GridView to work with Stored Procedures (EntityFramework)
See Also:
How to implement common scenarios when using ASPxGridView bound with EntityDataSource / Entity Framework

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.