EF4 - Selected stored procedure does not return columns

Question

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

See the following code for 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 does not support the import of stored procedures that build a result set from:

• Dynamic queries
• Temporary tables

The reason is that in order to import the procedure, EF must execute it . Such an operation can be dangerous because it can cause some changes to the database. Because of this, EF uses a special SQL command before executing the stored procedure:

 SET FMTONLY ON 

By executing this command, the stored procedure returns only "metadata" about the columns in its result set and does not execute its logic. But since the logic was not executed, there is no temporary table (or inline dynamic query), so the metadata does not contain anything.

You have two options (except for those that require re-writing your stored procedure, so as not to use these functions):

• Define the return complex type manually (I think it should work)
• Use a hack and just to add the stored procedure that is set at the beginning of SET FMTONLY OFF . This will allow you to execute the rest of your SP code in the usual way. Just make sure your SP does not modify any data, as these changes will be made during import! After successful import, delete this hack.

Answer 2

Adding this illogical block of code solved the problem. Even if he never hits

 IF 1=0 BEGIN SET FMTONLY OFF END 

Why doesn't my typed dataset look like temporary tables?

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fe76d511-64a8-436d-9c16-6d09ecf436ea/

Answer 3

Or you can create a custom table type and return this.

 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 

EF should now recognize the type of returned columns.

Answer 4

As some others have noted, make sure that the procedure is actually executing. In particular, in my case, I successfully completed this procedure without errors in SQL Server Management Studio, completely forgetting that I was logged in as an administrator. As soon as I tried to start the procedure using my main application user, I found that the query had a table in which this user did not have access to access.

Answer 5

I would add:

That import also fails if stored procedures have parameters and do not return a result set for the default parameter values.

My stored procedure had two float parameters and returned nothing when both parameters are 0.

So, to add this stored procedure to the entity model, I set the value of these parameters in the stored procedure to ensure that it will return some rows, regardless of which parameters actually are.

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

Answer 6

The interesting side of the note: had the same problem that I first solved using Table Variables, not Temp tables (import only). It was not particularly intuitive for me and threw me away when I initially observed my two SProcs: one used Temp tables and one with Table Variables.

(SET FMTONLY OFF never worked for me, so I just temporarily changed my SProcs to get column information, instead of worrying about breaking into the EF side just like FYI.)

My best option is to simply manually create a complex type and display the import of the function into it. It worked perfectly, and the only difference was that Designer Factory added an additional FactoryMethod to create properties.

Answer 7

both solutions: 1- Define the returned complex type manually (I think it should work) 2. Use a hack and just to add the stored procedure installed at the beginning of SET FMTONLY OFF.

didn't work with me in some procedure, however he worked with another!

my procedure ends with this line:

 SELECT machineId, production [AProduction] , (select production FROM #ShiftBFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [BProduction] , (select production FROM #ShiftCFinalProd WHERE machineId = #ShiftAFinalProd.machineId) [CProduction] FROM #ShiftAFinalProd ORDER BY machineId 

thank

Answer 8

In addition to what @tmanthley said, make sure your stored procedure actually works by running it first in SSMS. I imported some stored procedures and forgot about several dependent scalar functions, which caused EF to determine that the procedure did not return the columns. It looks like an error that I should have caught earlier, but EF does not give you an error message in this case.

Answer 9

In my case, adding SET NOCOUNT ON; at the top of the procedure, fixed the problem. In any case, this is best practice.

Answer 10

In my case, SET FMTONLY OFF does not work. Using the following method, I took a backup of the original stored procedure and replaced it with only the column name similar to the query below.

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

After this change, create a new import of the function, a complex type in the entity structure. After creating the function and complex type, replace the above request with the original stored procedure.

Answer 11

 SET FMTONLY OFF 

worked for me for one of the procedures, but was not executed for another procedure. The following steps help me solve my problem.

• In the stored procedure, I created a temporary table with the same column type and inserted all the data returned by the dynamic query into the temp table. and selected the data from the temp table.

   Create table #temp ( -- columns with same types as dynamic query ) EXEC sp_executeSQL @sql insert into #temp Select * from #temp drop table #temp 

• The remote existing complex type, the import function, and the stored procedure instance for the old stored procedure and an updated entity model for the current new procedure.

• Edit the imported function in essence modal for the desired complex type, you will get all the column information that is not received for the previous stored procedure.

• Once you are done creating the type, you can delete the temporary table from the stored procedure and then update the Entity Framework.

Answer 12

In the Entity structure, when receiving information about columns, sql executes a procedure with passing zero values ​​in a parameter. Therefore, I handled the null case differently, creating a temporary table with all the necessary columns and returning all columns without value when null is passed to the procedure.

There was a dynamic request in my procedure, something like

 declare @category_id int set @category_id = (SELECT CATEGORY_ID FROM CORE_USER where USER_ID = @USER_ID) declare @tableName varchar(15) declare @sql VARCHAR(max) declare @USER_IDT varchar(100) declare @SESSION_IDT varchar(10) IF (@category_id = 3) set @tableName = 'STUD_STUDENT' else if(@category_id = 4) set @tableName = 'STUD_GUARDIAN' if isnull(@tableName,'')<>'' begin set @sql = 'SELECT [USER_ID], [FIRST_NAME], SCHOOL_NAME, SOCIETY_NAME, SCHOOL_ID, SESSION_ID, [START_DATE], [END_DATE] from @tableName .... EXECUTE (@sql) END ELSE BEGIN SELECT * from #UserPrfTemp END 

I did not get the column information in my case after using the set FMTONLY OFF trick.

This is a temporary table that I created to receive empty data. Now I get column information

 Create table #UserPrfTemp ( [USER_ID] bigint, [FIRST_NAME] nvarchar(60), SCHOOL_NAME nvarchar(60), SOCIETY_NAME nvarchar(200) ..... }