Entity Framework Stored Procedure Returns No Columns

I was recently adding a new stored procedure to my Entity Framework EDMX (database first) when no columns were being recognized by EF. I was slightly confused by this, luckily I found this simple solution. At the top of your stored procedure definition, add SET FMTONLY OFF. I suggest only adding this setting temporarily; further explanation is below.

Adding a stored procedure is not something I do often but each time I always have to look what the setting is. I can always remember that I must do something; however, I was never good at writing it down. Well, I’m writing it down now so I won’t forget.

I’m also going to write it down three more times to ensure I remember for next. Say it with me now so you will also remember:

SET FMTONLY OFF
SET FMTONLY OFF
SET FMTONLY OFF

All right, it is hopefully burned in my brain forever.

I think it’s also time that I try and understand what this FMTONLY setting really does. Looking at the official documentation from Microsoft it states:

“Returns only metadata to the client. Can be used to test the format of the response without actually running the query.”

This explanation actually makes perfect sense as to why it would make Entity Framework find the missing columns that it could not return. It’s actually only returning the column information. Clearly EF attempts to run the stored procedure and analyze the results for the columns. Because my stored procedures always require input, no results are every returned preventing Entity Framework from reading the columns.

This implies to me – and something I did anyways – was to not leave that line of code in my stored procedure. I truly am unsure whether it matters or not, but I feel more comfortable removing it and only adding it while I need EF to detect my columns when adding a stored procedure.