[EF] Store Procedure - identity

該篇筆記 - [EF] Store Procedure，只記錄

• 預存程序對應並透過 SQL Profile 觀察
• 預存程序和函式匯入實體模型
  

閱讀該篇文章 - 設計工具的 CUD 預存程序，發現有使用 idnetity 欄位和使用 Store Procedure 注意事項，就記錄一下囉

Store Procedure - InsertPerson TSQL 語法

CREATE PROCEDURE [dbo].[InsertPerson]
    @LastName nvarchar(50),
    @FirstName nvarchar(50),
    @HireDate datetime,
    @EnrollmentDate datetime,
    @Discriminator nvarchar(50)
AS
INSERT INTO dbo.Person (
    LastName,
    FirstName,
    HireDate,
    EnrollmentDate,
    Discriminator)
VALUES (
    @LastName, 
    @FirstName, 
    @HireDate, 
    @EnrollmentDate, 
    @Discriminator);

SELECT SCOPE_IDENTITY() AS NewPersonID;
GO

Person Entity 的預存程序對應設定

C# Code 執行測試

namespace EFStoreProcedure2
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new SchoolEntities())
            {
                var newInstructor = new Person
                {
                    FirstName = "Robyn",
                    LastName = "Martin",
                    HireDate = DateTime.Now,
                    Discriminator = "Instructor"
                };

                // Add the new object to the context.
                context.Person.Add(newInstructor);

                Console.WriteLine("Added {0} {1} to the context.",
                    newInstructor.FirstName, newInstructor.LastName);

                Console.WriteLine("Before SaveChanges, the PersonID is: {0}",
                    newInstructor.PersonID);

                // SaveChanges will call the InsertPerson sproc.  
                // The PersonID property will be assigned the value
                // returned by the sproc.
                context.SaveChanges();

                Console.WriteLine("After SaveChanges, the PersonID is: {0}",
                    newInstructor.PersonID);
            }
        }
    }
}

執行結果

MSDN 文章說明，使用 Store Procedure 注意事項

預存程序的對應 CUD 作業時的考量，當將 CUD 作業對應至預存程序中，適用下列考量：

1. 如果您要對應 CUD 作業的其中一個預存程序，將對應所有人都。 如果您不會對應所有三個，對應的作業將會失敗，如果執行，而且UpdateException就會擲回。
2. 您必須將預存程序的每個參數對應至實體屬性。
3. 如果伺服器就會產生主索引鍵值插入的資料列，您必須將此值對應回實體的索引鍵屬性。 在範例中，如下所示InsertPerson預存程序會傳回新建立的主索引鍵做為預存程序的結果集的一部分。 主索引鍵對應到實體索引鍵 (PersonID) 使用**<加入結果繫結>** EF 設計工具的功能。
4. 預存程序呼叫是對應與 1:1 概念模型中的實體。 例如，如果您實作在概念模型，然後對應中的繼承階層架構 CUD 預存程序父代（基本） 和子（衍生） 的實體，儲存子系變更都只會呼叫子的預存程序，就不會觸發父的預存程序呼叫。

針對第三點測試，假如預存程序對應時，沒有設定 NewPersonID 的話，不會出現任何錯誤訊息，就 identity 欄位不會帶回來而已，執行後如下圖

[EF] Store Procedure - Output 參數

閱讀 設計工具的查詢預存程序 時，才發現到之前筆記 - [EF] Store Procedure 沒有記錄到 Store Procedure Output 參數使用方式，再紀錄一下

Store Procedure - GetDepartmentName TSQL

CREATE PROCEDURE [dbo].[GetDepartmentName]
  @ID int,
  @Name nvarchar(50) OUTPUT
AS
  SELECT @Name = Name 
  FROM Department
  WHERE DepartmentID = @ID
GO

C# Code

// ObjectParameter 所在 namespace
using System.Data.Entity.Core.Objects;

namespace EFStoreProcedureOutput
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SchoolEntities context = new SchoolEntities())
            {
                ObjectParameter op = new ObjectParameter("Name", typeof(String));
                context.GetDepartmentName(1, op);
                Console.WriteLine($"ObjectParameter.Name：{op.Name}");
                Console.WriteLine($"ObjectParameter.ParameterType：{op.ParameterType}");
                Console.WriteLine($"ObjectParameter.Value：{op.Value}");
            }
        }
    }
}

• 延伸閱讀
• ObjectParameter Class
• [Entity Framework] DbContext中預存程式ObjectParameter錯誤參考 (DB First )

[EF] 關聯性

根據該篇文章 - 關聯性 的練習筆記，主要分為兩個主題

• 關聯 (Association) 和參考條件約束 (ReferentialConstraint)
• 關聯對應 (Association Mappings)

建立 關聯 (Association) 和參考條件約束 (ReferentialConstraint)

剛學習 EF 時建立關聯 (Association) 後，使用 [驗證] 都會有下列錯誤訊息，搞清楚後才知道還要建立參考條件約束 (ReferentialConstraint) 才算是完成

