Share via

How to Map Stored Procedures using the ADO.NET Entity Framework

  • Article

 

This example provides the basic elements of schema syntax required to map a stored procedure to an Entity Data Model implementation. Implementing stored procedures currently requires manual modifications to the *.edmx file. This example describes the schema syntax and demonstrates how to use stored procedures now.

The sample uses the AdventureWorks database that ships with SQL Server 2005. Only a subset of this large database will be required. You can find an implementation of the model in the documentation topic: https://msdn2.microsoft.com/en-us/library/bb387147(VS.90).aspx . The AdventureWorks Sales Model is an Entity Data Model (EDM) implementation based on the tables in the AdventureWorks sample.

Five entities are declared:

  • Address
  • Contact
  • Product
  • SalesOrderDetail
  • SalesOrderHeader

This example shows how to implement a stored procedure to return the data contained by the SalesOrderDetail tables related to a single SalesOrderHeader. (The FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID association in this model can do the same thing as this example).

Execute the following query command to implement the stored procedure in the Adventureworks database:

USE AdventureWorks;

GO

IF OBJECT_ID ( 'dbo.GetOrderDetails', 'P' ) IS NOT NULL

    DROP PROCEDURE dbo.GetOrderDetails;

GO

CREATE PROCEDURE dbo.GetOrderDetails

   @SalesOrderHeaderId int

AS

    SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,

            OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,

            rowguid, ModifiedDate

    FROM Sales.SalesOrderDetail

WHERE SalesOrderID = @SalesOrderHeaderId;

GO

Store Schema Definition Language (SSDL) Requirements

When you select the GetOrderDetails stored procedure along with the five tables in the Entity Data Model wizard, a Function element is generated as part of the AdventureWorks Sales Model. The result in the ssdl schema segment of the *.edmx file will look like this.

<Function Name="GetOrderDetails" Aggregate="false"

    BuiltIn="false" NiladicFunction="false"

    IsComposable="false"

    ParameterTypeSemantics="AllowImplicitConversion"

    Schema="dbo">

        <Parameter Name="SalesOrderHeaderId" Type="int" Mode="in" />

</Function>

To see this syntax, open the *.edmx file with the Visual Studio XML editor. If the model was generated before you created the stored procedure, simply add the Function syntax to the ssdl segment of the schema inside the Schema tags but not inside the EntityContainer tags.

That's all that's needed in the SSDL segment of the *.edmx file.

Conceptual Schema Definition Language (CSDL) Requirements

Next we need to implement the function import statement in the CSDL segment of the *.edmx file. Add the following XML to the EntityContainer of the csdl segment:

<FunctionImport Name="GetOrderDetails"

    EntitySet="SalesOrderDetail"

    ReturnType="Collection(AdventureWorksModel.SalesOrderDetail)">

  <Parameter Name="SalesOrderHeaderId" Type="Int32" Mode="in">

  </Parameter>

</FunctionImport>

That takes care of the CSDL schema requirements.

Mapping Specification Language (MSL) Requirements

Next, the function import from the CSDL model must be mapped to the SSDL model in the MSL portion of the *.edmx file (look for “C-S mapping content”). The function is mapped in the following syntax inside the EntityContainerMapping:

<FunctionImportMapping FunctionImportName="GetOrderDetails"

       FunctionName="AdventureWorksModel.Store.GetOrderDetails"/>

Rebuild the model, and the stored procedure with the required parameter should show up in the Object Browser as a method on the AdventureworksEntities namespace: GetOrderDetails(int).

Using the Method in Code

The stored procedure is used in the following code to enumerate results in a foreach loop.

using (AdventureWorksEntities db = new AdventureWorksEntities())

    {

        int soHeaderNumber = 43659;

        foreach (SalesOrderDetail order in db.GetOrderDetails(soHeaderNumber))

                   Console.WriteLine("Header#: {0} " +

                   "Order#: {1} ProductID: {2} Quantity: {3} Price: {4}",

                   soHeaderNumber, order.SalesOrderDetailID, order.ProductID,

                   order.OrderQty, order.UnitPrice);

    }

The output should look like this:

Header#: 43659 Order#: 1 ProductID: 776 Quantity: 1 Price: 2024.9940

Header#: 43659 Order#: 2 ProductID: 777 Quantity: 3 Price: 2024.9940

Header#: 43659 Order#: 3 ProductID: 778 Quantity: 1 Price: 2024.9940

Header#: 43659 Order#: 4 ProductID: 771 Quantity: 1 Price: 2039.9940

Header#: 43659 Order#: 5 ProductID: 772 Quantity: 1 Price: 2039.9940

Header#: 43659 Order#: 6 ProductID: 773 Quantity: 2 Price: 2039.9940

Header#: 43659 Order#: 7 ProductID: 774 Quantity: 1 Price: 2039.9940

Header#: 43659 Order#: 8 ProductID: 714 Quantity: 3 Price: 28.8404

Header#: 43659 Order#: 9 ProductID: 716 Quantity: 1 Price: 28.8404

Header#: 43659 Order#: 10 ProductID: 709 Quantity: 6 Price: 5.7000

Header#: 43659 Order#: 11 ProductID: 712 Quantity: 2 Price: 5.1865

