Some days I love Entity Framework, other days it leaves me pulling my hair out. Earlier this week, it was the latter. I ran into an issue when I had to modify a stored procedure that was part of an Entity Framework model. When I updated the model after modifying the stored procedure, Entity Framework didn’t generate a complex type to use as the return type like it usually had. If you aren’t familiar with complex types within Entity Framework, they are just custom generated classes used to hold the results returned from a stored procedure. Of course, if your query just returns the columns of a single table you can just use a type generated off that table. In my case, the stored procedure was returning a mash-up of columns from a several tables.
Adding a Stored Procedure to Entity Framework Doesn’t Generate a Complex Type?
As mentioned above, I had to modify a stored procedure to add several new columns. Doing this also meant adding 5 new joins to an already complex query. By the time I was done tuning the query, I had broken up a monster SQL statement into several smaller queries. Some of which were selecting into temp tables to achieve the performance needed for the real-time dashboard I was working on.
The frustration began when I went to update the Entity Framework model from the database and found that it wasn’t generating the complex type for me to use as the return type in the function import dialog. Clicking “Get Column Information” didn’t help either as it just returned the message “The Selected Store Procedure or Function Returns No Columns”. Lies! Running the stored procedure returns a bunch of rows of data with a dozen or so columns. Looking in the model browser in Visual Studio, I could see the stored procedure there and the function import, but no complex types.
How did I resolve this issue and get it to work? I temporarily changed the stored procedure back to a single query returning all the necessary columns to generate the model. I then reverted it back to the modified version with the multiple queries. Before attempting this, I had checked stackoverflow for solutions and tried everything that I could find (like setting FMTONLY ON) but none of them worked for me.
Reproducing the Problem
To illustrate the problem, I created two small tables and wrote a stored procedure named “SelectAllProducts” with multiple select statements like below.
I then right clicked on the .edmx file in the model browser and chose “Update model from database” and selected the stored procedure.
After completing the wizard, you can see in the model browser that my stored procedure is there with a function import, but no complex type.
Double clicking the function import for my stored procedure opens the “Edit Function Import” dialogue. Notice that complex is grayed out with no options to select. Also, clicking the “Get Column Information” button returns, “The Selected Store Procedure or Function Returns No Columns”.
The Solution
As mentioned above, the solution that I found that worked was to temporarily modify the query to be just a single select statement returning all the necessary columns. Below is how the modified stored procedure would look for the simple example provided above.
After modifying the query, I deleted the stored procedure and function import from my model and went through the “update model from database” process again. This time you can see in the model browser that the complex type was generated.
When I open the “Edit Function Import” dialogue again by double clicking on the function import in the model browser, you can see that the complex type is available to set as the return type for the newly added stored procedure.
At this point, all that needs done is to revert the stored procedure back to the original version.