A Stored procedure that is defined in a database can be exposed in a conceptual model in two ways:

  • Create a function import in the conceptual model that maps to a stored procedure.  This allows a method to be defined on the ObjectContext that executes the stored procedure in the database.  Depending on how the stored procedure is defined, the corresponding function import can return entity types or complex types.
  • Map the insert, update and delete operations for an entity type to stored procedures.  This allows defining custom insert, update and delete behaviors for entity types.

This blog describes how to create a function import in a conceptual model that maps to a stored procedure in the database and using that stored procedure to retrieve a collection of entity types.   For information about mapping the insert, update and delete operations for entity types to stored procedures, see Using Teradata Stored Procedures to Insert, Update and Delete in Entity Framework.

Including a stored procedure in the Entity Data Model (EDM) allows the stored procedure to be called from the application code.  When a store procedure is added to the conceptual model, it is called a Function Import.

To demonstrate let’s start with a very simple EF model with a single entity which represents a Category.
 

 

Use the following stored procedure for the Category entity:

Select:

CreatePROCEDURE GetCategories ()
Dynamic Result Sets 1
BEGIN
   Declare
cur1 cursor with return only to client for
   SELECT
CategoryID, CategoryName FROM  Category;
   open cur1;
END;

 

1.  Creating a Function Import

     a.  First, let’s include the stored procedure in the EDM by right clicking on the entity,
          then select Update Model from Database… as shown below:
    
  

     The Choose Your Database Objects dialog will be displayed; select the stored procedures to add to the
     EF model as shown below:

       
  

     b.  Now, import the Function.  Right click on the designer, then select Add -> Function Import…

    


     The Add Function Import dialog will be displayed;  fill in the details as shown below:

    

 

2.  Modifying the SSDL

     Microsoft implementation of run-time and design-time tools for Entity Framework is targeted to the users of
     MS SQL Server.  Current functionality doesn’t provide full support for specific features of Teradata Stored
     Procedures.  Specifically, the MaxLength, Precision and Scale facets are not written out to the SSDL for a
     function parameter.  To guarantee correct MaxLength, Precision and Scale for function parameters, the SSDL
     must be manually modified to include these facets where relevant for the Teradata types below.  Otherwise,
     the Entity Provider for Teradata will assume the value in the DefaultValue attribute from the manifest. 

  1. Blob
  2. Byte
  3. Char
  4. Clob
  5. Decimal
  6. Interval Day to Second
  7. Interval Second
  8. Interval Month
  9. Interval Year
  10. Interval Year to Month
  11. Period Types
  12. Time
  13. Timestamp
  14. Time With Time Zone
  15. VarByte
  16. VarChar

     Refer to the ADO.NET Entity Provider for Teradata book in the .NET Data Provider for Teradata documentation
     for more details on the Data Type Mapping and Restrictions of the Visual Studio Entity Data Model Wizard.

 
3.  Retrieving Entity Types with a Stored Procedure

     In this procedure you will add code that executes the Function Import that you previously created from the
     GetCategories store procedure. 
 

     using(ExampleEntities context = new ExampleEntities())
    {

         // query the categories
         // Note: this is not the same as creating and executing a query.  The function will be
         // executed immediately when the function is called in code.  The execution  will not be deferred.

         var result = context.GetCategories(); 

         foreach(Category c in result)
        {
             Console.WriteLine("{0} {1}", c.CategoryID, c.CategoryName);
        }

    }

 

Conclusion

The Entity Framework provides us the built-in capability to import stored procedures as functions.  We can then map the results of the function back to our entity types. 

Discussion
Jasonviera 2 comments Joined 02/12
26 Mar 2012

After following your example I get the following error:
Keyword not supported: 'user id'.

Only a fool tests the depths of the water with both feet.

Jasonviera 2 comments Joined 02/12
26 Mar 2012

Ok so I fixed the connection string problem but now I get the following error:

'((result).ElementType).DeclaringMethod' threw an exception of type 'System.InvalidOperationException'

{"Method may only be called on a Type for which Type.IsGenericParameter is true."}

Only a fool tests the depths of the water with both feet.

You must sign in to leave a comment.