Comments

  • Anonymous
    September 14, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/09/14/how-to-map-stored-procedures-using-the-adonet-entity-framework/

  • Anonymous
    October 15, 2007
    How do I setup the CSDL/SSDL/MDL for a SP that returns no resultset? I want to do a SP for inserting a row into a table.. How can I do that.. Thanks....Paul

  • Anonymous
    November 07, 2007
    Entity Framework FAQ Version 0.1 – first draft Contents 1. Introduction . 3 1.1. Why use EDM? How does

  • Anonymous
    November 07, 2007
    Version 0.1 – first draft, November 8, 2007 Contents 1. Introduction 1.1. About this FAQ… 1.2. Where

  • Anonymous
    April 09, 2008
    How do I setup the CSDL for an SP that returns a join of 2 tables?  How to specify the ReturnType in the <FunctionImport>??

  • Anonymous
    May 21, 2008
    How has it been changed in beta 1? Could it be done in the visual designer?

  • Anonymous
    June 11, 2008
    The Entity Framework enables developers to reason about and write queries in terms of the EDM model rather than the logical schema of tables, joins, foreign keys, and so on. Many enterprise systems have multiple applications/databases with varying degrees

  • Anonymous
    July 14, 2008
    Hai      I have been using the NorthWind Database and have been trying to map one of its stored procedures by name “  GetTenMostExpensiveProducts” through the EDM model using <FuctionImport> . I have added the following pieces of code in the  csdl and the msl files respectively. <FunctionImport Name="GetTenMostExpensiveProducts" EntitySet="Products" ReturnType="Collection(Self.Products)" /></EntityContainer>        <EntityType Name="Customers"> <FunctionImportMapping FunctionImportName="GetTenMostExpensiveProducts" FunctionName="NorthwindModel.Store.Ten_Most_Expensive_Products" /></EntityContainerMapping> There is no parameter in my example as this is returning just the ten most expensive products. Now since the stored procedure is not returning all the row information , when I execure the stored procedure from my application I get this following exception The data reader is incompatible with the specified 'NorthwindModel.Products'. A member of the type, 'ProductID', does not have a corresponding column in the data reader with the same name. It might be because the EDM is trying to map its entities with the column names. The problem is solved when I return all the rows from the stored proc by giving a “ select *   “ . I would be grateful if you could give me feedback on the same and help me solve the problem. Regards Krishna chandran Developer MindTree consulting.

  • Anonymous
    August 05, 2008
    @Paul: Have a look at: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/17/ado-net-entity-framework-tools-stored-procedures.aspx Regards, Flo

  • Anonymous
    August 09, 2008
    Part of the Entity Framework FAQ . 13. EDM 13.1. Does Entity Framework support Abstract types in EDM

  • Anonymous
    August 17, 2008
    Has anyone found a solution to the problem described by Krishna Chandran?  I can repeat this error by using an ObjectDataSource and, in a BLL method, returning a generic list from a sproc.  Any help would be much appreciated.   Thanks.

  • Anonymous
    September 30, 2008
    Can anyone tell the solution to the problem stated by Krishna Chandran. Even I am facing the same problem. Thanks. John

  • Anonymous
    October 15, 2008
    I'm there with Krishna Chandran.  I create an entity and map it to a function import but there does not appear to be a way to map an entity member to a column number or name returned by the stored procedure. Is this correct?

  • Anonymous
    November 17, 2008
    Hi.. I'm with the same problem of the Krishna... Yet with no one solution... Some response from ADO .NET team?

  • Anonymous
    November 20, 2008
    After modifying all three sections, you say "Rebuild the model"  How do you "Rebuild the model"?

  • Anonymous
    December 21, 2008
    I am also looking for a solution to Krishna's problem.  It would be nice to get some sort of acknowledgment or explanation from the EF developers on this.

  • Anonymous
    December 31, 2008
    I have just tried to map a SQL Stored Procedure and am also encountering the same problem as described by Krishna.  Has anyone been able to resolve this issue with SPROC mapping with the Entity Framework?

  • Anonymous
    December 31, 2008
    After doing some testing and modifications to my SPROC, I noticed that it seems that if the SPROC is not returning columns names that are a one to one match to the property names of the Entity Object that you are mapping it to, you will receive this message.  For example, in my table I have a column named Product_Id, my model has a property named ProductId, it seems the datareader is returning the column name as Product_Id and this cannot map to the Entity Object that is mapped to the SPROC.  My SPROC is simply performing a 'SELECT *...' but I guess this would require one to actually write out all the column names and set up aliases that match the property name and case of the Entity Properties.  There must be a better way to do this, team?

  • Anonymous
    February 09, 2009
    I am having the same problem as described by krishna. This seems like this is a bug.  If I change the entity property name to match the column name, everything works.  

  • Anonymous
    August 05, 2009
    We have written a tutorial about working with Oracle stored procedures and Entity Framework here: <a href="http://www.devart.com/blogs/dotconnect/?p=5">http://www.devart.com/blogs/dotconnect/?p=5 </a>

  • Anonymous
    September 10, 2009
    Is there a solution to The data reader is incompatible with the specified '   '. A member of the type, '    ', does not have a corresponding column in the data reader with the same name.

  • Anonymous
    November 14, 2009
    I mapped my stored proc as mentioned above but it doesnt appear as object in my data context although it appeared before ,can anyone help?

  • Anonymous
    January 25, 2010
    It only seems you can use the SP Functions if you return an entity collection.  If you select 'none' or 'scalar' then the fuction does not appear added to the context.  Not sure if it just gets added elsewhere in these cases