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

0. 前言

在前一篇文章中，我们详细介绍了sqlsugar的增删改查操作，这些已经足以满足日常工程开发的需求。然而，还有一些在开发中不常用但非常有用的方法。接下来，让我们一起来看看还有哪些有趣的内容。

1. 不同寻常的查询

之前我们介绍了针对单个表的查询，这些查询模式相对简单。虽然在开发中已经足够使用，但难免会遇到一些特殊情况。以下这些方法就是为了解决这些意料之外的情况。

1.1 多表查询

SqlSugar提供了一种特殊的多表查询方案，使用IQueryable接口。让我们看看如何操作：

ISugarQueryable Queryable(Expression> joinExpression);
ISugarQueryable Queryable(ISugarQueryable joinQueryable1, ISugarQueryable joinQueryable2, Expression> joinExpression) where T : class, new() where T2 : class, new();
ISugarQueryable Queryable(ISugarQueryable joinQueryable1, ISugarQueryable joinQueryable2, JoinType joinType, Expression> joinExpression) where T : class, new() where T2 : class, new();
ISugarQueryable Queryable(Expression> 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; }
}

简单描述一下两个类的关系：一个雇员身份对应一个人，但一个人不一定会有一个雇员身份。

现在，从第一个方法开始介绍：

var query = context.Client.Queryable((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(),
context.Client.Queryable(),
(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(),
context.Client.Queryable(),
JoinType.Left,
(pr, em) => pr.Id == em.PersonId);
最后一个方法：
var query = context.Client.Queryable((pr, em) => pr.Id == em.PersonId);
直接指定两个表之间的联系方式。
需要指出的是，所有的方法都只是返回了一个可查询对象，如果不进行后续的投影（进行select）则可能会提示主键冲突。而且，所有的方法在进行ToXXX之前都不会立即执行。
1.2 查询函数
SqlSugar添加了很多我们常用的方法，使其可以映射为sql语句。我们来看一下支持哪些内容：
public class SqlFunc
{
public static TResult AggregateAvg(TResult thisValue);//针对这个列进行取平均数统计
public static int AggregateCount(TResult thisValue);// 统计这个列数量 等价于 SQL里的 count(x)
public static int AggregateDistinctCount(TResult thisValue);/ 返回去重之后的数量
public static TResult AggregateMax(TResult thisValue);//返回最大值
public static TResult AggregateMin(TResult thisValue);// 返回最小值
public static TResult AggregateSum(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[] thisValue, object InField);// 数组是否包含
public static bool ContainsArray(List thisValue, object InField);//列表是否包含
public static bool ContainsArrayUseSqlParameters(List thisValue, object InField);// 
public static bool ContainsArrayUseSqlParameters(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 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(bool Expression, TResult thenValue, TResult elseValue);// case when
public static TResult IsNull(TResult thisValue, TResult ifNullValue);// sql 里的 IsNull
public static bool IsNullOrEmpty(object thisValue);//判断是否是Null或者空
public static int Length(object value);//取长度
public static TResult MappingColumn(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 Subqueryable() 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如何使用：

							

								
								

									NetShop网店系统
									
NetShop软件特点介绍： 1、使用ASP.Net(c#)2.0、多层结构开发 2、前台设计不采用任何.NET内置控件读取数据，完全标签化模板处理，加快读取速度3、安全的数据添加删除读取操作，利用存储过程模式彻底防制SQL注入式攻击4、前台架构DIV+CSS兼容IE6,IE7,FF等，有利于搜索引挚收录5、后台内置强大的功能，整合多家网店系统的功能，加以优化。6、支持三种类型的数据库：Acces
								
								下载 
							
						
var conditions = new List();
var query = context.Client.Queryable().Where(conditions);
可以在Where中传入IConditionModel类型。SqlSugar提供了两个受支持的实现类：
public class ConditionalCollections : IConditionalModel
{
public ConditionalCollections();
public List> 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 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>()
{
new  KeyValuePair
( WhereType.And ,
new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }),
new  KeyValuePair
(WhereType.Or,
new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" }),
new  KeyValuePair
( WhereType.And,
new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" })
}});
var student = db.Queryable().Where(conModels).ToList();
继续看一下WhereIF，WhereIF的使用就相对简单一点：
ISugarQueryable WhereIF(bool isWhere, Expression> expression);
示例代码：
var query = context.Client.Queryable().WhereIF(string.IsNullOrEmpty(input), p => p.Age > 10);
理解起来也很容易，第一个参数如果结果为False，则不执行后续的查询，否则就执行。
一些高级玩法
除了增删改查，SqlSugar还提供了一些别的有意思的机制，继续我们的探索吧。
2.1 批量操作
SqlSugar提供了一种一次性记录很多操作然后统一提交执行的模式，之前的操作都是仅支持批量插入、批量修改、批量删除。在这种模式下，SqlSugar还支持了批量（插入、修改、删除）。也就是说，在一个批处理中，即可以插入也可以修改还可以删除。
那么我们来看如何让这个功能为我们所用吧：
void AddQueue();
在IDeleteable、IInsertable、IUpdateable、ISugarQueryable都有这个方法，一旦调用这个方法就表示该条指令进行缓存不立即执行，直到调用SqlSugarClient.SaveQueues()。通过调用SaveQueues()保存到数据库中。
值得注意的是：
2.2 事务
SQL本身支持事务，大多数ORM都支持事务，SqlSugar也不例外。SqlSugar通过哪些方法来自己实现一个事务呢？
在SqlSugarClient中执行：
public void BeginTran();
会将SqlSugarClient做一个事务标记，表示之后的操作都是在事务中，直到事务提交或者回滚。
在SimpleClient中执行：
public ITenant AsTenant();
返回一个ITenant实例，然后通过这个实例提交事务或者回滚事务。
2.3 原生SQL执行
SqlSugar在很多地方都添加了原生Sql的支持。
比如说通过如下这种方式，可以使用Sql语句进行查询：
var t12 = context.Client.SqlQueryable("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。