滑鼠右鍵 => 加入新項目 => 新增 關聯 (Association)

確認 CourseDeparment 關聯 (Association) 內容

CourseDepartment Association 屬性視窗 => 參考限制式

輸入參考限制式內容

關聯對應 (Association Mappings)

應用在 Many to Many 情況下

在 SSMS 上的 Many to Many

在 edm 上的 Many to Many

因為 CourseInstructor Table 欄位 CourseID 和 PersonID 都是 PK，所以在 edm 內就不會出現，且 Course 和 Person 會直接變成 Many to Many

點選關聯 (Association) 並選擇資料表對應，就可以觀察對應情況

[EF] Table 對應多 Entities

根據該篇文章 - 分割的資料表設計工具 的練習筆記，目的是要把 1 個 Table 拆成 2 個 Entity 來使用

MSDN 說明

使用消極式載入來載入您的物件時，您可能想要使用資料表分割的其中一個原因延遲載入的某些屬性。 您可以分隔可能包含非常大量的資料分成個別的實體，並只載入時所需的屬性。

在網路看見的介紹是二進位欄位資料，假如一起抓出來會影響效能，因此利用該方式，把二進位欄位資料拆到另外一個 Entity 去，需要顯示時才載進來使用顯示，該文章是把 Person.HireDate 拆到 HireInfo Entity 去

把 School 範例資料庫 的 Person 拉進來使用

[EF] Entity 對應多 Table

根據該篇文章 - 分割的實體設計工具 的練習筆記，目的是要把 2 個 Entity 整合為 1 個並對應到 2 個 Table 去

MSDN 說明

當多個資料表共用共同的索引鍵時，您可以將實體對應至多個資料表。 適用於將實體類型對應至兩個資料表的概念，很容易擴大為將實體類型對應至兩個以上的資料表。

該文章的範例並不在 School 資料庫內，文章內是另外建立一個 EntitySplitting DB 來放 Person 和 PersonInfo 這兩個 Table

CREATE TABLE [dbo].[Person] (
  [PersonId] INT IDENTITY (1, 1) NOT NULL,
  [FirstName] NVARCHAR (200) NULL,
  [LastName] NVARCHAR (200) NULL,
  CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonId] ASC));

CREATE TABLE [dbo].[PersonInfo] (
  [PersonId] INT NOT NULL,
  [Email] NVARCHAR (200) NULL,
  [Phone] NVARCHAR (50) NULL,
  CONSTRAINT [PK_PersonInfo] PRIMARY KEY CLUSTERED ([PersonId] ASC),
  CONSTRAINT [FK_Person_PersonInfo] FOREIGN KEY ([PersonId]) REFERENCES [dbo].[Person] ([PersonId]) ON DELETE CASCADE);

把 Person 和 PersonInfo 拉進來

剪下 PersonInfo 的 Email 和 Phone Property

在 Person 上貼上

Email 和 Phone 從 PersonInfo 移轉至 Person 上去

在 Person 上進行資料表對應，如下圖所示，對應之後才刪除 PersonInfo，千萬不要先刪除 PersonInfo，要進行對應時會沒有 PersonInfo 選項可以選

執行測試

namespace MapEntityToTables
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EntitySplittingEntities())
            {
                context.Database.Log = Console.WriteLine;

                var person = new Person
                {
                    FirstName = "John",
                    LastName = "Doe",
                    Email = "john@example.com",
                    Phone = "555-555-5555"
                };

                context.Person.Add(person);
                context.SaveChanges();

                foreach (var item in context.Person)
                {
                    Console.WriteLine(item.FirstName);
                }
            }
        }
    }
}

可以看見有兩段 Insert 語法產生

Select 資料時會自動進行 JOIN

[SQL] 限制資料筆數 2

這篇筆記算是下列兩篇筆記相關

• [SQL] 限制資料筆數
• [SQL] UNION ALL 一定會讀出全部資料？

想法也是先限制住 PSendout、PsendBack、PSendStock 這三個 Table 資料，避免過多資料被送出，才會各別對 Table 進行篩選，下述是語法重點片段

簡易架構說明：就 1 個 Master 搭配 3 個 Detail

改善前重點語法

SELECT
  ---------------
FROM psendoutheader AS r
  JOIN psendout AS p ON r.OUT_ID = p.out_id
  JOIN poitem AS m ON p.odno = m.odno
                      AND p.modelcode = m.modelcode
WHERE r.PAYDATE = '10804'
UNION ALL
SELECT
  ---------------
FROM psendoutheader AS r
  JOIN psendstock AS p ON r.OUT_ID = p.stock_no
  JOIN poitem AS m ON p.odno = m.odno
                      AND p.modelcode = m.modelcode
WHERE r.PAYDATE = '10804'
UNION ALL
SELECT
  ---------------
FROM psendoutheader AS r
  JOIN psendback AS p ON r.OUT_ID = p.back_id
WHERE r.PAYDATE = '10804'

改善後重點語法

SELECT 
  ---------------
