C# Lambda 運算式產生 SQL 查詢語句

標籤：協助   方式   union   new   sse   private   實現   作者   code   

由於一些曆史原因，導致公司現有項目的資料庫中存在大量中文表名，中文欄位名，而且操作資料庫的方式還是 SQL 陳述式拼接 + ADO.NET，當然操作資料庫的方式一點問題都沒，但是最讓我不能接受的就是 SQL 陳述式的拼接，因為資料庫中大量中文表名，中文欄位名的原因，導致一開啟相關代碼，黑壓壓一片漢字，著實辣眼睛，為瞭解決這個問題，編寫了 TQueryHelper 協助類。

TQueryHelper 的主要作用是避免在拼接 SQL 陳述式中出現中文，所以我的解決思路是：中文表名，中文欄位名，可以利用特性（Attribute）來與實體類的屬性一一映射，查詢語句通過 Lambda 運算式分塊產生，然後利用 StringBuilder 進行拼接，因為是在現有項目上改進，所以 LinqToSql 和 EF 都不適合，因為 LinqToSql 和 EF 都需要串連資料庫建立相應的實體類，這不符合需求，加上網上沒有找到期望的類庫，所以打算自己造，需要說明的是，Lambda 運算式產生 SQL 參考的這篇文章《由淺入深運算式樹狀架構（二）遍曆運算式樹狀架構 騰飛（Jesse）》，在此特別感謝作者。

上面說明的是編寫 TQueryHelper 動機和過程，發本文的目的是期望自己寫的協助類能夠協助其他人，避免別人重複造輪子，另外由於本文水平有限，也期望大家能給出好的建議，讓本人加以改進，還有如果有現成類庫，本人就可以直接使用成熟的類庫，這樣也避免本人繼續走彎路。

下面介紹一下 TQueryHelper 產生 SQL 陳述式的方式，增刪改查雖然都有包括，但下面只介紹查詢方面的，首先我們定義兩個實體類：

[DataBaseT("文章表")]public class TopicModel{    public int IdentityID { get; set; }    [DataBaseT("標題")]    public string Title { get; set; }    [DataBaseT("類別")]    public string TypeCode { get; set; }}[DataBaseT("文章類別表")]public class TopicTypeModel{    public int IdentityID { get; set; }    [DataBaseT("編號")]    public string TypeCode { get; set; }    [DataBaseT("名稱")]    public string TypeName { get; set; }}

單表簡單查詢代碼如下：

string querySql = new TQueryHelper<TopicModel>().Query()                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].* from [文章表] with(nolock)

查詢前 10 條資料，並且只查詢 IdentityID 和 標題欄位代碼如下：

string querySql = new TQueryHelper<TopicModel>().Query(10)                  .Select(p => new { ID = p.IdentityID, p.Title })                  .ToSql();//產生的 SQL 陳述式如下：select top 10 [文章表].[IdentityID] as ID,[文章表].[標題] from [文章表] with(nolock)

複雜一點的單表查詢，包括 Where 語句，Order By 語句，Group By 語句的代碼如下：

string querySql = new TQueryHelper<TopicModel>().Query()                  .Select(p => new { ID = p.IdentityID, p.Title })                  .Count(p => p.IdentityID, "Count")                  .Where(p => p.IdentityID == 1)                  .OrderDesc(p => p.IdentityID)                  .Group(p => new { p.IdentityID, p.Title })                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].[IdentityID] as ID,[文章表].[標題],COUNT([文章表].[IdentityID]) as [Count] from [文章表] with(nolock) where [文章表].[IdentityID] = 1 group by [文章表].[IdentityID],[文章表].[標題] order by [文章表].[IdentityID] desc

下面來看看多表查詢，簡單雙表內聯串連查詢代碼如下：

string querySql = new TQueryHelper<TopicModel>().Query()                  .InnerJoin<TopicTypeModel>((p, y) => p.IdentityID == y.IdentityID)                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].* ,[文章類別表].* from [文章表] with(nolock) inner join [文章類別表] with(nolock) on [文章表].[IdentityID]=[文章類別表].[IdentityID]

