Update Entity Framework Model

Entity Framework: Stored Procedures & Complex Types

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.

Stored Procedure Original

I then right clicked on the .edmx file in the model browser and chose “Update model from database” and selected the stored procedure.

Update Wizard

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.

Model Browser No Complex Types

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”.

Edit Function Import No Complex Types

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.

Stored Procedure Modified

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.

Model Browser Complex Types

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.

Edit Function Import Complex Types

At this point, all that needs done is to revert the stored procedure back to the original version.

  • Regin

    Thanks for this post. It pointed to the right solution which needed a few additional tweaks in my case. The results table were all custom columns, so I could not write a single select query as I had to declare the columns first. The answer from this post worked like a charm and then I updated the model as suggested here.

    http://stackoverflow.com/questions/5996887/why-cant-entity-framework-see-my-stored-procedures-column-information

    Thanks again!

  • You are a life saver. 🙂 Thank You. Same story happened to me except at the end I found your article 🙂 Thank You,

  • Mark Guinness

    You were setting FMTONLY ON when you needed it OFF. EF executes SET FMTONLY ON before calling the stored proc to return column metadata (no actual data is being retrieved). When you SET FMTONLY OFF, you override that behavior.

  • BigZee

    I had to alter an SP and now I’m stuck (sort of). I’ve done all of the below (and read other articles)…added FMTONLY OFF…etc. I even changed my #temp tables to @tables…nothing worked. Strange though I have other SPs in the model which are very similar, i.e. they have #temp tables and FMTONLY OFF and they work fine. So my solution is write a simply SP to call the ‘problem’ SP which then writes the data away to a table instead of returning it. Then import that table into the model…there seems to be a bug or something in VS. ALSO I update to update3 for VS 2015, might be something there or not causing the crap.