0. 前言前一篇我們詳細的介紹了SqlSugar的增刪改查，那些已經滿足我們在日常工程開發中的使用了。但是還有一點點在開發中並不常用，但是卻非常有用的方法。接下來讓我們一起來看看還有哪些有意思的內容。 1. 不同尋常的查詢之前介紹了針對單個表的查詢，同樣也是相對簡單的查詢模式。雖然開發完全夠用， ...

0. 前言

前一篇我們詳細的介紹了SqlSugar的增刪改查，那些已經滿足我們在日常工程開發中的使用了。但是還有一點點在開發中並不常用，但是卻非常有用的方法。接下來讓我們一起來看看還有哪些有意思的內容。

1. 不同尋常的查詢

之前介紹了針對單個表的查詢，同樣也是相對簡單的查詢模式。雖然開發完全夠用，但是難免會遇到一些特殊的情況。而下麵這些方法就是為瞭解決這些意料之外。

1.1 多表查詢

SqlSugar提供了一種特殊的多表查詢方案，使用IQueryable介面。來看看是怎樣操作的吧：

ISugarQueryable<T, T2> Queryable<T, T2>(Expression<Func<T, T2, object[]>> joinExpression);
ISugarQueryable<T, T2> Queryable<T, T2>(ISugarQueryable<T> joinQueryable1, ISugarQueryable<T2> joinQueryable2, Expression<Func<T, T2, bool>> joinExpression)
            where T : class, new()
            where T2 : class, new();
ISugarQueryable<T, T2> Queryable<T, T2>(ISugarQueryable<T> joinQueryable1, ISugarQueryable<T2> joinQueryable2, JoinType joinType, Expression<Func<T, T2, bool>> joinExpression)
            where T : class, new()
            where T2 : class, new();
ISugarQueryable<T, T2> Queryable<T, T2>(Expression<Func<T, T2, bool>> joinExpression) where T : class, new();

這些方法是屬於SqlSugarClient類的方法，SqlSugar提供了最多12個泛型的方法支持，當然實際上開發中能遇到5個表的聯查都很少。除非說是在做報表程式，否則就得審查一下數據表模型是否合理了。就以這四個方法為例，介紹一下多表查詢如何使用：

先來兩個模型類：

public class Person
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}
public class Employee
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int PersonId { get; set; }
    [SugarColumn(IsIgnore = true)]
    public Person Person { get; set; }
}

簡單的描述一下兩個類的關係：一個雇員身份對應一個人，但一個人不一定會有一個雇員身份。

OK，先從第一個方法說起：

var query = context.Client.Queryable<Person, Employee>((pr, em)=>new object[]
{
    JoinType.Left,
    em.PersonId == pr.Id
});

第一個返回，是兩個表的連接方式，例如：Left代表左連接，Inner表示內連接，Right表示右連接；第二個返回是兩個表之間的連接依據。這是一個固定的形式，返回一個Object數組，其中第一個是連接方式，第二個是通過哪個（些）欄位進行連接。

生成的SQL類似如下：

SELECT `pr`.`Id`,`pr`.`Name`,`pr`.`Age` FROM `Person` pr Left JOIN `Employee` em ON ( `em`.`PersonId` = `pr`.`Id` )

第二個方法：

var query = context.Client.Queryable(context.Client.Queryable<Person>(),
                context.Client.Queryable<Employee>(),
                (pr, em) => pr.Id == em.PersonId);

這個方法使用內連接連接兩個表，最後一個參數用來指定兩個表之間的連接欄位。

生成的SQL類似如下：

SELECT  `pr`.`Id` AS `Person.Id` , `pr`.`Name` AS `Person.Name` , `pr`.`Age` AS `Person.Age` , `em`.`Id` AS `Employee.Id` , `em`.`Name` AS `Employee.Name` , `em`.`PersonId` AS `Employee.PersonId` , `em`.`DeptId` AS `Employee.DeptId`  FROM  (SELECT `Id`,`Name`,`Age` FROM `Person`  ) pr  Inner JOIN  (SELECT `Id`,`Name`,`PersonId`,`DeptId` FROM `Employee`  ) em   ON ( `pr`.`Id` = `em`.`PersonId` )

第三個方法在第二個方法的基礎上，可以指定連接方式：

var query = context.Client.Queryable(context.Client.Queryable<Person>(),
                context.Client.Queryable<Employee>(),
                JoinType.Left,
                (pr, em) => pr.Id == em.PersonId);

最後一個：

var query = context.Client.Queryable<Person, Employee>((pr, em) => pr.Id == em.PersonId);

直接指定兩個表之間的聯繫方式。

需要指出的是，所有的方法都只是返回了一個可查詢對象，如果不進行後續的投影（進行select）則可能會提示主鍵衝突。而且，所有的方法在進行ToXXX之前都不會立即執行。

1.2 查詢函數

SqlSugar添加了很多我們常用的方法，使其可以映射為sql語句。我們來看一下支持哪些內容：

