Using Stored Procedures to load structured data.

V1 of the Entity Framework allows you to use stored procedures in two main ways:

  1. Mapping Create, Update and Delete entity operations to appropriate stored procedures.
  2. Doing a FunctionImport that allows you to return an enumeration of Entities*

Now the thing is, in order to return an enumeration of Entities, you have to map the Entity too.


Well in V1 FunctionImport attaches the returned entities, so you can make and save changes.

But of course sometimes you don’t want that functionality, you just want a structured way of moving information around, so it is a shame to be required to do the mapping too.

This leaves us two possibilities for V2.

  1. Allowing FunctionImports to return unattached/untracked Entities.
  2. Allowing FunctionImports to return ComplexTypes.

The ComplexTypes option is the topic of the following one-pager by Asad a Program Manager on the Entity Framework team:


Customer Goal:

The customer wants to do something like this in their .NET code:

public CustomerInfo GetCustomerInfo(int Id)
    using (NorthwindEntities ctx = new NorthwindEntities())
        CustomerInfo info = ctx.GetCustomerInfoById(Id).FirstOrDefault();
        return info;

Without needing to create mappings etc, as this lowers the level of friction inherent in using stored procedures for queries with the Entity Framework. 

NB: today we only support Collection(Type) as the return type of the FunctionImport, which means it results in an Enumeration, hence the call to FirstOrDefault(). A separate work item is required to support returning just one ComplexType (or EntityType) rather than collections.

Detailed Scenario walk through:

User defines a Stored Procedure in store:

Create Procedure GetCustomerInfoById(
        @Id int
    SELECT first_name As Firstname, last_name As Lastname, city As City 
    FROM CustomerTable
    WHERE id = @Id

Complex type definition in EDM:

User wants to map the result set from this stored procedure to a Complex type “CustomerData” defined in Entity Data Model as:

  <ComplexType Name=CustomerData>
    <Property Name=Firstname Type=String MaxLength=50 />
    <Property Name=Lastname Type=String MaxLength=50 />
    <Property Name=“City Type=String MaxLength=50 />

The mapping involves following three steps:

Define import function definition in SSDL:

<Function Name=GetCustomerInfoById Aggregate=false BuiltIn=false NiladicFunction=false IsComposable=false ParameterTypeSemantics=AllowImplicitConversion Schema=dbo>
  <Parameter Name=Id Type=int Mode=In />

Expose function definition in CSDL schema file

<EntityContainer Name=CustomerEntityContainer
  <FunctionImport Name=GetCustomerInfoById”  ReturnType=Collection(Self.CustomerData)>
<Parameter Name=Id Mode=In Type=Int32 /> 

Define mapping (convention based) MSL file:

<EntityContainerMapping StorageEntityContainer=StoreContainer CdmEntityContainer=CustomerEntityContainer
  <FunctionImportMapping FunctionImportName=GetCustomerInfoById FunctionName=StoreNamespace.GetCustomerInfoById />

Design Details:

       Mapping between Complex type and result type is by convention.

       Explicit property to column name mapping is not supported.

       The result from the stored procedure has to exactly match the shape and the property names of the complex types.

       In EDM the result is captured in a Complex type therefore no EntitySet definition or mapping is required.


       A future work item (covered by a separate feature entry) would enable richer mapping capability by allowing column renames. However for now Convention based mapping is the only supported feature.

Looking forward to hearing your feedback.

Alex James
Program Manager,
Entity Framework Team

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at this post.

*In V1 it is also possible to do a FunctionImport that returns a scalar value, but this FunctionImport is not available in Object Services, if you want to use it you have to drop down to eSQL in the EntityServices layer. 

Comments (9)

  1. eric says:

    This is a feature that V1 should really have had and where it las badly behind L2S. Working with an existing db with a strong investment in SPs in v1 is really problematic. I have tried using a DTO pattern to create entities for the output of the SPs – but that leads to other issues if those DTO entities (which are not mapped to table in anyway) exist on an edm which you are also using for table mappings. The error you get when you access a mapped entity such as Order or Employee is:

    error 3027: No mapping specified for the following EntitySet/AssociationSet – ProductsDTOs

  2. Dathan says:

    I think the Complex Type approach works well for many scenarios, however supporting both approaches may be best.  Certain cases where the shape of return data is highly flexible and not track-worthy would make the CT approach onerous.

  3. vairam v says:

    Should be able to create DTO’s for simple stored procedures (returning only one result set )

  4. yaniv says:

    What if I want to fill several entities from a single stored procedure like I used to when working with typed datasets ?

  5. Vern says:

    What about the situation where the intent is to return complex data that is not an Entity?   I have an SP where I verify inputs and alter related entries across 4 tables, the SP returns values from a 3-column temp table.

    What is the best-practice for handling this situation?

  6. thanhhh says:

    When i create FunctionImport at step "Expose function definition in CSDL schema file", it takes the following error:

    Error 146: Return type is not valid in FunctionImport ‘{FunctionImportName}’. The FunctionImport must return a collection of scalar values or a collection of entities.

    Please help me.

  7. simmdan says:


    This kind of question will really get a better response if you ask it in the forums.  Try:

    In any case, though, one of the first questions that should be asked is: what version of .net do you have?  Is it 3.5sp1?  Or do you have .net 4 beta 1, beta 2, RC or what?

    – Danny

  8. Brent Green says:

    This is ridiculous.  I use stored procedures to return declarative tables that are not tied to just one table.  I am mad that I have to move all of my current code where I used Object Data Sources to the new EDMX style (RIA) to support Silverlight.  I just wish things would work with old elements.  Why can't we have some sort of framework in Silverlight that supports the Object data source?  I just spent over two hours designing a Report.edmx file using all of the reporting stored procedures in our system and now come to find that it will not work because I do not have a mapping to an actual table or view.

    I am using Visual Studio 2010 Version 10.0.30319.1 RTMRel on .NET Framework 4.0.30319 RTMRel.