[EF] 使用資料表值函式

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

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

建立 GetStudentGradesForCourse Table Value Function

CREATE FUNCTION [dbo].[GetStudentGradesForCourse] 
(
    @CourseID INT
)
RETURNS TABLE
RETURN
    SELECT [EnrollmentID],
           [CourseID],
           [StudentID],
           [Grade]
    FROM   [dbo].[StudentGrade]
    WHERE  CourseID = @CourseID
GO

匯入 TVF 來使用，匯入當下會是 [複雜：GetStudentGradesForCourse_Result]，修正為 [實體：StudentGrade]

實際執行觀察 TVF 產生的 TSQL 語法

using System.Data.Entity;

namespace EFTVF
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new SchoolEntities())
            {
                context.Database.Log = Console.Write;

                var CourseID = 4022;
                var Grade = 3.5M;

                // 使用 Table Value Function
                // Return all the best students in the Microeconomics class.
                var students = context.GetStudentGradesForCourse(CourseID)
                    .Where(s => s.Grade >= Grade)
                    .Select(s => new { s.Person, s.Course.Title });

                foreach (var result in students)
                {
                    Console.WriteLine(
                        "Couse: {0}, Student: {1} {2}",
                        result.Title,
                        result.Person.FirstName,
                        result.Person.LastName);
                }
            }
        }
    }
}

一個 TSQL 語法就帶出所需資料

建立 uspStudentGradesForCourse Store Procedure 來觀察產生的 TSQL 語法

CREATE PROCEDURE [dbo].[uspStudentGradesForCourse] 
(
    @CourseID INT
)
AS
  BEGIN
    SELECT 
      [EnrollmentID],
      [CourseID],
      [StudentID],
      [Grade]
    FROM  [dbo].[StudentGrade]
    WHERE CourseID = @CourseID
  END
GO

實際執行觀察 Store Procedure 產生的 TSQL 語法

using System.Data.Entity;

namespace EFTVF
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new SchoolEntities())
            {
                context.Database.Log = Console.Write;

                var CourseID = 4022;
                var Grade = 3.5M;

                // 使用 Stroe Procedure 並使用 Eager Loading
                context.StudentGrade
                    .Include(p => p.Person)
                    .Include(c => c.Course);

                var students = context.uspStudentGradesForCourse(CourseID)
                    .Where(s => s.Grade >= Grade)
                    .Select(s => new { s.Person, s.Course.Title });

                foreach (var result in students)
                {
                    Console.WriteLine(
                        "Couse: {0}, Student: {1} {2}",
                        result.Title,
                        result.Person.FirstName,
                        result.Person.LastName);
                }
            }
        }
    }
}

即使使用 Eager Loading 仍然跑了多次 TSQL 來取出所需資料

突發想到，想說把 Lazy Loading 給關閉，結果就是直接拋出 Exception，完全無法執行

sing System.Data.Entity;

namespace EFTVF
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new SchoolEntities())
            {
                context.Database.Log = Console.Write;

                var CourseID = 4022;
                var Grade = 3.5M;

                // 把 LazyLoadingEnabled 關閉
                context.Configuration.LazyLoadingEnabled = false;

                context.StudentGrade
                    .Include(p => p.Person)
                    .Include(c => c.Course);

                var students = context.uspStudentGradesForCourse(CourseID)
                    .Where(s => s.Grade >= Grade)
                    .Select(s => new { s.Person, s.Course.Title });

                foreach (var result in students)
                {
                    Console.WriteLine(
                        "Couse: {0}, Student: {1} {2}",
                        result.Title,
                        result.Person.FirstName,
                        result.Person.LastName);
                }
            }
        }
    }
}