EF4-선택한 저장 프로 시저가 열을 반환하지 않음

EF4-선택한 저장 프로 시저가 열을 반환하지 않음

---

일부 동적 SQL로 연결된 서버를 호출하는 저장 프로 시저에 쿼리가 있습니다. EF가 그것을 좋아하지 않는다는 것을 이해하므로 반환 될 모든 열을 구체적으로 나열했습니다. 그러나 그것은 여전히 ​​그것을 좋아하지 않습니다. 내가 여기서 뭘 잘못하고 있니? 필요한 클래스를 만들 수 있도록 EF가 저장 프로 시저에서 반환 된 열을 감지 할 수 있기를 바랍니다.

내 저장 프로 시저의 마지막 줄을 구성하는 다음 코드를 참조하십시오.

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

---

EF는 다음에서 결과 집합을 빌드하는 저장 프로 시저 가져 오기를 지원하지 않습니다.

• 동적 쿼리
• 임시 테이블

그 이유는 프로 시저를 가져 오려면 EF가 실행해야하기 때문 입니다. 이러한 작업은 데이터베이스에서 일부 변경을 트리거 할 수 있으므로 위험 할 수 있습니다. 그 때문에 EF는 저장 프로 시저를 실행하기 전에 특수 SQL 명령을 사용합니다.

SET FMTONLY ON

이 명령을 실행하면 저장 프로 시저가 결과 집합의 열에 대한 "메타 데이터"만 반환하고 논리를 실행하지 않습니다. 그러나 로직이 실행되지 않았기 때문에 임시 테이블 (또는 빌드 된 동적 쿼리)이 없으므로 메타 데이터에는 아무것도 포함되지 않습니다.

두 가지 선택 사항이 있습니다 (이러한 기능을 사용하지 않도록 저장 프로 시저를 다시 작성해야하는 경우 제외).

• 반환 된 복합 유형을 수동으로 정의합니다 (작동해야 함).
• 해킹을 사용하고 처음에 저장 프로 시저를 추가하기 위해 사용합니다 SET FMTONLY OFF. 이렇게하면 나머지 SP 코드가 정상적인 방식으로 실행됩니다. 이러한 수정은 가져 오는 동안 실행되므로 SP가 데이터를 수정하지 않는지 확인하십시오! 성공적으로 가져온 후 해당 해킹을 제거하십시오.

---

이 비논리적 코드 블록을 추가하면 문제가 해결되었습니다. 히트하지 않더라도

IF 1=0 BEGIN
    SET FMTONLY OFF
END

유형이 지정된 데이터 세트가 임시 테이블을 좋아하지 않는 이유는 무엇입니까?

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

---

또는 사용자 정의 테이블 유형을 생성하고 반환 할 수 있습니다.

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

그런 다음 절차에서 :

DECLARE @tempTable dbo.T1

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

....

SELECT * FROM @tempTable

이제 EF는 반환 된 열 유형을 인식 할 수 있습니다.

---

다른 사람들이 언급했듯이 절차가 실제로 실행되는지 확인하십시오. 특히 필자의 경우 관리자 권한으로 로그인 한 것을 완전히 잊고 SQL Server Management Studio에서 오류없이 즐겁게 절차를 실행하고있었습니다. 내 응용 프로그램의 주 사용자를 사용하여 프로 시저를 실행하려고 시도하자마자 해당 사용자에게 액세스 권한이 없다는 쿼리에 테이블이 있다는 것을 알았습니다.

---

내가 추가 할 것은 :

저장 프로 시저에 매개 변수가 있고 기본 매개 변수 값에 대한 결과 집합을 반환하지 않는 경우에도 가져 오기가 실패합니다.

내 저장 프로 시저에는 2 개의 부동 매개 변수가 있으며 두 매개 변수가 모두 0이면 아무 것도 반환하지 않습니다.

따라서이 저장 프로 시저를 엔터티 모델에 추가하기 위해 매개 변수가 실제로 무엇이든 상관없이 일부 행을 반환 할 수 있도록 저장 프로 시저에서 이러한 매개 변수의 값을 설정했습니다.

그런 다음이 저장 프로 시저를 엔터티 모델에 추가 한 후 변경 사항을 취소했습니다.

---

흥미로운 참고 사항 : 임시 테이블 (가져 오기 전용) 대신 테이블 변수를 사용하여 처음 해결 한 것과 동일한 문제가있었습니다. 그것은 나에게 특히 직관적이지 않았고, 처음에 두 개의 SProc을 관찰 할 때 나를 버렸습니다. 하나는 Temp 테이블을 사용하고 다른 하나는 테이블 변수를 사용합니다.

(SET FMTONLY OFF는 저에게 효과가 없었기 때문에 FYI처럼 EF 측의 해킹에 신경을 쓰지 않고 일시적으로 열 정보를 얻기 위해 SProc을 변경했습니다.)

내 최선의 선택은 실제로 복잡한 유형을 수동으로 만들고 함수 가져 오기를 매핑하는 것입니다. 훌륭하게 작동했고 유일한 차이점은 속성을 생성하는 추가 FactoryMethod가 Designer에 포함되었다는 것입니다.

---

두 솔루션 : 1- 반환 된 복합 유형을 수동으로 정의하십시오 (작동해야한다고 생각합니다) 2- 해킹을 사용하고 시작 부분에 저장된 프로 시저를 추가하기 위해 SET FMTONLY OFF.

일부 절차에서 나와 함께 일하지 않았지만 다른 절차에서는 작동했습니다!

내 절차는 다음 줄로 끝납니다.

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

감사

---

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.

---

In my case adding SET NOCOUNT ON; at the top of the procedure fixed the problem. It's best practice anyway.

---

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.

---

SET FMTONLY OFF 

worked for me for one of the procedure but failed for other procedure. Following steps helps me to resolve my problem

1. Within a stored procedure, I have created temporary table with the same column type and inserted all the data returned by dynamic query to temp table. and selected the temp table data.

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

2. Deleted existing complex type, import function and stored procedure instance for old stored procedure and updated entity model for current new procedure.

3. Edit the imported Function in entity modal for desired complex type, you will get all the column information there which is not getting for previous stored procedure.

4. once you have done with the type creation you can delete the temporary table from stored procedure and then refresh Entity Framework.

---

In Entity framework, while getting column information the sql executes the procedure with passing null values in parameter. So I handled null case differently by creating a temp table with all the required columns and returning all the columns with no value when null is passed to the procedure.

In my procedure there was dynamic query, 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 was not getting the column information in my case after using the set FMTONLY OFF trick.

This is temp table I created to get the blank data. Now I am getting the column info

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

---

Entity Framework will try to get the columns by executing your stored procedure, passing NULL for every argument.

1. Please make sure that the stored procedure will return something under all the circumstances. Note it may have been smarter for Entity Framework to execute the stored proc with default values for the arguments, as opposed to NULLs.

2. ER does the following to get the metadata of the table:

   SET FMTONLY ON

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

4. So to get a result as complex type; please try by adding

   SET FMTONLY OFF;

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

Referred from https://social.msdn.microsoft.com/Forums/en-US/e7f598a2-6827-4b27-a09d-aefe733b48e6/entity-model-add-function-import-stored-procedure-returns-no-columns?forum=adodotnetentityframework

참고URL : https://stackoverflow.com/questions/7128747/ef4-the-selected-stored-procedure-returns-no-columns