FROM psendoutheader AS r
  JOIN
    (
      SELECT  ---------------
      FROM psendout
      UNION ALL
      SELECT  ---------------
      FROM psendstock
      UNION ALL
      SELECT  ---------------
      FROM psendback
    ) AS p ON R.Out_ID = p.Out_ID
  LEFT JOIN POItem AS M ON p.odno = m.odno
                           AND p.modelcode = m.modelcode
WHERE r.PAYDATE = '10804'

效能改善比較

	改善前	改善後
Psendout Logical Read	188,982	993

改善前後執行計畫觀察

下圖原語法，可以看見 PsendoutHeader、PSendOut、POItem 綁在一起抓資料，Psendout 拋出大量資料

改善後語法是透過 Nested Loop 一筆一筆資料進 Psnedout 搜尋資料，資料量少超級多，顯而易見效能也會跟著改善

[SQL] 日期轉換效能 (續)

整合 Trace 和效能計數器來觀察，發現 AVG. Disk Quene Length 的三個極端峰值都是相同語法

看完 TSQL 語法，發現是該篇筆記 - [SQL] 日期轉換效能 相同的問題，利用下述語法來轉換午夜時間

CAST(DATEDIFF(dd, 0, DATEADD(yy, -1, getdate())) AS DATETIME)

不同於上次，上次執行計畫只改善 1 %，老實講差異不大，但這次改了兩個 WHERE 上的日期轉換語法，差異就非常明顯

延伸閱讀
• [SQL] 當天午夜時間
• [SQL] 日期轉換效能

[SQL] TempDB 異常成長

收到 TempDB 自動成長通知信，恰巧有開 Trace 要收集超過 1 秒的 TSQL 語法，就利用 Profile 並匯入效能計數器來觀察一下，看到底哪裡出問題

[標準報表-磁碟使用量]內再確認，在 0422 左右，TempDB 自動成長 272MB 左右

Priofile 匯入效能計數器後的 [Avg. Disk Write Quene] 曲線圖，單獨截圖來記錄一下

點到 0422 峰值來顯示對應 TSQL 語法

發現使用者是同事，語法竟然是把一個 70 萬筆 Table 資料回傳，詢問過後才知道是忘記下 TOP 10 導致，不是 ERP 有異常語法，結案

延伸閱讀
• [SQL] 避免重覆的 WHERE 條件 2
• [SQL] 標準報表 - 磁碟使用量
  

[EF] TPT

該篇為 MSDN 文章 - 設計工具的 TPT 繼承 的練習筆記

Table-Per-Type（TPT） MSDN 解釋說明：一類一表 (Table-Per-Type) 繼承會在資料庫中使用個別資料表來維護繼承階層架構 (Inheritance Hierarchy) 中每一個類型的非繼承屬性和索引鍵屬性。

Table 說明：

• Course 為 OnlineCourse、OnsiteCourse  的共同資料欄位
• OnlineCourse、OnsiteCourse 有各自專屬資料欄位

[SQL] 多檔案備份

之前參與研討會時，課程內容提到備份還原時，可以透過產生多檔案放在多顆 Disk 上來進行備份還原，多顆 Disk 意味將使用到多顆 Disk IO，對於備份還原讀取和速度會有提升，拿 9G DB 來進行完整備份還原測試

TSQL 語法示意

-- 備份
BACKUP DATABASE [DBName] TO 
  DISK = 'D:\DBName.bak', 
  DISK = 'E:\DBName2.bak'
WITH 
  INIT , 
  NAME = 'BackupDemo'
GO

-- 還原
RESTORE DATABASE [DBName] FROM
  DISK = 'D:\DBName.bak' ,
  DISK = 'E:\DBName2.bak'
WITH REPLACE
GO

測試結果，時間單位為秒，讀取單位為 MB/Sec

	備份時間	備份讀取	還原時間	還原讀取
單顆	53.31	94.56	54.94	91.76
雙顆	35.92	140.35	34.09	147.89

參考資料
• [SQL Server][備份]提升備份的速度Part2(分檔備份)

[EF] TPH

該篇為 MSDN 文章 - 設計工具的 TPH 繼承 的練習筆記

Table Per Hierarchy (TPH) MSDN 解釋：使用一個資料庫資料表來維護繼承階層架構中的實體類型的所有資料，了解過後，個人解讀是，Table 沒有正規化情況下，在 Entity 進行正規化並進行 Coding

Person Table 說明，Discriminator 為 Studnet 或 Instructor 兩種類別

• Discriminator = Studnet 時，使用 EnrollmentDate，HireDate 不使用為 null
• Discriminator = Instructor 時，使用 HireDate，EnrollmentDate 不使用為 null

[EF] 使用資料表值函式

練習該 資料表值函式 (Tvf) 文章內容，內容還有教學 Video 可以看喔

課程截圖，老實講實際作並觀察產生的 TSQL 語法，才體會出這段話意義

[EF] 學校範例資料庫

在 MSDN 上找到該範例資料庫 - School 範例資料庫，先手動建立 School DB 後，再執行文章內的 TSQL Script 就完成建立，相關 Table 如下圖