What to do if Entity Framework Function Import Does Not Detect Any Columns Being Returned by your Stored Procedure
If you work with Entity Framework (EF) and a database full of stored procedures then chances are that at some point you have encountered this problem. I recently tried to map a function import to a stored procedure that existed in our database. The problem was that when I clicked "Get Column Information" in order to create a new complex type based off of the results of the stored procedure, EF said there were no columns being returned by the stored procedure. If I ran the stored procedure in SQL Server Management Studio I would see the data being returned. I did not know what the problem was at the time.
Another issue I have encountered is when trying to map a function import and have it return a scalar value. The stored procedure was supposed to return a single column and a single row containing an integer, but instead I was getting this error:
"The data reader returned by the store data provider does not have enough columns for the query requested."
Clicking "Get Column Information" on this function import yielded the same result as before, stating there were no columns being returned. I told our Database Administrator (DBA) my issue and he said he restructured the stored procedure. After that everything started working. I remained ignorant to what he did to fix it, until today. Many times when a stored procedure is created to perform work it simply returns some results after the work is completed. Entity Framework cannot detect the data being returned from the stored procedure without a SELECT query involved. Our DBA was simply adding SELECT statements to the procedures in order to more officially return some data.
After adding the SELECT statements EF is able to parse the stored procedure and detect the columns being returned. It was also able to populate my scalar value that I was attempting to return in my other function import. So if you ever encounter any of these issues, ensure that the stored procedure is performing an actual SELECT and not just returning data.