Calling Stored Procedure with Parameter and Multiple Result Set Entity Framework [Resolved]

Posted by Krishnasamy2008 under ASP.NET MVC on 11/3/2015 | Points: 10 | Views : 1789 | Status : [Member] | Replies : 2
Hi,
I have a stored procedure uspGetProductAndCategory which is accepting a parameter @SupplierID and returns the products, categories and suppliers [these tables from the Database.

My problem is when I am integrating this stored procedure with an .edmx it's generating one method in my ContextEntityClass as uspGetProductAndCategory with a return type of ObjectResult<uspGetProductAndCategory_Result>. But this complex type is about only 1st result set.

I have read many topics on "How to Call Multiple Result Set SP in EF 6" but didn't get any solution yet. After changing the function import and other elements through XML editor as describe., how I will call the function uspGetProductAndCategory_Result which is already returning ObjectResult<uspGetProductAndCategory_Result>.Refered http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram
https://msdn.microsoft.com/en-us/data/jj691402.aspx

Thanks,
Krishna.K




Responses

Posted by: Rajnilari2015 on: 11/3/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Krishna,
We are answering as per our understanding. Your existing SP is somewhat as under

CREATE PROCEDURE [dbo].[Prod_Cat_Supp]
@SupplierID VARCHAR(10)
AS
SELECT * from products where SupplierID= @SupplierID;
SELECT * from categories where SupplierID = @SupplierID;
SELECT * from suppliers where SupplierID = @SupplierID;

The Prod_Cat_Supp stored procedure returns 3 result sets - the first result set from products table,the second result set from the categories table while the third from suppliers table.In which case, the EF designer won't be able to map the return value of the stored procedure as expected. We need to manually modify the .edmx file as outlined below.

Right click on the .edmx file in the Solution Explorer and using "Open With" menu option open it in the XML editor. Locate the function import element for Prod_Cat_Supp stored procedure. It will be like this:

<FunctionImport Name="Prod_Cat_Supp" 
ReturnType="Collection(YourEntityModel.Prod_Cat_Supp_Result)">
<Parameter Name="SupplierID" Mode="In" Type="String" />
</FunctionImport>

Change the above function import definition as shown below

<FunctionImport Name="Prod_Cat_Supp">
<ReturnType EntitySet="products" Type="Collection(YourEntityModel.products)" />
<ReturnType EntitySet="categories" Type="Collection(YourEntityModel.categories)" />
<ReturnType EntitySet="suppliers" Type="Collection(YourEntityModel.suppliers)" />
<Parameter Name="SupplierID" Mode="In" Type="String" />
</FunctionImport>


Then locate function import mapping for Prod_Cat_Supp stored procedure and Modify the <ResultMapping> section:

...........................
............................
............................
<ResultMapping>
<EntityTypeMapping TypeName="YourEntityModel.products">
</EntityTypeMapping>
</ResultMapping>
<ResultMapping>
<EntityTypeMapping TypeName="YourEntityModel.categories">
</EntityTypeMapping>
</ResultMapping>
<ResultMapping>
<EntityTypeMapping TypeName="YourEntityModel.suppliers">
</EntityTypeMapping>
</ResultMapping>
...........................
............................
............................


Now, we have three <ResultMapping> sections and each uses <EntityTypeMapping> to map the return values to products,categories and suppliers entities respectively.
This completes the .edmx modifications part.Save the .edmx file.

Now delete the Prod_Cat_Supp_Result auto-generated complex type from the model.

Now, we have three <ResultMapping> sections and each uses <EntityTypeMapping> to map the return values to products,categories and suppliers entities respectively.

This completes the .edmx modifications part.

Finally,write the following C# code to invoke the SP:

MyTestEntities dbEntities = new MyTestEntities();
var res1 = dbEntities.Prod_Cat_Supp("YOUR SUPPLIER ID");
var res2=res1.GetNextResult<categories>();
var res3=res2.GetNextResult<suppliers>();


As can be figure out that, the GetNextResult() method on the first result set will help to get the second result-set and a similar will help to get the third one. Hope this helps.

--
Thanks & Regards,
RNA Team

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 11/3/2015 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
You can call the way you call a normal stored procedure in Entity Framework, read this post http://www.dotnetfunda.com/forums/show/16631/how-to-call-stored-procedure-using-dbcontext-in-aspnet-mvc or http://www.dotnetfunda.com/articles/show/1542/adonet-entity-framework-how-to-retrieve-data-using-stored-procedure-st .

The returned object would give you what you are looking for with all foreign key objects.

Alternatively, simply use ADO.NET and get the data into DataSet and then iterate through them and set into objects.

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Krishnasamy2008, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response