Entity Framework Stored Procedure Returns No Columns 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:




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

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.


Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.