Entiy Framework Add Function Import - Get Column Information returns nothing..

I needed to update a sql stored procedure in our entity data model. In the Model Browser I clicked "Update Model from Database" and then double clicked on the SP under functions import to open the "Edit function import" wizard so I could make the complex return type from the SP. To my surprice the import wizard told me that "The selected stored procedure returns no columns"? I knew for a fact that the SP returned several rows.

After a little Google searching I found a solution in this forum post from "Brian”: Microsoft forum

Quote: "

it seems Entity Framework will try to get the columns by executing your Stored Procedure, passing NULL for every argument. You can see this by putting a trace on your SQL Server.

So - first make sure your S_P will return something under these circumstances. Note it may have been smarter for Entity Framework to execute the Stored Proc with Default Values for arguments, as opposed to NULLS. Never mind - nothing we can do about that!

However - before trying to run the Stored Procedure, ER does this

SET FMTONLY ON

This will break your stored procedure in various circumstances, in particular, if it uses a temp table.

So, add to the start of your Stored Procedure:

SET FMTONLY OFF;

This worked for me - hope it works for you too.

brian

"

One other work around or maybe a better solution is to not use temp tables at all, if you don’t need too index those tables that is.

If you use table variables, the SET FMTONLY ON; option will not break in your stored procedure. Performance wise, except indexing option on temp tables, they seem quite the same.

Ref this article:

SQL Rocks Temp Tables vs Table Variables

LINQ and Stored Procedures

Just starting off with LINQ, I've already found a strange behavior. In my SP, I have to select some blank columns, that used to exist in the table. Now, they're only there to stop old apps from crashing:

Select '' as inst from footable

This made Linq return only a cryptic error message:
System.FormatException: String must be exactly one character long.
at System.Data.Linq.DBConvert.ChangeType(Object obj, Type type)

But when I replaced the '' in the SP with a ' ' (that is, added a blank space) - everything works. So it looks as if Linq is trying to type every column in the result, and if it's empty, it dies.

Oh, and if you have a variable named @@foo in your script - I don't know why it was called @@foo, it just was - Linq will not be able to run it! It presumes that all variables are named @foo, which probably is the correct SQL, by the way. But - the error message, again, is not so helpful: The procedure expects the parameter "foo", which was not provided.