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.

Why?

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:

Scenario:

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
    )
As
    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" />
  </ComplexType>

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" />
</Function>

Expose function definition in CSDL schema file

Define mapping (convention based) MSL file:

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

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.

Assumptions/suppositions:

-       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.