Entity Framework Stored Procedure Returns No Columns

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.

Published on Feb 22, 2020

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | Entity Framework Tutorials For Beginners and Professionals