︿
Top

2020年1月5日 星期日

[C#] How to query with table-valued parameters in entity framework?


0. 前言


目前的專案, 遇到需要傳多個值, 查出對應資料的需求, 例如: 傳入多個組織單位代號, 去取出對應的組織單位名稱; 或者傳入多個產品類別, 去取出對應的產品資料.

經上網查詢, 至少有以下4種解決方式.

  • 方式一: 採用 LINQ 的 Contains 方法
  • 方式二: 自行撰寫 WHERE IN 的 SQL 敘述
  • 方式三: 採用 Stored Procedure, 自行傳入 Table-Valued Parameter
  • 方式四: 採用 Stored Procedure, 利用 EntityFrameworkExtras.EF6, 傳入 Table-Valued Parameter

以下茲以 傳入多個產品類別, 去取出對應的產品資料 為例, 進行說明.
本篇文章的編排比較類似個人的筆記, 說明會放在程式碼裡, 或圖片即能理解, 就不多作說明.

相關程式, 可 由此下載




1. 資料庫環境設置


1.1 資料庫與 Table 建立 


USE MyCookies1;
GO
-- ===========================
-- 移除 Table
-- ===========================
DROP TABLE IF EXISTS [dbo].[Products];
GO
DROP TABLE IF EXISTS [dbo].[Categories]
GO
-- ===========================
-- 建立 Table
-- ===========================
CREATE TABLE [dbo].[Categories]
( [Id]              INT                         NOT NULL
, [Name]            NVARCHAR(30)                NOT NULL
CONSTRAINT PK_Catgories PRIMARY KEY ( [Id] )
);
GO
CREATE TABLE [dbo].[Products]
( [Id]              INT                         NOT NULL
, [Name]            NVARCHAR(30)                NOT NULL
, [CategoryId]      INT                         NOT NULL
, [Price]           INT                         NOT NULL
  CONSTRAINT PK_Products PRIMARY KEY ( [Id] )
, CONSTRAINT FK_Categories FOREIGN KEY ( [CategoryId] )
                           REFERENCES [dbo].[Categories] ( [Id] )
);
GO
-- ===========================
-- 新增測試資料
-- ===========================
INSERT INTO [dbo].[Categories]
VALUES ( 1, N'餅乾類' )
,      ( 2, N'蛋糕類' )
,      ( 3, N'奶酪類' )
;
GO
INSERT INTO [dbo].[Products]
VALUES ( 1, N'夏威夷豆塔', 1, 50 )
,      ( 2, N'堅果塔', 1, 55 )
,      ( 3, N'養生堅果', 1, 60 )
,      ( 4, N'乳酪塔', 1, 60 )
,      ( 5, N'紅豆塔', 1, 60 )
,      ( 6, N'草莓塔', 1, 65 )
,      ( 7, N'輕乳酪蛋糕(片)', 2, 65 )
,      ( 8, N'重乳酪蛋糕(片)', 2, 70 )
,      ( 9, N'抹茶奶酪', 3, 90 )
,      ( 10, N'草莓奶酪', 3, 85 )
,      ( 11, N'芒果奶酪', 3, 85 )
,      ( 12, N'紅豆奶酪', 3, 80 )
;
GO

CREATE OR ALTER VIEW [dbo].[ViewProducts]
AS
SELECT      ProductId       = P.Id
    ,       ProductName     = P.Name
    ,       CategoryId      = P.CategoryId
    ,       Price           = P.Price
    ,       CategoryName    = C.Name
FROM        dbo.Products    P
LEFT JOIN   dbo.Categories  C   ON ( P.CategoryId = C.Id )
;
GO

1.2 Stored Procedure 的建立

含使用者自訂型態 udt_CategoryId, 及 Stored Procedure: dbo.usp_GetProductsByCategories. 

                
USE MyCookies1;
GO

CREATE TYPE [dbo].[udt_CategoryId] AS TABLE (
    [CategoryId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([CategoryId] ASC));
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER PROCEDURE dbo.usp_GetProductsByCategories
    @tbl_Categories udt_CategoryId READONLY
,   @pi_ProductName NVARCHAR(30)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT  A.ProductId
    ,       A.ProductName
    ,       A.CategoryId
    ,       A.Price
    ,       A.CategoryName
    FROM    ViewProducts        A
    JOIN    @tbl_Categories     B  ON (A.CategoryId = B.CategoryId)
    WHERE   A.ProductName LIKE  '%' + @pi_ProductName + '%'
    ;

END

-- -- ============================
-- -- 以 TSQL 呼叫
-- -- ============================
-- 
-- DECLARE @tbl_Categories  udt_CategoryId;
-- DECLARE @ls_ProductName NVARCHAR(30) = N'紅豆';
-- 
-- INSERT INTO @tbl_Categories(CategoryId)
-- VALUES   (1)
--  ,   (2)
--  ,   (3)
--  ;
-- 
-- EXEC usp_GetProductsByCategories @tbl_Categories, @ls_ProductName
-- ;
-- 

-- OUTPUT ==========>
-- ProductId    ProductName CategoryId  Price   CategoryName
-- 5    紅豆塔 1   60  餅乾類
-- 12   紅豆奶酪    3   80  奶酪類

2. 方式一: 採用 LINQ 的 Contains 方法


2.1 範例程式:

說明: 採用傳入 產品字串 List 的方式; 利用 LINQ Contains. 


/// <summary>
/// 傳入多個商品類別查詢產品, 以 Linq Contains 語句進行處理
/// </summary>
/// <param name="categories"></param>
/// <returns></returns>
public List<ViewProduct> GetProductsWithLinqContains(List<int> categories)
{
    var products = new List<ViewProduct>();
    _db.Database.Log = Console.Write;       // Entity Framework 產生的 SQL 指令, 由 Console.Write 輸出

    products = _db.ViewProducts.Where(x => categories.Contains(x.CategoryId)).ToList();

    return products;
}

由 Entity Framework 產出的 SQL 指令, 如下; 其實, 是產出 WHERE IN 的敘述. 


於 2020/1/5 下午 03:04:34 +08:00
 開啟連接SELECT 
    [Extent1].[ProductId] AS [ProductId], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent1].[CategoryId] AS [CategoryId], 
    [Extent1].[Price] AS [Price], 
    [Extent1].[CategoryName] AS [CategoryName]
    FROM (SELECT 
    [ViewProducts].[ProductId] AS [ProductId], 
    [ViewProducts].[ProductName] AS [ProductName], 
    [ViewProducts].[CategoryId] AS [CategoryId], 
    [ViewProducts].[Price] AS [Price], 
    [ViewProducts].[CategoryName] AS [CategoryName]
    FROM [dbo].[ViewProducts] AS [ViewProducts]) AS [Extent1]
    WHERE [Extent1].[CategoryId] IN (2, 3)
-- 於 2020/1/5 下午 03:04:35 +08:00
 執行-- 於 50 毫秒後完成，結果: SqlDataReader

於 2020/1/5 下午 03:04:35 +08:00
 關閉連接

2.2 測試方法:

說明: 撰寫測試程式, 利用 ExpectedObjects , 進行實際值與預期值的比對. 


[TestMethod()]
[TestCategory("GetProducts")]
public void GetProductsWithLinqContainsTest()
{
    // Arrange
    var expected = new List<ViewProduct>
    {   new ViewProduct() { ProductId = 7,  ProductName = "輕乳酪蛋糕(片)", CategoryId = 2, Price = 65, CategoryName = "蛋糕類"}
    ,   new ViewProduct() { ProductId = 8,  ProductName = "重乳酪蛋糕(片)", CategoryId = 2, Price = 70, CategoryName = "蛋糕類"}
    ,   new ViewProduct() { ProductId = 9,  ProductName = "抹茶奶酪", CategoryId = 3, Price = 90, CategoryName = "奶酪類"}
    ,   new ViewProduct() { ProductId = 10, ProductName = "草莓奶酪", CategoryId = 3, Price = 85, CategoryName = "奶酪類"}
    ,   new ViewProduct() { ProductId = 11, ProductName = "芒果奶酪", CategoryId = 3, Price = 85, CategoryName = "奶酪類"}
    ,   new ViewProduct() { ProductId = 12, ProductName = "紅豆奶酪", CategoryId = 3, Price = 80, CategoryName = "奶酪類"}
    }.ToExpectedObject();

    var categories = new List<int>() { 2, 3 };

    // Act
    var obj = new CookiesService();
    var actual = obj.GetProductsWithLinqContains(categories);

    // Assert
    expected.ShouldEqual(actual);
}


3. 方式二: 自行撰寫 WHERE IN 的 SQL 敘述


如果不滿意 Entity Framework 產出的 SQL 指令, 想自己寫, 也是可以; 只是沒有辦法作到強型別的檢查. 且要自己小心, 別打錯字.

3.1 範例程式:

說明: 採用傳入 產品字串 List 的方式; 利用 SQL IN. 


/// <summary>
/// 傳入多個商品類別查詢產品, 以 T-SQL IN 語句進行處理
/// </summary>
/// <param name="categories"></param>
/// <returns></returns>
public List<ViewProduct> GetProductsWithSqlIn(List<int> categories)
{

    var products = new List<ViewProduct>();
    _db.Database.Log = Console.Write;       // Entity Framework 產生的 SQL 指令, 由 Console.Write 輸出

    // -------------------
    // STEP 1: 原始的 sql 字串
    // -------------------
    string sql = @"
SELECT      A.ProductId
    ,       A.ProductName
    ,       A.CategoryId
    ,       A.Price
    ,       A.CategoryName
FROM        ViewProducts    A 
WHERE       A.CategoryId    IN  "
;

    // -------------------
    // STEP 2: 建立 IN 的參數
    // -------------------
    StringBuilder sb = new StringBuilder();
    List<SqlParameter> parameters = new List<SqlParameter>();
    int i = 1;
    foreach (var category in categories)
    {
        // IN clause
        sb.Append("@category_" + i + ",");
        // parameters
        parameters.Add(new SqlParameter("@category_" + i, SqlDbType.Int) { Value = category });
        i++;
    }

    //去除最後的 "," ; 並加上 左/右 括號 及分號
    string strIn = sb.ToString();
    strIn = "(" + strIn.Substring(0, strIn.Length - 1) + ");";

    // -------------------
    // STEP 3: 串成新的 sql
    // -------------------
    sql = sql + strIn;

    products = _db.Database.SqlQuery<ViewProduct>(sql, parameters.ToArray()).ToList();

    return products;
}

3.2 測試方法:

說明: 撰寫測試程式, 利用 ExpectedObjects , 進行實際值與預期值的比對. 


[TestMethod()]
[TestCategory("GetProducts")]
public void GetProductsWithLinqContainsTest()
{
    // Arrange
    var expected = new List<ViewProduct>
    {   new ViewProduct() { ProductId = 7,  ProductName = "輕乳酪蛋糕(片)", CategoryId = 2, Price = 65, CategoryName = "蛋糕類"}
    ,   new ViewProduct() { ProductId = 8,  ProductName = "重乳酪蛋糕(片)", CategoryId = 2, Price = 70, CategoryName = "蛋糕類"}
    ,   new ViewProduct() { ProductId = 9,  ProductName = "抹茶奶酪", CategoryId = 3, Price = 90, CategoryName = "奶酪類"}
    ,   new ViewProduct() { ProductId = 10, ProductName = "草莓奶酪", CategoryId = 3, Price = 85, CategoryName = "奶酪類"}
    ,   new ViewProduct() { ProductId = 11, ProductName = "芒果奶酪", CategoryId = 3, Price = 85, CategoryName = "奶酪類"}
    ,   new ViewProduct() { ProductId = 12, ProductName = "紅豆奶酪", CategoryId = 3, Price = 80, CategoryName = "奶酪類"}
    }.ToExpectedObject();

    var categories = new List<int>() { 2, 3 };

    // Act
    var obj = new CookiesService();
    var actual = obj.GetProductsWithSqlIn(categories);

    // Assert
    expected.ShouldEqual(actual);
}


4. 方式三: 採用 Stored Procedure, 自行傳入 Table-Valued Parameter


如果 SELECT 的邏輯, 不是很單純用一個 SQL 指令, 就可產生結果; 此時可考慮採用 Stored Procedure, 同時建立使用者資料型態, 傳入多值查詢條件. 如本文 1.2 Stored Procedure 的建立 的說明.

4.1 範例程式:

說明: 採用傳入 產品字串 List 的方式; 利用 Stored Procedure + User Defined DataType.
該 Stored Procedure, 共有 2 個傳入參數 @tbl_Categories, @pi_ProductName.
前者的 SqlDbType 必須定義為 SqlDbType.Structured, 且傳入自行定義的 data table, 作為對應的實際參數值. 


/// <summary>
/// 傳入多個商品類別查詢產品, 以 Entity Framework 的 SqlQuery<T> 呼叫 stored procedure 進行處理
/// </summary>
/// <param name="categories"></param>
/// <param name="productName"></param>
/// <returns></returns>
public List<ViewProduct> GetProductsWithSpByEf(List<int> categories, string productName = "")
{
    var result = new List<ViewProduct>();
    _db.Database.Log = Console.Write;       // Entity Framework 產生的 SQL 指令, 由 Console.Write 輸出

    //建立查詢條件的 DataTable (只包含 1 個 Column)
    DataTable dt = new DataTable("MyTable");
    dt.Columns.Add(new DataColumn("CategoryId", typeof(int)));
    DataRow row = null;
    foreach (var category in categories)
    {
        row = dt.NewRow();
        row["CategoryId"] = category;
        dt.Rows.Add(row);
    }

    //建立查詢參數
    var para1 = new SqlParameter("@tbl_Categories", SqlDbType.Structured) { Value = dt };
    var para2 = new SqlParameter("@pi_ProductName", SqlDbType.NVarChar, 30) { Value = productName };

    //設定 SqlDbType.Structured 那個參數對應的 User Defined Type
    para1.TypeName = "dbo.udt_CategoryId";
    result = _db.Database.SqlQuery<ViewProduct>("exec usp_GetProductsByCategories @tbl_Categories, @pi_ProductName", para1, para2).ToList();

    return result;
}

4.2 測試方法:

說明: 撰寫測試程式, 利用 ExpectedObjects , 進行實際值與預期值的比對. 


[TestMethod()]
[TestCategory("GetProducts")]
public void GetProductsWithSpByEfTest()
{
    // Arrange
    var expected = new List<ViewProduct>
    {   new ViewProduct() { ProductId = 5,  ProductName = "紅豆塔", CategoryId = 1, Price = 60, CategoryName = "餅乾類"}
    ,   new ViewProduct() { ProductId = 12, ProductName = "紅豆奶酪", CategoryId = 3, Price = 80, CategoryName = "奶酪類"}
    }.ToExpectedObject();

    var categories = new List<int>() { 1, 2, 3 };
    var productName = "紅豆";

    // Act
    var obj = new CookiesService();
    var actual = obj.GetProductsWithSpByEf(categories, productName);

    // Assert
    expected.ShouldEqual(actual);
}


5. 方式四: 採用 Stored Procedure, 利用 EntityFrameworkExtras.EF6, 傳入 Table-Valued Parameter


如果覺得前述自行建立 data table 很不方便, 則可以採用 EntityFrameworkExtras.EF6 這個套件; 它透過 Attribute 的設置, 建立呼叫 Stored Procedure 時所需的對應.

5.1 範例程式:

說明: 採用傳入 產品字串 List 的方式; SQL 端採 Stored Procedure + User Defined DataType; C# 端, 採用 EntityFrameworkExtras.EF6 這個套件.

共有 5 個步驟, 細節可以參考程式碼的註解.
前 2 個步驗, 需要另外單獨建立對應的 2 個 class; 後 3 個步驟, 則是進行呼叫.

  • STEP 1 : Define a stored procedure class
  • STEP 2 : Define the user defined data type
  • STEP 3 : 將傳入的查詢條件, 轉為 Extras.EF6 定義的 user defined data type 格式
  • STEP 4 : 建立 Extras.EF6 的 proc instance
  • STEP 5 : 透過 Extras.EF6 呼叫 stored procedure


/// <summary>
/// Extras : STEP 1 : Define a stored procedure class
/// </summary>
/// <remarks>
/// https://github.com/Fodsuk/EntityFrameworkExtras
/// </remarks>
[StoredProcedure("usp_GetProductsByCategories")]
public class GetProductsByCategories
{
    /// <summary>
    /// 多筆的產品類別代碼
    ///  </summary>
    /// <remarks>
    /// 必須定義 ParameterName, 以供 Extras.EF6 在呼叫時作對應
    /// </remarks>
    [StoredProcedureParameter(SqlDbType.Udt, ParameterName = "tbl_Categories")]
    public List<udt_CategoryId> Categories { get; set; }

    /// <summary>
    /// 產品名稱
    /// </summary>
    /// <remarks>
    /// 必須定義 ParameterName, 以供 Extras.EF6 在呼叫時作對應
    /// </remarks>
    [StoredProcedureParameter(SqlDbType.NVarChar, ParameterName = "pi_ProductName")]
    public string ProductName { get; set; }
}

/// <summary>
/// Extras : STEP 2 : Define the user defined data type
/// </summary>
/// <remarks>
/// 必須指定該 Class, 是對應至那一個 User Defined Data Type;
/// 如果有多個, 就要建立多個不同的 Class
/// </remarks>
[UserDefinedTableType("udt_CategoryId")]
public class udt_CategoryId
{
    [UserDefinedTableTypeColumn(1)]
    public int CategoryId { get; set; }
}
                

/// <summary>
/// 傳入多個商品類別查詢產品, 透過 EntityFrameworkExtra.EF6, 呼叫 stored procedure 進行處理
/// </summary>
/// <param name="categories"></param>
/// <param name="productName"></param>
/// <returns></returns>
public List<ViewProduct> GetProductsWithSpByEfExtras(List<int> categories, string productName = "")
{
    var result = new List<ViewProduct>();
    _db.Database.Log = Console.Write;       // Entity Framework 產生的 SQL 指令, 由 Console.Write 輸出

    // Extras : STEP 3 : 將傳入的查詢條件, 轉為 Extras.EF6 定義的 user defined data type 格式
    var codes = categories.Select(x => new udt_CategoryId() { CategoryId = x }).ToList();

    // Extras : STEP 4 : 建立 Extras.EF6 的 proc instance
    var proc = new GetProductsByCategories()
    {
        Categories = codes,
        ProductName = productName
    };

    // Extras : STEP 5 : 透過 Extras.EF6 呼叫 stored procedure
    result = _db.Database.ExecuteStoredProcedure<ViewProduct>(proc).ToList();
    return result;
}

5.2 測試方法:

說明: 撰寫測試程式, 利用 ExpectedObjects , 進行實際值與預期值的比對. 


[TestMethod()]
[TestCategory("GetProducts")]
public void GetProductsWithSpByEfExtrasTest()
{
    // Arrange
    var expected = new List<ViewProduct>
    {   new ViewProduct() { ProductId = 5,  ProductName = "紅豆塔", CategoryId = 1, Price = 60, CategoryName = "餅乾類"}
    ,   new ViewProduct() { ProductId = 12, ProductName = "紅豆奶酪", CategoryId = 3, Price = 80, CategoryName = "奶酪類"}
    }.ToExpectedObject();

    var categories = new List<int>() { 1, 2, 3 };
    var productName = "紅豆";

    // Act
    var obj = new CookiesService();
    var actual = obj.GetProductsWithSpByEfExtras(categories, productName);

    // Assert
    expected.ShouldEqual(actual);
}


6. 結論


在專案的進行中, 有時會有傳入多個值, 查出對應資料的需求; 本文僅就此類需求, 上網查詢解決方式, 並作整理, 撰寫範例程式; 供有需要的朋友作參考.


7. 參考文件


張貼者： 於
標籤： , , , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)