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 Set or Data set in 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 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 as in the following:

 

 

 

 

Step 3

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

Step 4

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

Step 5

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

 

<edmx:ConceptualModels>
<SchemaNamespaceSchemaNamespace="MyModel"Alias="Self"p1:UseStrongSpatialTypes="false"xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
xmlnsxmlns:p1="http://schemas.microsoft.com/ado/2009/02/edm/annotation"xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
<EntityContainerNameEntityContainerName="CustomerManagementEntities"p1:LazyLoadingEnabled="true">
<FunctionImportNameFunctionImportName="GetCustomerProductDetails">
<ReturnTypeTypeReturnTypeType="Collection(MyModel.GetCustomerProductDetails_Result1)" />
<ReturnTypeTypeReturnTypeType="Collection(MyModel.GetCustomerProductDetails_Result2)" />
</FunctionImport>
</EntityContainer>
<ComplexTypeNameComplexTypeName="GetCustomerProductDetails_Result1">
<PropertyTypePropertyType="Int32"Name="Id"Nullable="false" />
<PropertyTypePropertyType="String"Name="Name"Nullable="true"MaxLength="50" />
</ComplexType>
<ComplexTypeNameComplexTypeName="GetCustomerProductDetails_Result2">
<PropertyTypePropertyType="Int32"Name="ProductID"Nullable="false" />
<PropertyTypePropertyType="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

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

 

<!-- C-S mapping content -->
<edmx:Mappings>
<MappingSpaceMappingSpace="C-S"xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
<EntityContainerMappingStorageEntityContainerEntityContainerMappingStorageEntityContainer="MyModelStoreContainer"
CdmEntityContainer="CustomerManagementEntities">
<FunctionImportMappingFunctionImportNameFunctionImportMappingFunctionImportName="GetCustomerProductDetails"
FunctionName="MyModel.Store.GetCustomerProductDetails">
<ResultMapping>
<ComplexTypeMappingTypeNameComplexTypeMappingTypeName="MyModel.GetCustomerProductDetails_Result1">
<ScalarPropertyNameScalarPropertyName="Id"ColumnName="Id" />
<ScalarPropertyNameScalarPropertyName="Name"ColumnName="Name" />
</ComplexTypeMapping>
</ResultMapping>
<ResultMapping>
<ComplexTypeMappingTypeNameComplexTypeMappingTypeName="MyModel.GetCustomerProductDetails_Result2">
<ScalarPropertyNameScalarPropertyName="ProductID"ColumnName="ProductID" />
<ScalarPropertyNameScalarPropertyName="ProductName"ColumnName="ProductName" />
</ComplexTypeMapping>
</ResultMapping>
</FunctionImportMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>

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

Step 7

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 = newCustomerManagementEntities())
{
    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….