Practical Introduction To Entity Framework: Day 4

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

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

Add a new project day

Add new item

ADO dot NET entity data model

Step 3. Select the procedure for returning multiple result sets

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

Tables

Step 4. Open the edmx file in the XML editor

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

Open with

Click on yes

XML text editor

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.

Single result set

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.

Single result set

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

Validate

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