public class SqlFunc
{
    public static TResult AggregateAvg<TResult>(TResult thisValue);//針對這個列進行取平均數統計
    public static int AggregateCount<TResult>(TResult thisValue);// 統計這個列數量 等價於 SQL里的 count(x)
    public static int AggregateDistinctCount<TResult>(TResult thisValue);/ 返回去重之後的數量
    public static TResult AggregateMax<TResult>(TResult thisValue);//返回最大值
    public static TResult AggregateMin<TResult>(TResult thisValue);// 返回最小值
    public static TResult AggregateSum<TResult>(TResult thisValue);// 返回總和
    public static bool Between(object value, object start, object end);// 判斷列的值是否在兩個值之間
    public static int CharIndex(string findChar, string searchValue);// SQL 的charindex
    public static bool Contains(string thisValue, string parameterValue);// 是否包含
    public static bool ContainsArray<T>(T[] thisValue, object InField);// 數組是否包含
    public static bool ContainsArray<T>(List<T> thisValue, object InField);//列表蘇菲包含
    public static bool ContainsArrayUseSqlParameters<T>(List<T> thisValue, object InField);//
    public static bool ContainsArrayUseSqlParameters<T>(T[] thisValue, object InField);//
    public static DateTime DateAdd(DateTime date, int addValue, DateType dataType);// 時間添加
    public static DateTime DateAdd(DateTime date, int addValue);// 日期添加
    public static bool DateIsSame(DateTime date1, DateTime date2);// 時間是否相同
    public static bool DateIsSame(DateTime? date1, DateTime? date2);//時間是否相同
    public static bool DateIsSame(DateTime date1, DateTime date2, DateType dataType);//時間是否相同,根據DateType判斷
    public static int DateValue(DateTime date, DateType dataType);// 根據dateType, 返回具體的時間值
    public static bool EndsWith(string thisValue, string parameterValue);//字元串是否以某些值結尾
    public static bool Equals(object thisValue, object parameterValue);//是否相等
    public static DateTime GetDate();//返回當前資料庫時間
    public static string GetRandom();//
    public static TResult GetSelfAndAutoFill<TResult>(TResult value);//
    public static bool HasNumber(object thisValue);//返回是否大於0，且不能為Null
    public static bool HasValue(object thisValue);// 是否有值，且不為Null
    public static CaseThen IF(bool condition);// sql 里的if判斷
    public static TResult IIF<TResult>(bool Expression, TResult thenValue, TResult elseValue);// case when
    public static TResult IsNull<TResult>(TResult thisValue, TResult ifNullValue);// sql 里的 IsNull
    public static bool IsNullOrEmpty(object thisValue);//判斷是否是Null或者空
    public static int Length(object value);//取長度
    public static TResult MappingColumn<TResult>(TResult oldColumnName, string newColumnName);// 列名映射
    public static string MergeString(string value1, string value2);
    public static string MergeString(string value1, string value2, string value3, string value4);
    public static string MergeString(string value1, string value2, string value3, string value4, string value5);
    public static string MergeString(string value1, string value2, string value3, string value4, string value5, string value6);
    public static string MergeString(string value1, string value2, string value3);
    public static string MergeString(string value1, string value2, string value3, string value4, string value5, string value6, string value7);
    public static string Replace(object value, string oldChar, string newChar);// 替換
    public static bool StartsWith(string thisValue, string parameterValue);
    public static Subqueryable<T> Subqueryable<T>() where T : class, new();
    public static string Substring(object value, int index, int length);// 獲取子串
    public static bool ToBool(object value);//類型轉換
    public static DateTime ToDate(object value);// 類型轉換
    public static decimal ToDecimal(object value);// 類型轉換
    public static double ToDouble(object value);// 類型轉換
    public static Guid ToGuid(object value);// 類型轉換
    public static int ToInt32(object value);// 類型轉換
    public static long ToInt64(object value);// 類型轉換
    public static string ToLower(object thisValue);// 類型轉換
    public static string ToString(object value);// 類型轉換
    public static TimeSpan ToTime(object value);// 類型轉換
    public static string ToUpper(object thisValue);// 類型轉換
    public static string Trim(object thisValue);// 去除首尾的空格
}

這裡的方法大多簡單直接，我就不一一演示了。

1.3 動態查詢

之前我們寫的查詢條件都是固定好的，至少在編程的時候就知道最終查詢條件是什麼了。但是在開發過程中，有時候並不會那麼早的知道最終查詢條件或者說查詢需要根據用戶輸入來調整查詢條件，那麼如何實現呢？

常見的解決方案有以下幾種：

使用SQL語句，動態拼接SQL語句，然後根據SQL語句執行返回結果
在使用Lambda表達式時，進行動態拼接Lambda表達式
獲取IQueryable介面，然後根據條件添加方法進行查詢

這三種方法各有優略，使用查詢介面會有一個明顯的問題就是對應用層開放了更高的許可權，使用SQL語句也是同樣的道理。所以更符合邏輯的是使用動態拼接Lambda表達式。

當然，SqlSugar在這三種方案之上，提供了另外兩種方案：

