entity framework - Creating entities from stored procedures which have dynamic sql

Question

I have a stored procedure which uses a couple of tables and creates a cross-tab result set. For creating the cross-tab result set I am using CASE statements which are dynamically generated on basis of records in a table.

Is it possible to generate an entity from this SP using ADO.NET Entity framework? Cuz each time I try Get Column Information for the particular SP, it says that The selected stored procedure returns no columns.

Any help would be appreciated.

Answer 1

A member of my team recently encountered something like this, where a stored procedure was generating all kinds of dynamic SQL and returning calculated columns so the data context didn't know what to make of it. I haven't tried it myself yet, but this was the solution he claimed worked:

The solution is simply to put the line “SET FMTONLY OFF;” into the proc. This allows the Data Context to actually generate the return class. This works in this case, only because the proc is doing nothing but querying data.

Full details here: http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/

You only need the “SET FMTONLY OFF” in the proc long enough to generate the class. You can then comment it out.