Using Stored Procedures to load structured data.

Artigo
07/18/2008

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

Mapping Create, Update and Delete entity operations to appropriate stored procedures.
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.

Allowing FunctionImports to return unattached/untracked Entities.
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:

The mapping involves following three steps:

Define import function definition in SSDL:

Expose function definition in CSDL schema file

Define mapping (convention based) MSL file:

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.

Comments

Anonymous
September 16, 2008
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
Anonymous
October 03, 2008
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.
Anonymous
December 07, 2008
Should be able to create DTO's for simple stored procedures (returning only one result set )
Anonymous
December 31, 2008
What if I want to fill several entities from a single stored procedure like I used to when working with typed datasets ?
Anonymous
October 15, 2009
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?
Anonymous
October 21, 2009
please check this post also : http://ledomoon.blogspot.com/2009/10/stored-procedure-mapping-and-usage-into.html
Anonymous
April 05, 2010
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.
Anonymous
April 06, 2010
@thanhhh, This kind of question will really get a better response if you ask it in the forums. Try: http://social.msdn.microsoft.com/Forums/en-US/adonetefx/threads 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

Anonymous
January 24, 2011
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.

Compartilhar via

Using Stored Procedures to load structured data.

Scenario:

Customer Goal:

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:

The mapping involves following three steps:

Define import function definition in SSDL:

Expose function definition in CSDL schema file

Define mapping (convention based) MSL file:

Design Details:

Assumptions/suppositions:

Comments

Recursos adicionais

Compartilhar via

Using Stored Procedures to load structured data.

Scenario:

Customer Goal:

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:

The mapping involves following three steps:

Define import function definition in SSDL:

Expose function definition in CSDL schema file

Define mapping (convention based) MSL file:

Design Details:

Assumptions/suppositions:

Comments

Recursos adicionais

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