0. 前言

前一篇我們詳細(xì)的介紹了SqlSugar的增刪改查，那些已經(jīng)滿足我們在日常工程開發(fā)中的使用了陶舞。但是還有一點點在開發(fā)中并不常用鸥鹉，但是卻非常有用的方法。接下來讓我們一起來看看還有哪些有意思的內(nèi)容豌拙。

1. 不同尋常的查詢

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

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個泛型的方法支持况芒，當(dāng)然實際上開發(fā)中能遇到5個表的聯(lián)查都很少。除非說是在做報表程序叶撒，否則就得審查一下數(shù)據(jù)表模型是否合理了绝骚。就以這四個方法為例，介紹一下多表查詢?nèi)绾问褂茫?/p>

先來兩個模型類：

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; }
}

簡單的描述一下兩個類的關(guān)系：一個雇員身份對應(yīng)一個人祠够，但一個人不一定會有一個雇員身份压汪。

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

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

第一個返回古瓤，是兩個表的連接方式止剖，例如：Left代表左連接，Inner表示內(nèi)連接落君，Right表示右連接穿香；第二個返回是兩個表之間的連接依據(jù)。這是一個固定的形式绎速，返回一個Object數(shù)組皮获，其中第一個是連接方式，第二個是通過哪個（些）字段進(jìn)行連接朝氓。

生成的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);

這個方法使用內(nèi)連接連接兩個表魔市，最后一個參數(shù)用來指定兩個表之間的連接字段主届。