複雜一點的雙表內聯查詢代碼如下：

string querySql = new TQueryHelper<TopicModel>()                  .Query()                  .Select(p => new { ID = p.IdentityID, p.Title })                  .Select<TopicTypeModel>(p => new { TypeID = p.IdentityID, p.TypeCode, p.TypeName })                  .InnerJoin<TopicTypeModel>((p, y) => p.TypeCode == y.TypeCode)                  .Where(p => p.TypeCode == "life" && p.IdentityID <= 20)                  .Order(p => p.IdentityID)                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].[IdentityID] as ID,[文章表].[標題],[文章類別表].[IdentityID] as TypeID,[文章類別表].[編號],[文章類別表].[名稱] from [文章表] with(nolock) inner join [文章類別表] with(nolock) on [文章表].[類別]=[文章類別表].[編號] where [文章表].[類別] = ‘life‘ and [文章表].[IdentityID] <= 20 order by [文章表].[IdentityID] asc

TQueryHelper 支援任意張表內聯串連/左串連/右串連查詢，TQueryHelper 也支援聯集查詢和子查詢，簡單聯集查詢的代碼如下：

string querySql = new TQueryHelper<TopicModel>()                  .Query()                  .Union<TopicModel>(                      new TQueryHelper<TopicModel>().Query()                  )                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].* from [文章表] with(nolock) union (select [文章表].* from [文章表] with(nolock))

複雜的聯集查詢代碼也都依然支援，通過代碼也能看到，聯集查詢之間互相無影響，所以邏輯可以相對複雜，子查詢的處理方式跟聯集查詢類似，From In 子查詢代碼如下：

string querySql = new TQueryHelper<TopicModel>()                  .Query()                  .Select()                  .FromIn<TopicModel>(                      new TQueryHelper<TopicModel>()                      .Query()                  )                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].* from (select [文章表].* from [文章表] with(nolock)) as [文章表]

目前只提供了 Where In 的支援，代碼如下：

string querySql = new TQueryHelper<TopicModel>()                  .Query()                  .Select()                  .Where(p => p.IdentityID == 1)                  .WhereIn<TopicTypeModel>(                      p => p.TypeCode,                      new TQueryHelper<TopicTypeModel>()                      .Distinct()                      .Select(p => new { TypeCode = p.TypeCode })                  )                  .ToSql();//產生的 SQL 陳述式如下：select [文章表].* from [文章表] with(nolock) where [文章表].[IdentityID] = 1 and [文章表].[類別] in (select distinct [文章類別表].[編號] from [文章類別表] with(nolock) )

至此，TQueryHelper 的主要功能都介紹完畢，TQueryHelper 支援 SQL 拼接的大部分邏輯實現，當然也支援產生參數（@）的 SQL 陳述式，這兒不一一列出，需要說明的是，雖然支援 CacheKey（就是根據 CacheKey 把查詢語句緩衝出來），但是還是無法避免運算式的參數傳遞，因為測試發現，比如下面的代碼：

private void X<T>(Expression<Func<T, object>> lambda) {}for (int index = 0; index < 10000; index ++ ){    X<TopicModel>(p => new { p.IdentityID, p.Title });}

上面的代碼，X 函數裡面什麼也沒做，但是測試之後發現竟然需要消耗大概 130 毫秒左右，這兒實在是沒想出辦法來最佳化，所以調用產生一次 SQL 之後應該根據 KEY 值及時緩衝，這樣下次調用可以直接從緩衝中拿出已拼接好的 SQL 陳述式，而不再需要通過 TQueryHelper 產生 SQL 陳述式，最後，還是希望看過本文的朋友提出寶貴的改進意見，或者給出成熟的類庫連結，本人感激不進。源碼：https://pan.baidu.com/s/1jHG3mSm 密碼：ytm5

C# Lambda 運算式產生 SQL 查詢語句