正是上一篇文中提到的IConditionalModel和WhereIF。我們先來看一下IConditionalModel如何使用：

var conditions = new List<IConditionalModel>();
var query = context.Client.Queryable<Person>().Where(conditions);

可以在Where中傳入IConditionModel類型。SqlSugar提供了兩個受支持的實現類：

public class ConditionalCollections : IConditionalModel
{
    public ConditionalCollections();
    public List<KeyValuePair<WhereType, ConditionalModel>> ConditionalList { get; set; }
}
public class ConditionalModel : IConditionalModel
{
    public ConditionalModel();
    public string FieldName { get; set; }
    public string FieldValue { get; set; }
    public ConditionalType ConditionalType { get; set; }
    public Func<string, object> FieldValueConvertFunc { get; set; }
}

對於一個集合里的兄弟 ConditionModel，表示查詢條件都是 and 關係。而ConditionCollections則不同，其中ConditionList表示是一個鍵值對集合。鍵是WhereType類型，ConditionModel是值。我們先說說 WhereType：

public enum WhereType
{
    And = 0,
    Or = 1
}

分別表示And，Or。怎樣理解呢？就是說，這一條鍵值對與前一個關係模型是And還是Or。

看一下示例：

// and id=100 and (id=1 or id=2 and id=1) 
conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "100" });
conModels.Add(new ConditionalCollections() { ConditionalList=
new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()
{
    new  KeyValuePair<WhereType, ConditionalModel>
    ( WhereType.And ,
    new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }),
    new  KeyValuePair<WhereType, ConditionalModel> 
    (WhereType.Or,
    new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" }),
    new  KeyValuePair<WhereType, ConditionalModel> 
    ( WhereType.And,
    new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" })
}
});
var student = db.Queryable<Student>().Where(conModels).ToList();

繼續看一下WhereIF，WhereIF的使用就相對簡單一點：

ISugarQueryable<T> WhereIF(bool isWhere, Expression<Func<T, bool>> expression);

示例代碼：

var query = context.Client.Queryable<Person>().WhereIF(string.IsNullOrEmpty(input), p=>p.Age>10);

理解起來也很容易，第一個參數如何結果為False，則不執行後續的查詢，否則就執行。

2. 一些高級玩法

除了增刪改查，SqlSugar還提供了一些別的有意思的機制，繼續我們的探索吧。

2.1 批量操作

SqlSugar提供了一種一次性記錄很多操作然後統一提交執行的模式，之前的操作都是僅支持批量插入、批量修改、批量刪除。在這種模式下，SqlSugar還支持了批量（插入、修改、刪除）。也就是說，在一個批處理中，即可以插入也可以修改還可以刪除。

那麼我們來看如何讓這個功能為我們所用吧：

void AddQueue();

在IDeleteable、IInsertable、IUpdateable、ISugarQueryable都有這個方法，一旦調用這個方法就表示該條指令進行緩存不立即執行，直到調用SqlSugarClient.SaveQueues()。通過調用SaveQueues()保存到資料庫中。

值得註意的是：

SqlSugar 雖然支持將查詢也加入到批量操作的支持中，但是這部分在我看來更像是為了保證介面一致化而作的。個人並不推薦在批處理中加入查詢，因為查詢更多的需要及時準確快速，如果一旦陷入批處理中，查詢就無法準確快速的返回數據了。

這樣對於設定批處理的初衷，反而是違背的。當然最重要的一點，實際開發中這種情況很少遇到。

2.2 事務

SQL本身支持事務，大多數ORM都支持事務，SqlSugar也不例外。SqlSugar通過哪些方法來自己實現一個事務呢？

在SqlSugarClient中執行：

public void BeginTran();

會將SqlSugarClient做一個事務標記，表示之後的操作都是在事務中，直到事務提交或者回滾。

在SimpleClient中執行：

public ITenant AsTenant();

返回一個ITenant實例，然後通過這個實例提交事務或者回滾事務。

註意，SqlSugar所有的事務都是針對 SqlSugarClient級別的，也就是說一個事務，一個SqlSugarClient。

2.3 原生SQL執行

SqlSugar在很多地方都添加了原生Sql的支持。

比如說通過如下這種方式，可以使用Sql語句進行查詢：

var t12 = context.Client.SqlQueryable<Student>("select * from student").Where(it=>it.id>0).ToPageList(1, 2);

通過以下這種方式，執行SQL：

context.Client.Ado.ExecuteCommand(sql, parameters)

然後，通過以下方式執行存儲過程：

context.Client.Ado.UseStoredProcedure()

3. 總結

優秀的ORM總是有各種各樣的方案，也有各種各樣的優點。SqlSugar到目前為止，可以告一段落了。當然，我還是剩下了一部分，留給大伙自己去探索挖掘。接下來，我將以Dapper作為《C# 數據操作系列》的最後內容。之後將會以項目的形式，帶領大家去瞭解並學習asp.net core。

更多內容煩請關註我的博客《高先生小屋》

file

C# 數據操作系列 - 16 SqlSugar 完結篇