权限管理系统快速开发框架落地-SqlSugar的使用

上一篇我们介绍了如何创建一个后台项目并集成一些基础框架，工程有了，还需要最基本的一项我们即可开始业务方面的开发--打通数据库，封装数据返回给API。这里我们选择使用SqlSugar。

以下是SqlSugar官网（www.donet5.com/home/doc）的介…

*SqlSugar 是一款 老牌 .NET 开源多库架构ORM框架，（EF Core单库架构），由果糖大数据科技团队

维护和更新 ，开箱即用最易上手的.NET ORM框架 。生态圈丰富，目前开源生态仅次于EF Core，但是在需要多库兼容的项目或产品中更加偏爱SqlSugar【开源生态】

Github源码：github.com/donet5/SqlS…

Gitee源码：gitee.com/dotnetchina…*

为什么要使用SqlSugar？

1、性能优越，查询使用 reflection.emit 创建IL语言+委托绑定，然后对该对象进行 cache，datareader直接赋值给cache对象,高性能拉姆达解析。

2、大量语法糖，拉姆达表达式筛选，新颖的多表查询 ，支持分页等。

3、支持NOLOCK查询，提高性能。

4、支持事务

5、内置实体类生成函数。

上一篇我们介绍了如何在.net7项目中集成配置SqlSugar，本篇直接讲使用。直接看代码：
`public class BaseDao : SimpleClient, IBaseDao where T : class, new() { public SqlSugarScope _sqlSugar; public SqlSugarScopeProvider _sqlSugarProvider; public BaseDao(SqlSugarScope sqlSugar) { _sqlSugar = sqlSugar; _sqlSugarProvider = sqlSugar.GetConnectionScopeWithAttr(); } // private ISqlSugarClient _sqlSugar => base.Context; /public BaseDao(ISqlSugarClient context) : base(context)//注意这里要有默认值等于null { base.Context = context; }/

    public T GetById(long id)
    {
        return _sqlSugar.Queryable<T>().InSingle(id);
    }

    public Task<T> GetByIdAsync(long id)
    {
        return _sqlSugar.Queryable<T>().InSingleAsync(id);
    }

    public List<T> GetList(
        Expression<Func<T, bool>> predicate = null,
        Expression<Func<T, object>> orderByExpression = null, 
        OrderByType orderByType = OrderByType.Asc
        )
    {
        return _sqlSugar.Queryable<T>()
            .WhereIF(predicate != null, predicate)
            .OrderByIF(orderByExpression != null, orderByExpression, orderByType)
            .ToList();
    }

    public async Task<List<T>> GetListAsync(
        Expression<Func<T, bool>> predicate = null,
        Expression<Func<T, object>> orderByExpression = null,
        OrderByType orderByType = OrderByType.Asc
        )
    {
        var list = await _sqlSugar.Queryable<T>()
            .WhereIF(predicate != null, predicate)
            .OrderByIF(orderByExpression != null, orderByExpression, orderByType)
            .ToListAsync();
        return list;
    }

    public PageList<T> QueryPage(Expression<Func<T, bool>> predicate, string orderBy = "",
        int pageIndex = 1, int pageSize = 10, bool blUseNoLock = false)
    {
        int totalCount = 0;
        var page = blUseNoLock
            ? _sqlSugar.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                .WhereIF(predicate != null, predicate).With(SqlWith.NoLock)
                .ToPageList(pageIndex, pageSize, ref totalCount)
            : _sqlSugar.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                .WhereIF(predicate != null, predicate).ToPageList(pageIndex, pageSize, ref totalCount);
        var list = new PageList<T>(page, pageIndex, pageSize, totalCount);
        return list;
    }

    public async Task<PageList<T>> QueryPageAsync(Expression<Func<T, bool>> predicate, string orderBy = "",
        int pageIndex = 1, int pageSize = 10, bool blUseNoLock = false)
    {
        RefAsync<int> totalCount = 0;
        var page = blUseNoLock
            ? await _sqlSugar.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                .WhereIF(predicate != null, predicate).With(SqlWith.NoLock)
                .ToPageListAsync(pageIndex, pageSize, totalCount)
            : await _sqlSugar.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                .WhereIF(predicate != null, predicate).ToPageListAsync(pageIndex, pageSize, totalCount);
        var list = new PageList<T>(page, pageIndex, pageSize, totalCount);
        return list;
    }

    public PageList<T> QueryPage(Expression<Func<T, bool>> predicate,
        Expression<Func<T, object>> orderByExpression, OrderByType orderByType, int pageIndex = 1,
        int pageSize = 10, bool blUseNoLock = false)
    {
        int totalCount = 0;
        var page = blUseNoLock
            ? _sqlSugar.Queryable<T>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                .With(SqlWith.NoLock).ToPageList(pageIndex, pageSize, ref totalCount)
            : _sqlSugar.Queryable<T>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                .ToPageList(pageIndex, pageSize, ref totalCount);
        var list = new PageList<T>(page, pageIndex, pageSize, totalCount);
        return list;
    }

    public async Task<PageList<T>> QueryPageAsync(Expression<Func<T, bool>> predicate,
        Expression<Func<T, object>> orderByExpression, OrderByType orderByType, int pageIndex = 1,
        int pageSize = 10, bool blUseNoLock = false)
    {
        RefAsync<int> totalCount = 0;
        var page = blUseNoLock
            ? await _sqlSugar.Queryable<T>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                .With(SqlWith.NoLock).ToPageListAsync(pageIndex, pageSize, totalCount)
            : await _sqlSugar.Queryable<T>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                .ToPageListAsync(pageIndex, pageSize, totalCount);
        var list = new PageList<T>(page, pageIndex, pageSize, totalCount);
        return list;
    }

    public PageList<TResult> QueryPageMuch<T1, T2, TResult>(
        Expression<Func<T1, T2, object[]>> joinExpression,
        Expression<Func<T1, T2, TResult>> selectExpression,
        Expression<Func<T1, T2, bool>> whereLambda = null,
        int pageIndex = 1,
        int pageSize = 10, 
        bool blUseNoLock = false)
    {
        int totalCount = 0;
        var page = blUseNoLock
            ? _sqlSugar.Queryable(joinExpression)
                .WhereIF(whereLambda != null, whereLambda)
                .Select(selectExpression)
                .With(SqlWith.NoLock)
                .ToPageList(pageIndex, pageSize, ref totalCount)
            : _sqlSugar.Queryable(joinExpression)
                .WhereIF(whereLambda != null, whereLambda)
                .Select(selectExpression)
                .ToPageList(pageIndex, pageSize, ref totalCount);

        var list = new PageList<TResult>(page, pageIndex, pageSize, totalCount);
        return list;
    }

    public List<TResult> QueryMuch<T1, T2, TResult>(
        Expression<Func<T1, T2, object[]>> joinExpression,
        Expression<Func<T1, T2, TResult>> selectExpression,
        Expression<Func<T1, T2, bool>> whereLambda = null,
        bool blUseNoLock = false)
    {
        if (whereLambda == null)
            return blUseNoLock
                ? _sqlSugar.Queryable(joinExpression).Select(selectExpression).With(SqlWith.NoLock)
                    .ToList()
                : _sqlSugar.Queryable(joinExpression).Select(selectExpression).ToList();
        return blUseNoLock
            ? _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression)
                .With(SqlWith.NoLock).ToList()
            : _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();
    }

    public List<TResult> QueryMuch<T1, T2, T3, TResult>(
        Expression<Func<T1, T2, T3, object[]>> joinExpression,
        Expression<Func<T1, T2, T3, TResult>> selectExpression,
        Expression<Func<T1, T2, T3, bool>> whereLambda = null,
        bool blUseNoLock = false)
    {
        if (whereLambda == null)
            return blUseNoLock
                ? _sqlSugar.Queryable(joinExpression).Select(selectExpression).With(SqlWith.NoLock)
                    .ToList()
                : _sqlSugar.Queryable(joinExpression).Select(selectExpression).ToList();

        return blUseNoLock
            ? _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression)
                .With(SqlWith.NoLock)
                .ToList()
            : _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression)
                .ToList();
    }


    public List<T> SqlQueryable(string sql)
    {
        var list = _sqlSugar.SqlQueryable<T>(sql).ToList();
        return list;
    }
}

封装一个BaseDao，其他业务Dao可直接继承使用（目前只有查询）。在BaseDao首先要注入SqlSugar对象，可以使用 SqlSugarClient （非单例）或者 SqlSugarScope （单例） 对数据库进行增、删、查、改等功能，两者除了名字和使用模式不同外，功能和API都一模一样。

SqlSugarClient	优点：性能比SqlSugarScope有5%左右提升缺点： db不能跨上下文使用，需要new出一个新对象，偶发错误难排查适合有经验用户。模式：不能单例，不是线程安全对象
SqlSugarScope （5.0.3.4）特色功能	如果没有.NET 开发经验的推荐使用 SqlSugarScope可以解决线程安全问题，无脑使用，目前只有2种情况需要注意：1、异步漏掉await ，没有await需要db替换成db.CopyNew() ，不想加CopyNew就需要排查是否有漏掉 。2、Task.WhenAll必须要用Task.Run(async=>await 异步方法 ) 需要用Task.Run包一下（CopyNew也行）。支持同一个上下文共享事务模式：单例模式SqlSugarScope一直new会内存泄露 一定要用单例

按照官方推荐，无脑注入SqlSugarScope ：

public SqlSugarScope _sqlSugar;
public SqlSugarScopeProvider _sqlSugarProvider;
public BaseDao(SqlSugarScope sqlSugar)
{
    _sqlSugar = sqlSugar;
    _sqlSugarProvider = sqlSugar.GetConnectionScopeWithAttr<T>();
}

BaseDao中实现了根据Id查询（同步和异步）：

public T GetById(long id)
{    
    return _sqlSugar.Queryable<T>().InSingle(id);
}

查询所有（同步和异步），同时支持条件查询和排序：

public List<T> GetList(            
    Expression<Func<T, bool>> predicate = null,            
    Expression<Func<T, object>> orderByExpression = null,             
    OrderByType orderByType = OrderByType.Asc)
{
        return _sqlSugar.Queryable<T>()        
        .WhereIF(predicate != null, predicate)        
        .OrderByIF(orderByExpression != null, orderByExpression, orderByType)       
        .ToList();
}

分页查询（同步和异步）：

public PageList<T> QueryPage(Expression<Func<T, bool>> predicate, string orderBy = "",            int pageIndex = 1, int pageSize = 10, bool blUseNoLock = false)
{    
    int totalCount = 0;    
    var page = blUseNoLock        
    ? 
    _sqlSugar.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)            
    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock)            
    .ToPageList(pageIndex, pageSize, ref totalCount)        
    : 
    _sqlSugar.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)            
    .WhereIF(predicate != null, predicate)
    .ToPageList(pageIndex, pageSize, ref totalCount);  
    
    var list = new PageList<T>(page, pageIndex, pageSize, totalCount);   
    return list;
}

两表联合查询：

public List<TResult> QueryMuch<T1, T2, TResult>(            
Expression<Func<T1, T2, object[]>> joinExpression,            
Expression<Func<T1, T2, TResult>> selectExpression,            
Expression<Func<T1, T2, bool>> whereLambda = null,            
bool blUseNoLock = false)
{    
    if (whereLambda == null)        
        return blUseNoLock            
                ? _sqlSugar.Queryable(joinExpression).Select(selectExpression).With(SqlWith.NoLock)                
                    .ToList()            
                : _sqlSugar.Queryable(joinExpression).Select(selectExpression).ToList();    
    return blUseNoLock        
        ? _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression)            
        .With(SqlWith.NoLock).ToList()        
        : _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToList();}

三表联合查询：

public List<TResult> QueryMuch<T1, T2, T3, TResult>(    
Expression<Func<T1, T2, T3, object[]>> joinExpression,    
Expression<Func<T1, T2, T3, TResult>> selectExpression,    
Expression<Func<T1, T2, T3, bool>> whereLambda = null,    
bool blUseNoLock = false){    
    if (whereLambda == null)        
        return blUseNoLock            
        ? _sqlSugar.Queryable(joinExpression).Select(selectExpression).With(SqlWith.NoLock)                
        .ToList()           
        : _sqlSugar.Queryable(joinExpression).Select(selectExpression).ToList();
    return blUseNoLock        
    ? _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression)            
    .With(SqlWith.NoLock)            
    .ToList()        
    : _sqlSugar.Queryable(joinExpression).Where(whereLambda).Select(selectExpression)            
    .ToList();
}

两表联合分页：

public PageList<TResult> QueryPageMuch<T1, T2, TResult>(    
Expression<Func<T1, T2, object[]>> joinExpression,    
Expression<Func<T1, T2, TResult>> selectExpression,   
Expression<Func<T1, T2, bool>> whereLambda = null,    
int pageIndex = 1,    
int pageSize = 10,     
bool blUseNoLock = false){    
    int totalCount = 0;    
    var page = blUseNoLock        
    ? _sqlSugar.Queryable(joinExpression)            
    .WhereIF(whereLambda != null, whereLambda)            
    .Select(selectExpression)            
    .With(SqlWith.NoLock)            
    .ToPageList(pageIndex, pageSize, ref totalCount)        
    : _sqlSugar.Queryable(joinExpression)            
    .WhereIF(whereLambda != null, whereLambda)            
    .Select(selectExpression)            
    .ToPageList(pageIndex, pageSize, ref totalCount);
    
    var list = new PageList<TResult>(page, pageIndex, pageSize, totalCount);   
    return list;
}

OK，接下来我们看如何具体使用BaseDao，以User为例。

## user tableCREATE TABLE `sys_user` (  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',  `dept_id` bigint DEFAULT NULL COMMENT '部门ID',  `account` varchar(30) NOT NULL COMMENT '用户账号',  `name` varchar(30) NOT NULL COMMENT '用户昵称',  `email` varchar(50) DEFAULT '' COMMENT '用户邮箱',  `phonenumber` varchar(11) DEFAULT '' COMMENT '手机号码',  `gender` int DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',  `avatar` varchar(100) DEFAULT '' COMMENT '头像地址',  `password` varchar(100) DEFAULT '' COMMENT '密码',  `status` int DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',  `is_delete` tinyint(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',  `login_ip` varchar(128) DEFAULT '' COMMENT '最后登录IP',  `login_date` datetime DEFAULT NULL COMMENT '最后登录时间',  `create_user` bigint DEFAULT '0' COMMENT '创建者',  `create_time` datetime DEFAULT NULL COMMENT '创建时间',  `update_user` bigint DEFAULT '0' COMMENT '更新者',  `update_time` datetime DEFAULT NULL COMMENT '更新时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';

创建UserDao类。

public class UserDao : BaseDao<User>, IUserDao{    
    public UserDao(SqlSugarScope sqlSugar) : base(sqlSugar)    
    {
    }
    public User GetUser(long id)    
    {        
        return base.GetById(id);    
    }
    public UserDto GetUserById(long id)    
    {        
        List<UserDto> results = this.JoinQuery((t1, t2) => t1.Id == id);
        return results.Count > 0 ? results.First() : null;    
    }
    public PageList<User> PageUser(int pageIndex, int pageSize)    
    {        
        return base.QueryPage(            
            null,            
            u => u.Name,            
            OrderByType.Asc,            
            pageIndex,            
            pageSize            
        );    
    }
    public List<UserDto> GetUsers()    
    {        
        return this.JoinQuery();    
    }
    private List<UserDto> JoinQuery(        
        Expression<Func<User, Department, bool>> whereLambda = null        
    )    
    {        
        return base.QueryMuch<User, Department, UserDto>(        
            (t1, t2) => new object[] {            
                JoinType.Left,            
                t1.DepartmentId == t2.Id,        
            },       
            (t1, t2) => new UserDto        
            {            
                CreateTime = t1.CreateTime.ToString(),            
                UpdateTime = t1.UpdateTime.ToString(),            
                Id = t1.Id,            
                Name = t1.Name,            
                Status = t1.Status,            
                Depatment = t2.Name        
            },        
            whereLambda        
        );    
     }
}

根据Id查询指定User：

public User GetUser(long id){    
    return base.GetById(id);
}

联表查询User，UserDto隐藏了password字段，并且包含User所在的部门名称：

public List<UserDto> GetUsers(){    
    return this.JoinQuery();
}
private List<UserDto> JoinQuery(    
    Expression<Func<User, Department, bool>> whereLambda = null){    
        return base.QueryMuch<User, Department, UserDto>(    
            (t1, t2) => new object[] {        
            JoinType.Left,        
            t1.DepartmentId == t2.Id,    
        },    
        (t1, t2) => new UserDto //上面是t1 t2，这里必须也是    
        {        
            CreateTime = t1.CreateTime.ToString(),        
            UpdateTime = t1.UpdateTime.ToString(),        
            Id = t1.Id,        
            Name = t1.Name,        
            Status = t1.Status,        
            Depatment = t2.Name    
        },    
        whereLambda    
      );
}

分页查询User：

public PageList<User> PageUser(int pageIndex, int pageSize){    
    return base.QueryPage(        
        null,        
        u => u.Name,        
        OrderByType.Asc,        
        pageIndex,        
        pageSize        
   );
}

UserService层注入UserDao，直接调用对应方法即可。

OK，启动项目，通过Swagger调用接口，成功返回数据，大功告成！