Entity Framework 4 – The selected stored procedure returns no columns (part 2)
My original post on this topic, Entity Framework 4 – The selected stored procedure returns no columns, has been getting quite a lot of hits recently and I realize that of all the solutions I gave on that page the one I use the most is the one that’s mentioned way down the page in a comment. So I thought it would be a good idea to highlight it.
The simplest solution to this issue is based on some data given by Frank Lan, a guy from MS Online Community Support on the SilverLight forums. He said:
I did some research on how Visual Studio gets the column information. If you open SQL Server profiler when you click “Get Column Information”, you will find that Visual Studio queries against database to get the column information. One query I notice is that Visual Studio tries to execute the store procedure with all parameters set to be null. I am not sure if this is the way Visual Studio determines whether there’s column information or not. But I suggest you trying to execute your query in this way to see if there’s any column returned by your store procedure. Of course you can monitor the profiler your own to find more useful information.
That suggested a simple way to handle any situation where you have applied the solutions I gave in the original article (don’t just ignore those solutions and do this easy one) or where you are forced to use temp tables because temp variables won’t do and you still can’t get the columns:
Stick an “if” statement at the top of your sproc that checks if all parameters are null and if true returns the structure.
if @param1 is null and @param2 is null then
cast(null as varchar(10)) as Column1,
cast(null as bit) as Column2,
cast(null as decimal) as Column3
Just be aware that the casts are required otherwise EF won’t be able to tell what the data types are supposed to be. Even if you return values instead of nulls you still need the casts.
Hope that helps.