Returning Multiple Result Sets from an Entity Framework Stored Procedure with Function Import Mappings

Introduction

Returning multiple result sets from a stored procedure has been supported since Entity Framework (EF) 5.0. However, the approach of mapping configurations for the feature using the EF designer has not been available even from EF 6.0. All business data application more or less needs to retrieve multiple result sets of data from stored procedures to avoid multiple calls. In addition, returning types of stored procedures are usually very complex than the simple entity types. This article provides the details of configuring the Function Import mappings for a stored procedure in the EF designer to return multiple result sets with complex types.

Sample Table and Stored Procedures

I have created one table named is “Topics” which contains three fields named is “Id, TopicName and Description”. I have created once procedure called ”GetTopics” to return the all topics. I am returning two result set from this procedure one query is returning all the columns of table Topics and other once is returning limited column from the Topics table.  Below is the script.

Table and stored procedure that returns two result sets:
–Table

CREATE TABLE [dbo].[Topics] (
[Id] int IDENTITY(1,1) NOT NULL,
[TopicName] varchar(50) NOT NULL,
[Description] varchar(max) NOT NULL
);

–Store procedure

CREATE PROCEDURE GetTopics
AS
BEGIN
SELECT * FROM Topics</div>
SELECT T.Id,T.TopicName FROM Topics T
END

Create Visual Studio Project with Entity Data Model

Follow the simple steps which you follow for your application or you can follow MSDN tutorial.

Step1- After creating the project adds the entity framework file to the application, at adding time you get a popup of setting database connect after that you get popup to select your database table, views and procedure. So, select Topics table and GetTopics procedure in order to add in your edmx file and let the compiler to create the ComplexType to your procedure.

Step2-
1st Query: System will automatically create this complextype for your first query of procedure as I sad above our first query returning the entire row so we can see we have 3 fields in the GetTopics_Result ComplexType to return the entire column.
2nd Query: As we seen in the procedure our second query is returning limited column so I have created one ComplexType that is GetTopic_Topic which have only two columns to return the limited column
Note: System will never generate complextype for all the result set it will create for first result only.
Step3- Now right clicks on your EDMX file and opens it as XML editor in order to map your complex type with your function GetTopics.

Basically system creates three type of content for user database, below are the names.

  1. SSDL content which parent tag name is
     <edmx:StorageModels> </edmx:StorageModels>
  2. CSDL content which parent tag name is
    <edmx:ConceptualModels> </edmx:ConceptualModels>
  3. CS content mapping which parent tag is
    <edmx:Mappings> </edmx:Mappings>

You not need to edit anything in your SSDL content you required to change in your CSDL content.

After adding the procedure you will see this code in your CSDL content to return the result.

Code1-

<EntityContainer Name="SampleEntities" annotation:LazyLoadingEnabled="true">
<EntitySet Name="Topics" EntityType="SampleModel.Topic" />
<FunctionImport Name="GetTopics" ReturnType="Collection(SampleModel.GetTopics_Result)">
</FunctionImport>
</EntityContainer>
<ComplexType Name="GetTopics_Result">
<Property Type="Int32" Name="Id" Nullable="false" />
<Property Type="String" Name="TopicName" Nullable="false" MaxLength="50" />
<Property Type="String" Name="Description" Nullable="false" />
</ComplexType>

And in your CS content tag you will see the complex type mapping with the GetTopics function below is the code.

Code2-

<FunctionImportMapping FunctionImportName="GetTopics" FunctionName="SampleModel.Store.GetTopics">
<ResultMapping>
<ComplexTypeMapping TypeName="SampleModel.GetTopics_Result">
<ScalarProperty Name="Id" ColumnName="Id" />
<ScalarProperty Name="TopicName" ColumnName="TopicName" />
<ScalarProperty Name="Description" ColumnName="Description" />
</ComplexTypeMapping>
</ResultMapping>
</FunctionImportMapping>

Now as per our requirement we need to map our 2nd result set and create complex type for our 2nd result set. As we already have created one complex type named is “GetTopics_Topic” which have two properties and complier will automatically create this complex type in our SSDL content below is the code. This code will get added in our Code1 this heading is declared above.

<ComplexType Name="GetTopics_Topic" >
<Property Type="Int32" Name="Id" Nullable="false" />
<Property Type="String" Name="TopicName" Nullable="false" />
</ComplexType>

Now we have to map our complex type with FunctionImportMapping in order to make able this function to return multiple result set.

As we can see our Code2 which already mapped with GetTopics_Result complex type we need to add one more ResultMappingTag for GetTopic_Topic complex type after adding this Code2 will looks like this see below code.

<FunctionImportMapping FunctionImportName="GetTopics" FunctionName="SampleModel.Store.GetTopics">
<ResultMapping>
<ComplexTypeMapping TypeName="SampleModel.GetTopics_Result">
<ScalarProperty Name="Id" ColumnName="Id" />
<ScalarProperty Name="TopicName" ColumnName="TopicName" />
<ScalarProperty Name="Description" ColumnName="Description" />
</ComplexTypeMapping>
</ResultMapping>
<ResultMapping>
<ComplexTypeMapping TypeName="SampleModel.GetTopics_Topic">
<ScalarProperty Name="Id" ColumnName="Id" />
<ScalarProperty Name="TopicName" ColumnName="TopicName" />
</ComplexTypeMapping>
</ResultMapping>
</FunctionImportMapping>

Now will change the (Code1) FunctionImport tag to return the multiple result code as follows.

<FunctionImport Name="GetTopics">
<ReturnType  Type="Collection(SampleModel.GetTopics_Result)"></ReturnType>
<ReturnType Type="Collection(SampleModel.GetTopics_Topic)"></ReturnType>
</FunctionImport>

Step4- C# code how to access this GetTopics function in the code below is the code.

using (var db = new SampleEntities())
{
var firstResult = db.GetTopics();
var secondResult = db.GetTopics().GetNextResult&lt;GetTopics_Topic&gt;();
Console.WriteLine("Writing Full Details of Topic");
foreach (var item in firstResult)
{
Console.WriteLine("Id={0}, Name={1}, Description={2}", item.Id, item.TopicName, item.Description);
}

Console.WriteLine("Writing Limited Details of Topic");
foreach (var item in secondResult)
{
Console.WriteLine("Id={0}, Name={1}", item.Id, item.TopicName);
}
}

Summary:

In this article I tried to explain and implement the return multiple results set from entity framework example. I hope after reading this article you will be able to return multiple results set from entity framework. I would like to have feedback and question from my blog reads, please post your comment and question in order to make it more useful. You can download code from here Returning Multiple Result Sets from an Entity Framework Stored Procedure with Function Import Mappings .

result set from Entity Framework.

5 thoughts on “Returning Multiple Result Sets from an Entity Framework Stored Procedure with Function Import Mappings

  1. is this is applicable for stored procedures which takes arguments ? i get the below error in the EDMX file

    Error 445 Error 5: The element ‘FunctionImport’ in namespace ‘http://schemas.microsoft.com/ado/2008/09/edm’ has invalid child element ‘ReturnType’ in namespace ‘http://schemas.microsoft.com/ado/2008/09/edm’. List of possible elements expected: ‘Documentation, Parameter’ in namespace ‘http://schemas.microsoft.com/ado/2008/09/edm’ as well as any element in namespace ‘##other’.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s