# SP & Entity Framework 複雜型別
###### tags: `SQL`
## 新增 SP 的時候，在 EF 中沒有產生複雜型別 (就是對應的 Model 啦)
### 問題描述
為什麼沒有產生 result 的 class ?
首先開啟模型瀏覽器
#### 手動產生方法-半自助式 ( 不過通常就是你寫的SP他抓不到，基本上去產也是無濟於事 )
1. SP案右鍵編輯
2. 取得資料行後建立新的複雜類型
[參考-Entity framework doesn't generate result class](https://stackoverflow.com/questions/32225903/entity-framework-doesnt-generate-result-class)
#### 手動產生方法-全自助式 ( 不過通常就是你寫的SP他抓不到，基本上去產也是無濟於事 )
1. 複雜類型右鍵，加入新的複雜類型
2. 重新命名剛加入的類型
3. 在剛剛新增的複雜類型案右鍵，加入純量屬性，一個一個加在重新命名
[參考-微軟複雜類型-EF 設計工具](https://docs.microsoft.com/zh-tw/ef/ef6/modeling/designer/data-types/complex-types)
### 最終解法，可以自動產生 result class 也能正常抓到資料
SP 寫法
```
USE [CSFHR]
GO
/****** Object: StoredProcedure [dbo].[spGetAttendanceGrouping] Script Date: 2020/6/17 下午 05:01:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGetAttendanceGrouping]
@StartDate Datetime,
@EndDate Datetime,
@DepNo nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT ENO Into #ENOList FROM tblExaminee WHERE DepNo = @DepNo And UseStatus = 1
IF (1=0)
BEGIN
SET FMTONLY OFF
BEGIN
-- 讓 EF 抓到結構可以產生複雜型別(spGetAttendanceGrouping_Result)，但卻不會回傳一個空結果的方法
-- declaration + dummy query
-- to allow EF obtain complex data type:
DECLARE @Result TABLE(
WorkDay Date,
DeclareCount Int,
LeaveCount Int,
LagORLessCount Int,
noAttendanceCount Int,
ChangeWorkDayCount Int,
OverTimeWorkCount Int
)
SELECT * FROM @Result WHERE (1=0)
END
END
-- 塞入資料至 Result
INSERT INTO @Result (WorkDay,DeclareCount,LeaveCount,LagORLessCount,noAttendanceCount,ChangeWorkDayCount,OverTimeWorkCount)
SELECT ISNULL(CONVERT(datetime, date, 111), '') AS WorkDay,
(SELECT COUNT(*) AS Expr1
FROM dbo.tblAttendanceSheet
WHERE (WorkDay = a.date) AND (WorkDayStatus IN (N'3')) AND (ENO IN (SELECT ENO FROM #ENOList)))
AS DeclareCount,
(SELECT COUNT(*) AS Expr1
FROM dbo.tblAttendanceSheet
WHERE (WorkDay = a.date) AND (WorkDayStatus IN (SELECT LeaveCode FROM dbo.vwGetLeaveType))
AND (ENO IN (SELECT ENO FROM #ENOList)))
AS LeaveCount,
(SELECT COUNT(*) AS Expr1
FROM dbo.vwGetAttendanceSheet1
WHERE (WorkDay = a.date) AND (LagTime > 0) AND (ENO NOT IN (SELECT ENO FROM dbo.vwGetOvertime WHERE (WorkDay = a.date) AND (FormCode IN ('2', '4')) AND (ApplyStatus = 10))) OR
(WorkDay = a.date) AND (ENO NOT IN
(SELECT ENO FROM dbo.vwGetOvertime WHERE (WorkDay = a.date) AND (FormCode IN ('2', '4')) AND (ApplyStatus = 10))) AND
(LessWorkTime < 0) AND (ENO IN (SELECT ENO FROM #ENOList))) AS LagORLessCount,
(SELECT COUNT(*) AS Expr1
FROM dbo.vwGetPersonalCalendar7
WHERE (WorkDay = a.date) AND (ISWorkDay = 1) AND (hasWorkLog = 0) AND (hasLeaveLog = 0) AND
(WorkDay <= CONVERT(varchar(10), GETDATE(), 111)) AND (ENO IN (SELECT ENO FROM #ENOList))) AS noAttendanceCount,
(SELECT COUNT(*) AS Expr1
FROM dbo.vwGetExchangeWorkDay
WHERE (WorkDay = a.date) AND (ApplyStatus > 1) AND (ENO IN (SELECT ENO FROM #ENOList))) AS ChangeWorkDayCount,
(SELECT COUNT(*) AS Expr1
FROM dbo.vwGetOvertime
WHERE (FormCode IN (2, 4)) AND (WorkDay = a.date) AND (ApplyStatus = 10 )AND (ENO IN (SELECT ENO FROM #ENOList))) AS OverTimeWorkCount
FROM dbo.vwGetCalendar AS a
Where a.date between Convert(varchar(max), @StartDate, 111) and Convert(varchar(max), @EndDate, 111)
-- 回傳結果
SELECT * FROM @Result
END
--exec spGetAttendanceGrouping '2019/01/01', '2019/01/15','P301'
```
如果說 EF 可以略過 If(1=0) 跟 Where (1=0)
神奇的是不知道在 SSMS 中為什麼還可以正常的執行出資料，畢竟都 if(1=0)了？
應該沒宣告 @Result ，執行起來卻沒壞掉
以下引用 Matt 的回答:
> I'd like to add something to Sudhanshu Singh's answer: It works very well, but if you have more complex structures, combine it with a table declaration.
>
> I have used the following successfully (place it at the very beginning of your stored procecure):
```
CREATE PROCEDURE [EY].[MyStoredProc]
AS
BEGIN
SET NOCOUNT ON;
IF (1=0)
BEGIN
SET FMTONLY OFF
BEGIN
-- declaration + dummy query
-- to allow EF obtain complex data type:
DECLARE @MyStoredProcResult TABLE(
ID INT,
VendorName VARCHAR(255),
ItemName VARCHAR(255),
Type VARCHAR(2),
Sequence TINYINT
);
SELECT * FROM @MyStoredProcResult WHERE (1=0)
END
END
-- your code follows here (SELECT ... FROM ...)
-- this code must return the same columns/data types
--
-- if you require a temp table / table variable like the one above
-- anyway, add the results during processing to @MyStoredProcResult
-- and then your last statement in the SP can be
-- SELECT * FROM @MyStoredProcResult
END
```
> Note that the 1=0 guarantees that it never gets executed, but the EF deducts the structure from it.
>
> After you have saved your stored procedure, open the EDMX file in Visual Studio, refresh the data model, go to the Entity Frameworks model browser. In the model browser, locate your stored procedure, open up the "Edit Function Import" dialog, select "Returns a collection of ... Complex", then click on the button "Get Column Information".
>
> It should show up the structure as defined above. If it does, click on "Create New Complex Type", and it will create one with the name of the stored procedure, e.g. "MyStoredProc_Result" (appended by "_Result").
>
> Now you can select it in the combobox of "Returns a collection of ... Complex" on the same dialog.
>
> Whenever you need to update something, update the SP first, then you can come back to the Edit Function Import dialog and click on the "Update" button (you don't need to re-create everything from scratch).
[參考-Why can't Entity Framework see my Stored Procedure's column information? Matt 的解答](https://stackoverflow.com/questions/5996887/why-cant-entity-framework-see-my-stored-procedures-column-information)
---
## 更新從 DB 產出的複雜模型
首先開啟模型瀏覽器
1. SP案右鍵編輯
2. 案下更新，下方會看到結果
3. 儲存. Done.
Sign in with Wallet
Connect another wallet