C# 資料操作系列 - 16 SqlSugar 完結篇

月影西下發表於2020-05-26
C#SqlSugar

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

相關文章