生成的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` )

第三個方法在第二個方法的基礎(chǔ)上赵哲，可以指定連接方式：

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);

直接指定兩個表之間的聯(lián)系方式。

需要指出的是君丁，所有的方法都只是返回了一個可查詢對象枫夺，如果不進(jìn)行后續(xù)的投影（進(jìn)行select）則可能會提示主鍵沖突。而且绘闷，所有的方法在進(jìn)行ToXXX之前都不會立即執(zhí)行橡庞。

1.2 查詢函數(shù)

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

public class SqlFunc
{
    public static TResult AggregateAvg<TResult>(TResult thisValue);//針對這個列進(jìn)行取平均數(shù)統(tǒng)計
    public static int AggregateCount<TResult>(TResult thisValue);// 統(tǒng)計這個列數(shù)量 等價于 SQL里的 count(x)
    public static int AggregateDistinctCount<TResult>(TResult thisValue);/ 返回去重之后的數(shù)量
    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);// 數(shù)組是否包含
    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);//時間是否相同,根據(jù)DateType判斷
    public static int DateValue(DateTime date, DateType dataType);// 根據(jù)dateType, 返回具體的時間值
    public static bool EndsWith(string thisValue, string parameterValue);//字符串是否以某些值結(jié)尾
    public static bool Equals(object thisValue, object parameterValue);//是否相等
    public static DateTime GetDate();//返回當(dāng)前數(shù)據(jù)庫時間
    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);//類型轉(zhuǎn)換
    public static DateTime ToDate(object value);// 類型轉(zhuǎn)換
    public static decimal ToDecimal(object value);// 類型轉(zhuǎn)換
    public static double ToDouble(object value);// 類型轉(zhuǎn)換
    public static Guid ToGuid(object value);// 類型轉(zhuǎn)換
    public static int ToInt32(object value);// 類型轉(zhuǎn)換
    public static long ToInt64(object value);// 類型轉(zhuǎn)換
    public static string ToLower(object thisValue);// 類型轉(zhuǎn)換
    public static string ToString(object value);// 類型轉(zhuǎn)換
    public static TimeSpan ToTime(object value);// 類型轉(zhuǎn)換
    public static string ToUpper(object thisValue);// 類型轉(zhuǎn)換
    public static string Trim(object thisValue);// 去除首尾的空格
}

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

1.3 動態(tài)查詢

之前我們寫的查詢條件都是固定好的法竞，至少在編程的時候就知道最終查詢條件是什么了。但是在開發(fā)過程中强挫，有時候并不會那么早的知道最終查詢條件或者說查詢需要根據(jù)用戶輸入來調(diào)整查詢條件岔霸，那么如何實現(xiàn)呢？

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

使用SQL語句俯渤，動態(tài)拼接SQL語句呆细，然后根據(jù)SQL語句執(zhí)行返回結(jié)果
在使用Lambda表達(dá)式時，進(jìn)行動態(tài)拼接Lambda表達(dá)式
獲取IQueryable接口八匠，然后根據(jù)條件添加方法進(jìn)行查詢

這三種方法各有優(yōu)略絮爷，使用查詢接口會有一個明顯的問題就是對應(yīng)用層開放了更高的權(quán)限，使用SQL語句也是同樣的道理梨树。所以更符合邏輯的是使用動態(tài)拼接Lambda表達(dá)式略水。

當(dāng)然，SqlSugar在這三種方案之上劝萤，提供了另外兩種方案：

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

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

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

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 關(guān)系跨释。而ConditionCollections則不同，其中ConditionList表示是一個鍵值對集合厌处。鍵是WhereType類型鳖谈，ConditionModel是值。我們先說說 WhereType：

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

分別表示And阔涉，Or缆娃。怎樣理解呢？就是說瑰排，這一條鍵值對與前一個關(guān)系模型是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();

繼續(xù)看一下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);

理解起來也很容易椭住，第一個參數(shù)如何結(jié)果為False崇渗，則不執(zhí)行后續(xù)的查詢，否則就執(zhí)行。

2. 一些高級玩法

除了增刪改查宅广，SqlSugar還提供了一些別的有意思的機(jī)制葫掉，繼續(xù)我們的探索吧。

2.1 批量操作

SqlSugar提供了一種一次性記錄很多操作然后統(tǒng)一提交執(zhí)行的模式跟狱，之前的操作都是僅支持批量插入俭厚、批量修改、批量刪除驶臊。在這種模式下套腹，SqlSugar還支持了批量（插入、修改资铡、刪除）电禀。也就是說，在一個批處理中笤休，即可以插入也可以修改還可以刪除尖飞。

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

void AddQueue();

在IDeleteable、IInsertable店雅、IUpdateable政基、ISugarQueryable都有這個方法，一旦調(diào)用這個方法就表示該條指令進(jìn)行緩存不立即執(zhí)行闹啦，直到調(diào)用SqlSugarClient.SaveQueues()沮明。通過調(diào)用SaveQueues()保存到數(shù)據(jù)庫中。

值得注意的是：

SqlSugar 雖然支持將查詢也加入到批量操作的支持中窍奋，但是這部分在我看來更像是為了保證接口一致化而作的荐健。個人并不推薦在批處理中加入查詢，因為查詢更多的需要及時準(zhǔn)確快速琳袄，如果一旦陷入批處理中江场，查詢就無法準(zhǔn)確快速的返回數(shù)據(jù)了。

這樣對于設(shè)定批處理的初衷窖逗，反而是違背的址否。當(dāng)然最重要的一點，實際開發(fā)中這種情況很少遇到碎紊。

2.2 事務(wù)

SQL本身支持事務(wù)佑附，大多數(shù)ORM都支持事務(wù)，SqlSugar也不例外仗考。SqlSugar通過哪些方法來自己實現(xiàn)一個事務(wù)呢音同？

在SqlSugarClient中執(zhí)行：

public void BeginTran();

會將SqlSugarClient做一個事務(wù)標(biāo)記，表示之后的操作都是在事務(wù)中痴鳄，直到事務(wù)提交或者回滾瘟斜。

在SimpleClient中執(zhí)行：

public ITenant AsTenant();

返回一個ITenant實例，然后通過這個實例提交事務(wù)或者回滾事務(wù)痪寻。

注意螺句，SqlSugar所有的事務(wù)都是針對 SqlSugarClient級別的，也就是說一個事務(wù)橡类，一個SqlSugarClient蛇尚。

2.3 原生SQL執(zhí)行

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

比如說通過如下這種方式顾画，可以使用Sql語句進(jìn)行查詢：

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

通過以下這種方式取劫，執(zhí)行SQL：

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

然后，通過以下方式執(zhí)行存儲過程：

context.Client.Ado.UseStoredProcedure()

3. 總結(jié)

優(yōu)秀的ORM總是有各種各樣的方案研侣，也有各種各樣的優(yōu)點谱邪。SqlSugar到目前為止，可以告一段落了庶诡。當(dāng)然惦银，我還是剩下了一部分，留給大伙自己去探索挖掘末誓。接下來扯俱，我將以Dapper作為《C# 數(shù)據(jù)操作系列》的最后內(nèi)容。之后將會以項目的形式喇澡，帶領(lǐng)大家去了解并學(xué)習(xí)asp.net core迅栅。

更多內(nèi)容煩請關(guān)注我的博客《高先生小屋》

file

C# 數(shù)據(jù)操作系列 - 16 SqlSugar 完結(jié)篇