I had a Stored Procedure created in Azure Sql DB and wanted to use it in Entity Framework. Stored Procedures was returning a data set in Sql Server Management Studio but when updated my edmx file and tried to call it from C# code, it was returning a int instead of the result set I was expecting. I found number of users were facing this issue and most of the solutions on net were not working. After spending some 30 minutes I found the solution. In this post I will try to explain the solution steps by step.
Step 1: We need to add SET FMTONLY OFF; as first line of our stored procedure. It will help Entity Framework to find our the list of column Stored Procedure returns.
Step 2: Move to your .edmx and At Model Browser Window/Function Imports find your procedure then double click it. A window will open as shown below
Step 3: Change the return type to complex type and then click on generate complete type button. It will generate a class which will represent the return type of stored procedure.
Now we will be able to use the stored produce and it will return a a list of POCO class which was generated in step 3.