C-数据操作系列-16-SqlSugar-完结篇

前一篇我们详细的介绍了 SqlSugar 的增删改查，那些已经满足我们在日常工程开发中的使用了。但是还有一点点在开发中并不常用，但是却非常有用的方法。接下来让我们一起来看看还有哪些有意思的内容。

之前介绍了针对单个表的查询，同样也是相对简单的查询模式。虽然开发完全够用，但是难免会遇到一些特殊的情况。而下面这些方法就是为了解决这些意料之外。

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 之前都不会立即执行。

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);// 去除首尾的空格
}

这里的方法大多简单直接，我就不一一演示了。

之前我们写的查询条件都是固定好的，至少在编程的时候就知道最终查询条件是什么了。但是在开发过程中，有时候并不会那么早的知道最终查询条件或者说查询需要根据用户输入来调整查询条件，那么如何实现呢？

常见的解决方案有以下几种：

使用 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，则不执行后续的查询，否则就执行。

除了增删改查，SqlSugar 还提供了一些别的有意思的机制，继续我们的探索吧。

SqlSugar 提供了一种一次性记录很多操作然后统一提交执行的模式，之前的操作都是仅支持批量插入、批量修改、批量删除。在这种模式下，SqlSugar 还支持了批量（插入、修改、删除）。也就是说，在一个批处理中，即可以插入也可以修改还可以删除。

那么我们来看如何让这个功能为我们所用吧：

void AddQueue();

在 IDeleteable、IInsertable、IUpdateable、ISugarQueryable 都有这个方法，一旦调用这个方法就表示该条指令进行缓存不立即执行，直到调用 SqlSugarClient.SaveQueues()。通过调用 SaveQueues() 保存到数据库中。

值得注意的是：

SqlSugar 虽然支持将查询也加入到批量操作的支持中，但是这部分在我看来更像是为了保证接口一致化而作的。个人并不推荐在批处理中加入查询，因为查询更多的需要及时准确快速，如果一旦陷入批处理中，查询就无法准确快速的返回数据了。

这样对于设定批处理的初衷，反而是违背的。当然最重要的一点，实际开发中这种情况很少遇到。

SQL 本身支持事务，大多数 ORM 都支持事务，SqlSugar 也不例外。SqlSugar 通过哪些方法来自己实现一个事务呢？

在 SqlSugarClient 中执行：

public void BeginTran();

会将 SqlSugarClient 做一个事务标记，表示之后的操作都是在事务中，直到事务提交或者回滚。

在 SimpleClient 中执行：

public ITenant AsTenant();

返回一个 ITenant 实例，然后通过这个实例提交事务或者回滚事务。

注意，SqlSugar 所有的事务都是针对 SqlSugarClient 级别的，也就是说一个事务，一个 SqlSugarClient。

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

优秀的 ORM 总是有各种各样的方案，也有各种各样的优点。SqlSugar 到目前为止，可以告一段落了。当然，我还是剩下了一部分，留给大伙自己去探索挖掘。接下来，我将以 Dapper 作为《C# 数据操作系列》的最后内容。之后将会以项目的形式，带领大家去了解并学习 asp.net core。

更多内容烦请关注我的博客《高先生小屋》

C-数据操作系列-16-SqlSugar-完结篇

0. 前言

1. 不同寻常的查询

1.1 多表查询

1.2 查询函数

1.3 动态查询

2. 一些高级玩法

2.1 批量操作

2.2 事务

2.3 原生 SQL 执行

3. 总结