EF4 - The selected stored procedure returns no col

Question

I have query in a stored procedure that calls some linked servers with some dynamic SQL. I understand that EF doesn't like that, so I specifically listed all the columns that would be returned. Yet, it still doesn't like that. What am I doing wrong here? I just want EF to be able to detect the columns returned from the stored procedure so I can create the classes I need.

Please see the following code that makes up the last lines of my stored procedure:

SELECT
    #TempMain.ID,
    #TempMain.Class_Data,
    #TempMain.Web_Store_Class1,
    #TempMain.Web_Store_Class2,
    #TempMain.Web_Store_Status,
    #TempMain.Cur_1pc_Cat51_Price,
    #TempMain.Cur_1pc_Cat52_Price,
    #TempMain.Cur_1pc_Cat61_Price,
    #TempMain.Cur_1pc_Cat62_Price,
    #TempMain.Cur_1pc_Cat63_Price,
    #TempMain.Flat_Length,
    #TempMain.Flat_Width,
    #TempMain.Item_Height,
    #TempMain.Item_Weight,
    #TempMain.Um,
    #TempMain.Lead_Time_Code,
    #TempMain.Wp_Image_Nme,
    #TempMain.Wp_Mod_Dte,
    #TempMain.Catalog_Price_Chg_Dt,
    #TempMain.Description,
    #TempMain.Supersede_Ctl,
    #TempMain.Supersede_Pn,
    TempDesc.Cust_Desc,
    TempMfgr.Mfgr_Item_Nbr,
    TempMfgr.Mfgr_Name,
    TempMfgr.Vendor_ID
FROM
    #TempMain
        LEFT JOIN TempDesc ON #TempMain.ID = TempDesc.ID
        LEFT JOIN TempMfgr ON #TempMain.ID = TempMfgr.ID

Answer 1

EF doesn't support importing stored procedures which build result set from:

• Dynamic queries
• Temporary tables

The reason is that to import the procedure EF must execute it. Such operation can be dangerous because it can trigger some changes in the database. Because of that EF uses special SQL command before it executes the stored procedure:

SET FMTONLY ON

By executing this command stored procedure will return only "metadata" about columns in its result set and it will not execute its logic. But because the logic wasn't executed there is no temporary table (or built dynamic query) so metadata contains nothing.

You have two choices (except the one which requires re-writing your stored procedure to not use these features):

• Define the returned complex type manually (I guess it should work)
• Use a hack and just for adding the stored procedure put at its beginning SET FMTONLY OFF. This will allow rest of your SP's code to execute in normal way. Just make sure that your SP doesn't modify any data because these modifications will be executed during import! After successful import remove that hack.

Answer 2

Or you can create a User-Defined Table Type and return that.

CREATE TYPE T1 AS TABLE 
( ID bigint NOT NULL
  ,Field1 varchar(max) COLLATE Latin1_General_CI_AI NOT NULL
  ,Field2 bit NOT NULL
  ,Field3 varchar(500) NOT NULL
  );
GO

Then in the procedure:

DECLARE @tempTable dbo.T1

INSERT @tempTable (ID, Field1, Field2, Field3)
SELECT .....

....

SELECT * FROM @tempTable

Now EF should be able to recognize the returned columns type.

Answer 3

What I would add is:

That the import also fails if the stored procedures has parameters and returns no result set for the default parameter values.

My stored procedure had 2 float parameters and would not return anything when both parameters are 0.

So in order to add this stored procedure to the entity model, I set the value of these parameters in the stored procedure so that it is guaranteed to return some rows, no matter what the parameters actually are.

Then after adding this stored procedure to the entity model I undid the changes.

Answer 4

In my case SET FMTONLY OFF did not work. The method I followed is, I took backup of original stored procedure and replace with only column name like the below query.

Select Convert(max,'') as Id,Convert(max,'') as Name

After this change, create new function import, complex type in entity framework. Once the function import and complex type is created, replace the above query with your original stored procedure.

Answer 5

Interesting side note: Had the same problem which I first solved by using Table Variables, rather than Temp Tables (just for the import). That wasn't particularly intuitive to me, and threw me off when initially observing my two SProcs: one using Temp tables and one with Table Variables.

(SET FMTONLY OFF never worked for me, so I just changed my SProcs temporarily to get the column info, rather than bothering with the hack on the EF side just as an FYI.)

My best option was really just manually creating the complex type and mapping the function import to it. Worked great, and the only difference ended up being that an additional FactoryMethod to create the properties was included in the Designer.

Answer 6

In addition to what @tmanthley said, be sure that your stored procedure actually works by running it first in SSMS. I had imported some stored procedures and forgot about a couple dependent scalar functions, which caused EF to determine that the procedure returned no columns. Seems like a mistake I should have caught earlier on, but EF doesn't give you an error message in that case.