Practical Introduction To Entity Framework: Day 4

The following are my previous articles on the fundamentals of Entity Framework

• Practical Introduction To Entity Framework: Day 1
• Practical Introduction To Entity Framework: Day 2
• Practical Introduction To Entity Framework: Day 3

To start with this article please read all 3 previous articles to understand better or see this video https://www.youtube.com/v/b6vTIiBNcJ0

I am assuming you are good in Entity Framework now, so let's start.

How to return multiple Result sets or Data sets in an entity framework?

Many developers do not know when they use Entity Framework whether it is very easy to implement and after updating the edmx whether the changes are still present.

Step 1. Create a normal stored procedure

Create a normal Stored Procedure that will return multiple select statements

CREATE PROCEDURE GetCustomerProductDetails    
AS    
BEGIN    
    SELECT Id, Name from Customer    
    SELECT ProductID, ProductName from product    
END    
GO

Step 2. Create a Web project and add an ADO Entity Model

Create a Web project and add an ADO Entity Model as in the following

Step 3. Select the procedure for returning multiple result sets

Now select the procedure you want to use for returning multiple result sets.

Step 4. Open the edmx file in the XML editor

Open the edmx file in the XML editor as in the following screens.

Step 5. Add multiple result mapping

Add multiple return types as per your requirements in the Function import Node and add Multiple Complex Types also

<edmx:ConceptualModels>
    <Schema Namespace="MyModel" Alias="Self" p1:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:p1="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
        <EntityContainer Name="CustomerManagementEntities" p1:LazyLoadingEnabled="true">
            <FunctionImport Name="GetCustomerProductDetails">
                <ReturnType Type="Collection(MyModel.GetCustomerProductDetails_Result1)" />
                <ReturnType Type="Collection(MyModel.GetCustomerProductDetails_Result2)" />
            </FunctionImport>
        </EntityContainer>
        <ComplexType Name="GetCustomerProductDetails_Result1">
            <Property Type="Int32" Name="Id" Nullable="false" />
            <Property Type="String" Name="Name" Nullable="true" MaxLength="50" />
        </ComplexType>
        <ComplexType Name="GetCustomerProductDetails_Result2">
            <Property Type="Int32" Name="ProductID" Nullable="false" />
            <Property Type="String" Name="ProductName" Nullable="true" MaxLength="50" />
        </ComplexType>
    </Schema>
</edmx:ConceptualModels>

Check the difference between normal and multiple result sets in the following screen for Return Type and Complex Type.

Step 6. Update your Entity Model

Add Multiple Result Mapping as per your requirements in the Function import Mapping

<!-- C-S mapping content -->
<edmx:Mappings>
    <MappingSpace="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="MyModelStoreContainer" CdmEntityContainer="CustomerManagementEntities">
            <FunctionImportMapping FunctionImportName="GetCustomerProductDetails" FunctionName="MyModel.Store.GetCustomerProductDetails">
                <ResultMapping>
                    <ComplexTypeMapping TypeName="MyModel.GetCustomerProductDetails_Result1">
                        <ScalarProperty Name="Id" ColumnName="Id" />
                        <ScalarProperty Name="Name" ColumnName="Name" />
                    </ComplexTypeMapping>
                </ResultMapping>
                <ResultMapping>
                    <ComplexTypeMapping TypeName="MyModel.GetCustomerProductDetails_Result2">
                        <ScalarProperty Name="ProductID" ColumnName="ProductID" />
                        <ScalarProperty Name="ProductName" ColumnName="ProductName" />
                    </ComplexTypeMapping>
                </ResultMapping>
            </FunctionImportMapping>
        </EntityContainerMapping>
    </Mapping>
</edmx:Mappings>

The difference between before and after Result Mapping is shown in the following screens.

Step 7. Call the procedure function

Update your Entity Model as in the following to add the new ComplexTypes you used in the code above as in

GetCustomerProductDetails_Result1
GetCustomerProductDetails_Result2

Step 8. Now we are done with the edmx changes. The last step is to call this procedure function

using (CustomerManagementEntitiesObj = new CustomerManagementEntities())
{
    var results = Obj.GetCustomerProductDetails(); // results is collection of customers
    var products = results.GetNextResult<GetCustomerProductDetails_Result2>(); // products is collection of products
}

In the source code of the web config just change the connection string before use.

Note. Use .Net Framework 4.5

That's it.

Thanks for reading….

Go to part 5