Entity Framework Core 7 JSON 欄位排序、資料過濾解決方式分享

測試環境：
.NET 6
Entity Framework Core 7.0.20

簡化過後的題目是這樣的：在 Products 資料表中有 Attributes 欄位，是儲存 JSON 格式的資料，需要依照欄位過濾，並需要解析 Attributes 欄位中的 JSON 格式並排序。

範例查詢結果：

    
+--+------+-------------------------------------------+
|Id|Name  |Attributes                                 |
+--+------+-------------------------------------------+
|1 |產品一 |{"Type1":"AAA","Type2":"BBB","Type4":"EEE"}|
|2 |產品二 |{"Type1":"AAA","Type3":"CCC","Type4":"EEE"}|
|3 |產品二 |null                                       |
+--+------+-------------------------------------------+

    

需要解析 Attributes 欄位中的 JSON 格式並排序，使用 SQL 語法排序範例：

    
SELECT *
FROM Products
ORDER BY JSON_VALUE(Attributes, '$.Type1')
       , JSON_VALUE(Attributes, '$.Type2')
       , JSON_VALUE(Attributes, '$.Type3')
       , JSON_VALUE(Attributes, '$.Type4')
    

要在 C# 中使用 Entity Framework Core 7 達成資料過濾並排序，但是因為 Attributes 中的 JSON 格式並不是都一樣，需要排序的欄位可能有缺少，在 C# 中無法直接處理(除非查詢全部資料在記憶體中過濾)。並且使用 FromSql 的話有些資料又無法過濾(例如要在 FromSql 的 SQL 語法中的 IN 放入 C# 的 List<int> )， FromSqlRaw 又不安全。

    

var products = _context.Products
    .FromSql($@"
        SELECT *
        FROM Products
        ORDER BY JSON_VALUE(Attributes, '$.Type1')
               , JSON_VALUE(Attributes, '$.Type2')
               , JSON_VALUE(Attributes, '$.Type3')
               , JSON_VALUE(Attributes, '$.Type4')
        "
    )
    .ToList();
    

最後的解決方式就是建立一個 VIEW ，將所有需要排序的欄位列出：

    
CREATE OR ALTER VIEW ProductView AS
SELECT Products.*
        , JSON_VALUE(Attributes, '$.Type1') as Type1
        , JSON_VALUE(Attributes, '$.Type2') as Type2
        , JSON_VALUE(Attributes, '$.Type3') as Type3
        , JSON_VALUE(Attributes, '$.Type4') as Type4
FROM Products;
    

這樣在 C# 中要過濾和排序都很簡單了！JSON 格式就和一般的資料表的欄位一樣。如果有更好的方式歡迎留言分享～