42岁大龄程序员的迷茫

我真傻,真的。我单知道雪天是野兽在深山里没有食吃,会到村里来;我不知道春天也会有……

我真傻,真的。我单知道程序员要活到老学到老,年龄大了要失业;我不知道码农（新型农民工）也会有……

上周回老家有点无聊就去小破站看了点视频，是讲Dapr的实践（朝夕教育某讲师的公开课录屏），看完非常之震撼：原来微服务离我那么近！

虽然有失业的风险，但是我还是觉得技术人嘛，养家糊口应该没问题的，压力是有点大，但是“办法总比困难多”。所以其实我也不迷茫……

好长时间没有更新博客了，因为我觉得“Show me the Code!”比较重要，最近用.net 5+Dapper搭建了一个WebAPI的开发框架，今天分享给大伙。

几年前有一篇类似的文章大伙可以回顾一下：一次asp.net core3.1打造webapi开发框架的实践

开始show you the code

• 1、Swagger管理API说明文档
• 2、JwtBearer token验证
• 3、Swagger UI增加Authentication
• 4、Dapper实现的Repository
• 5、在.net 5下使用Log4net
• 6、与钉钉开放平台交互

项目中使用到的包清单：

¨C14C

1）StartUP

这个只贴图吧，教程大伙都看吐了，司空见惯：

用的是标准的套路熟悉的代码。

 #region JWT  
        services.Configure<TokenManagement>(Configuration.GetSection("tokenManagement"));  
        var token = Configuration.GetSection("tokenManagement").Get<TokenManagement>();  
        services.AddAuthentication(x =>  
        {  
            x.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;  
            x.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;  
        }).AddJwtBearer(x =>  
        {  
            x.RequireHttpsMetadata = false;  
            x.SaveToken = true;  
            x.TokenValidationParameters = new TokenValidationParameters  
            {  
                ValidateIssuerSigningKey = true,  
                IssuerSigningKey = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(token.Secret)),  
                ValidIssuer = token.Issuer,  
                ValidAudience = token.Audience,  
                ValidateIssuer = false,  
                ValidateAudience = false  
            };  
        });  
        #endregion

#region Swagger
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1",
new OpenApiInfo
{
Title = "TSP车载MES接口文档",
Version = "v1",
Contact = new OpenApiContact
{
Email = "tsjg@ts-precision.com",
Name = "MES团队",
Url = new Uri("http://www.ts-precision.com/")
}
});
// 为 Swagger 设置xml文档注释路径
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
c.IncludeXmlComments(xmlPath);
c.AddSecurityDefinition("Bearer",
new OpenApiSecurityScheme
{
Description = "请输入OAuth接口返回的Token，前置Bearer。示例：Bearer {Roken}",
Name = "Authorization",
In = ParameterLocation.Header,
Type = SecuritySchemeType.ApiKey
});
c.AddSecurityRequirement(new OpenApiSecurityRequirement
{
{
new OpenApiSecurityScheme
{
Reference = new OpenApiReference()
{
Id = "Bearer",
Type = ReferenceType.SecurityScheme
}
}, Array.Empty()
}
});
});
#endregion

#region Hangfire
string HangfireConn = Configuration.GetConnectionString("HangfireDB");
//SqlServer持久性
services.AddHangfire(x => x.UseStorage(new SqlServerStorage(
HangfireConn,
new SqlServerStorageOptions
{
QueuePollInterval = TimeSpan.FromSeconds(15),//- 作业队列轮询间隔。默认值为15秒。
JobExpirationCheckInterval = TimeSpan.FromHours(1),//- 作业到期检查间隔（管理过期记录）。默认值为1小时。
CountersAggregateInterval = TimeSpan.FromMinutes(5),//- 聚合计数器的间隔。默认为5分钟。
PrepareSchemaIfNecessary = true,//- 如果设置为true，则创建数据库表。默认是true。
DashboardJobListLimit = 50000,//- 仪表板作业列表限制。默认值为50000。
TransactionTimeout = TimeSpan.FromMinutes(1),//- 交易超时。默认为1分钟。
}))
);
services.AddHangfireHttpJobAgent();
//Hangfire非持久性
//services.AddHangfire(x => x.UseStorage(new MemoryStorage()));
#endregion

2）Dapper相关：

DynamicQuery实现半自动sql编写：

public static class DynamicQuery
{
public static string GetUpdateQuery(string tableName, dynamic item)
{
PropertyInfo[] props = item.GetType().GetProperties();
string[] columns = props.Select(p => p.Name).ToArray();
List parameters = columns.Select(name => name + "=@" + name).ToList();
return string.Format("UPDATE {0} SET {1} WHERE ID=@ID", tableName, string.Join(",", parameters));
}

    public static string GetInsertQuery(string tableName, dynamic item)  
    {  
        PropertyInfo\[\] props = item.GetType().GetProperties();  
        string\[\] columns = props.Select(p => p.Name).Where(s => s != "ID").ToArray();  
        return string.Format("INSERT INTO {0} ({1}) OUTPUT inserted.ID VALUES (@{2})",  
                             tableName,string.Join(",", columns),string.Join(",@", columns));  
    }

    public static QueryResult GetDynamicQuery<T>(string tableName, Expression<Func<T, bool>> expression)  
    {  
        List<QueryParameter> queryProperties = new List<QueryParameter>();  
        try  
        {  
            BinaryExpression body = (BinaryExpression)expression.Body;  
            WalkTree(body, ExpressionType.Default, ref queryProperties);  
        }  
        catch (Exception)  
        {  
            WalkTree(expression.Body, ExpressionType.Default, ref queryProperties);  
        }  
        IDictionary<string, object> expando = new ExpandoObject();  
        StringBuilder builder = new StringBuilder();  
        builder.Append("SELECT \* FROM ");  
        builder.Append($"{tableName} WITH(NOLOCK)");  
        builder.Append(" WHERE ");  
        for (int i = 0; i < queryProperties.Count; i++)  
        {  
            QueryParameter item = queryProperties\[i\];  
            if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)  
            {  
                builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName,  
                                             item.QueryOperator));  
            }  
            else  
            {  
                builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator));  
            }  
            expando\[item.PropertyName\] = item.PropertyValue;  
        }  
        return new QueryResult(builder.ToString().TrimEnd(), expando);  
    }

    private static void WalkTree(Expression body, ExpressionType linkingType,  
                         ref List<QueryParameter> queryProperties)  
    {  
        if (body is BinaryExpression)  
        {  
            var body2 = body as BinaryExpression;  
            if (body2.NodeType != ExpressionType.AndAlso && body2.NodeType != ExpressionType.OrElse)  
            {  
                string propertyName = GetPropertyName(body2);  
                object propertyValue = GetPropertyValue(body2.Right);  
                string opr = GetOperator(body.NodeType);  
                string link = GetOperator(linkingType);  
                queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));  
            }  
            else  
            {  
                WalkTree(body2.Left, body.NodeType, ref queryProperties);  
                WalkTree(body2.Right, body.NodeType, ref queryProperties);  
            }  
        }  
        if (body is MethodCallExpression)  
        {  
            var body2 = body as MethodCallExpression;  
            string propertyName = body2.Object.ToString().Split(".").LastOrDefault();  
            object propertyValue = body2.Arguments.FirstOrDefault();  
            string link = GetOperator(linkingType);  
            if (body2.Method.Name.Equals("Contains"))  
            {  
                string val = propertyValue.ToString().Replace(((char)34).ToString(), "");  
                if (!val.Contains("%"))  
                    queryProperties.Add(new QueryParameter(link, propertyName, $"%{val}%", "LIKE"));  
                else  
                    queryProperties.Add(new QueryParameter(link, propertyName, $"{val}", "LIKE"));  
            }  
            if (body2.Method.Name.Equals("Equals"))  
            {  
                string val = propertyValue.ToString().Replace(((char)34).ToString(), "");  
                queryProperties.Add(new QueryParameter(link, propertyName, val, "="));  
            }  
        }  
    }

    private static object GetPropertyValue(Expression source)  
    {  
        ConstantExpression constantExpression = source as ConstantExpression;  
        if (constantExpression != null)  
        {  
            return constantExpression.Value;  
        }  
        Expression<Func<object>> evalExpr = Expression.Lambda<Func<object>>(Expression.Convert(source, typeof(object)));  
        Func<object> evalFunc = evalExpr.Compile();  
        object value = evalFunc();  
        return value;  
    }

    private static string GetPropertyName(BinaryExpression body)  
    {  
        string propertyName = body.Left.ToString().Split(new char\[\] { '.' })\[1\];  
        if (body.Left.NodeType == ExpressionType.Convert)  
        {  
            // hack to remove the trailing ) when convering.  
            propertyName = propertyName.Replace(")", string.Empty);  
        }  
        return propertyName;  
    }

    private static string GetOperator(ExpressionType type)  
    {  
        switch (type)  
        {  
            case ExpressionType.Equal:  
                return "=";

            case ExpressionType.NotEqual:  
                return "!=";

            case ExpressionType.LessThan:  
                return "<";

            case ExpressionType.GreaterThan:  
                return ">";

            case ExpressionType.AndAlso:  
            case ExpressionType.And:  
                return "AND";

            case ExpressionType.Or:  
            case ExpressionType.OrElse:  
                return "OR";

            case ExpressionType.Default:  
                return string.Empty;

            case ExpressionType.GreaterThanOrEqual:  
                return ">=";

            case ExpressionType.LessThanOrEqual:

                return "<=";

            default:  
                throw new NotImplementedException();  
        }  
    }  
}

基于Dapper的DbContext :DapperDbContextBase

public abstract class DapperDbContextBase : IDbContext
{
#region Constructors

    /// <summary>  
    /// 构造函数  
    /// </summary>  
    /// <param name="connectString">连接字符串</param>  
    protected DapperDbContextBase(string connectString)  
    {  
        ConnectString = connectString;  
    }

    #endregion Constructors

    #region Properties

    /// <summary>  
    ///获取 是否开启事务提交  
    /// </summary>  
    public IDbTransaction CurrentTransaction { get; private set; }

    #endregion Properties

    #region Fields

    /// <summary>  
    ///     当前数据库连接  
    /// </summary>  
    public IDbConnection CurrentConnection =>  
        TransactionEnabled ? CurrentTransaction.Connection : CreateConnection();

    /// <summary>  
    ///     获取 是否开启事务提交  
    /// </summary>  
    public bool TransactionEnabled => CurrentTransaction != null;

    /// <summary>  
    ///     连接字符串  
    /// </summary>  
    protected readonly string ConnectString;

    #endregion Fields

    #region Methods

    /// <summary>  
    /// 显式开启数据上下文事务  
    /// </summary>  
    /// <param name="isolationLevel">指定连接的事务锁定行为</param>  
    public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.Unspecified)  
    {  
        if (!TransactionEnabled) CurrentTransaction = CreateConnection().BeginTransaction(isolationLevel);  
    }

    /// <summary>  
    ///     提交当前上下文的事务更改  
    /// </summary>  
    /// <exception cref="DataAccessException">提交数据更新时发生异常：" + msg</exception>  
    public void Commit()  
    {  
        if (TransactionEnabled)  
            try  
            {  
                CurrentTransaction.Commit();  
            }  
            catch (Exception ex)  
            {  
                if (ex.InnerException?.InnerException is SqlException sqlEx)  
                {  
                    var msg = DataBaseHelper.GetSqlExceptionMessage(sqlEx.Number);  
                    throw new DataAccessException("提交数据更新时发生异常：" + msg, sqlEx);  
                }

                throw;  
            }  
    }

    /// <summary>  
    ///     创建记录  
    /// </summary>  
    /// <param name="entity">需要操作的实体类</param>  
    /// <returns>操作是否成功</returns>  
    public bool Create<T>(T entity)  
        where T : ModelBase  
    {  
        return CurrentConnection.Insert(new List<T> { entity }, CurrentTransaction) > 0;  
    }

    /// <summary>  
    ///异步创建记录  
    /// </summary>  
    /// <param name="entity">需要操作的实体类</param>  
    /// <returns>操作是否成功</returns>  
    public async Task<bool> CreateAsync<T>(T entity)  
        where T : ModelBase  
    {  
        var result = await CurrentConnection.InsertAsync(  
            new List<T>  
            {  
                entity  
            }, CurrentTransaction);  
        bool b = result > 0;  
        return b;  
    }

    /// <summary>  
    ///创建数据库连接IDbConnection  
    /// </summary>  
    /// <returns></returns>  
    public abstract IDbConnection CreateConnection();

    /// <summary>  
    ///     删除记录  
    /// </summary>  
    /// <returns>操作是否成功</returns>  
    /// <param name="entity">需要操作的实体类.</param>  
    public bool Delete<T>(T entity)  
        where T : ModelBase  
    {  
        return CurrentConnection.Delete(entity);  
    }  
    /// <summary>  
    /// 异步删除记录  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="entity"></param>  
    /// <returns></returns>  
    public async Task<bool> DeleteAsync<T>(T entity)  
        where T : ModelBase  
    {  
        var result = await CurrentConnection.DeleteAsync(entity);  
        return result;  
    }  
    /// <summary>  
    ///条件判断是否存在  
    /// </summary>  
    /// <returns>是否存在</returns>  
    /// <param name="predicate">判断条件委托</param>  
    public bool Exist<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        var result = CurrentConnection.ExecuteScalar(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);  
        return result != null;  
    }  
    /// <summary>  
    /// 异步判断符合条件的实体是否存在  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="predicate"></param>  
    /// <returns></returns>  
    public async Task<bool> ExistAsync<T>(Expression<Func<T, bool>> predicate = null)  
      where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        var result = await CurrentConnection.ExecuteScalarAsync(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);  
        return result != null;  
    }

    /// <summary>  
    /// 根据id获取记录  
    /// </summary>  
    /// <returns>记录</returns>  
    /// <param name="id">id.</param>  
    public T GetByKeyId<T>(object id)  
        where T : ModelBase  
    {  
        return CurrentConnection.Get<T>(id, CurrentTransaction);  
    }  
    /// <summary>  
    /// 异步根据id获取记录  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="id"></param>  
    /// <returns></returns>  
    public async Task<T> GetByKeyIdAsync<T>(object id)  
        where T : ModelBase  
    {  
        var result = await CurrentConnection.GetAsync<T>(id, CurrentTransaction);  
        return result;  
    }

    /// <summary>  
    ///条件获取记录集合  
    /// </summary>  
    /// <returns>集合</returns>  
    /// <param name="predicate">筛选条件.</param>  
    public List<T> GetList<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        return CurrentConnection.Query<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction).ToList();  
    }

    /// <summary>  
    /// 条件获取记录集合(异步)  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="predicate"></param>  
    /// <returns></returns>  
    public async Task<List<T>> GetListAsync<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        var result = await CurrentConnection.QueryAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);  
        return result.ToList();  
    }

    /// <summary>  
    ///条件获取记录第一条或者默认  
    /// </summary>  
    /// <returns>记录</returns>  
    /// <param name="predicate">筛选条件.</param>  
    public T GetFirstOrDefault<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        return CurrentConnection.QueryFirstOrDefault<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);  
    }

    /// <summary>  
    /// 条件获取记录第一条或者默认(异步)  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="predicate"></param>  
    /// <returns></returns>  
    public async Task<T> GetFirstOrDefaultAsync<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        var entity = await CurrentConnection.QueryFirstOrDefaultAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);  
        return entity;  
    }

    /// <summary>  
    /// 条件查询  
    /// </summary>  
    /// <returns>IQueryable</returns>  
    /// <param name="predicate">筛选条件.</param>  
    public IQueryable<T> Query<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        var result = CurrentConnection.Query<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction).ToList();  
        return result.AsQueryable();  
    }

    /// <summary>  
    /// 条件查询(异步)  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="predicate"></param>  
    /// <returns></returns>  
    public async Task<IQueryable<T>> QueryAsync<T>(Expression<Func<T, bool>> predicate = null)  
        where T : ModelBase  
    {  
        var tableName = GetTableName<T>();  
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);  
        var result = await CurrentConnection.QueryAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);  
        return result.AsQueryable();  
    }

    /// <summary>  
    ///显式回滚事务，仅在显式开启事务后有用  
    /// </summary>  
    public void Rollback()  
    {  
        if (TransactionEnabled) CurrentTransaction.Rollback();  
    }

    /// <summary>  
    ///执行Sql 脚本查询  
    /// </summary>  
    /// <param name="sql">Sql语句</param>  
    /// <param name="parameters">参数</param>  
    /// <returns>集合</returns>  
    public IEnumerable<T> SqlQuery<T>(string sql, IDbDataParameter\[\] parameters)  
    {  
        var dataParameters = CreateParameter(parameters);  
        return CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction);  
    }

    /// <summary>  
    ///执行Sql 脚本查询(异步)  
    /// </summary>  
    /// <param name="sql">Sql语句</param>  
    /// <param name="parameters">参数</param>  
    /// <returns>集合</returns>  
    public async Task<IEnumerable<T>> SqlQueryAsync<T>(string sql, IDbDataParameter\[\] parameters)  
    {  
        var dataParameters = CreateParameter(parameters);  
        var list = await CurrentConnection.QueryAsync<T>(sql, dataParameters, CurrentTransaction);  
        return list;  
    }

    /// <summary>  
    /// 执行Sql 脚本查询带分页(linq分页)  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="sql"></param>  
    /// <param name="parameters"></param>  
    /// <param name="pageIndex"></param>  
    /// <param name="pageSize"></param>  
    /// <returns></returns>  
    public PagedList<T> SqlQueryAndPagedList<T>(string sql, IDbDataParameter\[\] parameters, int pageIndex, int pageSize)  
    {  
        var dataParameters = CreateParameter(parameters);  
        var result = CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction);  
        return PageHelper.ToPagedList(result.AsQueryable(), pageIndex, pageSize);  
    }

    /// <summary>  
    /// 带分页（服务器端分页）的自定义查询  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="sql"></param>  
    /// <param name="orderField"></param>  
    /// <param name="pageSize"></param>  
    /// <param name="pageIndex"></param>  
    /// <param name="total"></param>  
    /// <returns></returns>  
    public IEnumerable<T> SqlQueryPage<T>(string sql, string orderField, int pageSize, int pageIndex, out int total) where T : class, new()  
    {  
        int num = (pageIndex - 1) \* pageSize;  
        int num1 = (pageIndex) \* pageSize;  
        orderField = "order by " + orderField;  
        StringBuilder sb = new StringBuilder();  
        sb.Append("Select \* From (Select ROW\_NUMBER() Over (" + orderField + ")");  
        sb.Append(" As rowNum, \* From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");  
        total = Convert.ToInt32(SqlQuery<int>("Select Count(1) From (" + sql + ") As t", null).FirstOrDefault());  
        string last\_sql = sb.ToString();  
        var dataQuery = CurrentConnection.Query<T>(last\_sql).ToList();  
        return dataQuery;  
    }

    /// <summary>  
    /// FindObjectBase对象通用查询带分页  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="t"></param>  
    /// <param name="orderField"></param>  
    /// <param name="pageSize"></param>  
    /// <param name="pageIndex"></param>  
    /// <param name="total"></param>  
    /// <returns></returns>  
    public IEnumerable<T> SqlQueryPage<T>(T t, string orderField, int pageSize, int pageIndex, out int total) where T : FindObjectBase, new()  
    {  
        string tableName = GetQueryTableName<T>();  
        StringBuilder sb = new StringBuilder();  
        sb.Append("SELECT \* FROM ");  
        sb.Append($"{tableName} WITH(NOLOCK) ");  
        sb.Append(" WHERE 1=1 AND ");  
        var props = typeof(T).GetProperties().Where(p => !p.Name.StartsWith("Chk\_"));  
        foreach (var prop in props)  
        {  
            object obj = prop.GetValue(t, null);  
            if (obj != null)  
            {  
                if (prop.Name.ToUpper().StartsWith("LIKE\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("LIKE\_", "")} LIKE {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("GT\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("GT\_", "")} > {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("ST\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("ST\_", "")} < {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("ST\_Eq\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("ST\_Eq\_", "")} <= {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("GT\_Eq\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("GT\_Eq\_", "")} >= {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("BETWEEN\_"))  
                {  
                    string\[\] array = obj.ToString().Split("|");  
                    sb.Append($"          {prop.Name.Replace("GT\_Eq\_", "")} BETWEEN {array.FirstOrDefault()} AND {array.LastOrDefault()} AND");  
                }  
                else  
                    sb.Append($"          {prop.Name}={obj} AND ");  
            }  
        }  
        string sql = sb.ToString().ToUpper().TrimEnd("AND ".ToCharArray());  
        int num = (pageIndex - 1) \* pageSize;  
        int num1 = (pageIndex) \* pageSize;  
        orderField = "order by " + orderField;  
        StringBuilder builder = new();  
        builder.Append("Select \* From (Select ROW\_NUMBER() Over (" + orderField + ")");  
        builder.Append(" As rowNum, \* From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");  
        total = Convert.ToInt32(SqlQuery<int>("Select Count(1) From (" + sql + ") As t", null).FirstOrDefault());  
        string last\_sql = builder.ToString();  
        var dataQuery = CurrentConnection.Query<T>(last\_sql).ToList();  
        return dataQuery;  
    }  
    /// <summary>  
    /// FindObjectBase对象通用查询  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="t"></param>  
    /// <returns></returns>  
    public IEnumerable<T> SqlQuery<T>(T t) where T : FindObjectBase, new()  
    {  
        string tableName = GetQueryTableName<T>();  
        StringBuilder builder = new();  
        builder.Append("SELECT \* FROM ");  
        builder.Append($"{tableName} WITH(NOLOCK) ");  
        builder.Append(" WHERE 1=1 AND ");  
        var props = typeof(T).GetProperties();  
        foreach (var prop in props)  
        {  
            object obj = prop.GetValue(t, null);  
            if (obj != null)  
            {  
                builder.Append($" {prop.Name}={obj} AND ");  
            }  
        }  
        string sql = builder.ToString().ToUpper().TrimEnd("AND".ToCharArray());  
        var dataQuery = CurrentConnection.Query<T>(sql).ToList();  
        return dataQuery;  
    }

    /// <summary>  
    /// 自定义查询返回DataTable  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="sql"></param>  
    /// <param name="parameters"></param>  
    /// <returns></returns>  
    public DataTable SqlQueryReturnDataTable<T>(string sql, IDbDataParameter\[\] parameters)  
    {  
        var dataParameters = CreateParameter(parameters);  
        var list = CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction).ToList();  
        return ConvertExtension.ToDataTable(list);  
    }

    /// <summary>  
    /// 带分页（服务器端分页）的自定义查询  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="sql"></param>  
    /// <param name="orderField"></param>  
    /// <param name="pageSize"></param>  
    /// <param name="pageIndex"></param>  
    /// <param name="total"></param>  
    /// <returns></returns>  
    public DataTable SqlQueryReturnDataTable<T>(string sql, string orderField, int pageSize, int pageIndex, out int total) where T : class, new()  
    {  
        int num = (pageIndex - 1) \* pageSize;  
        int num1 = (pageIndex) \* pageSize;  
        orderField = "order by " + orderField;  
        StringBuilder sb = new StringBuilder();  
        sb.Append("Select \* From (Select ROW\_NUMBER() Over (" + orderField + ")");  
        sb.Append(" As rowNum, \* From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");  
        total = Convert.ToInt32(SqlQuery<int>("Select Count(1) From (" + sql + ") As t", null).FirstOrDefault());  
        string last\_sql = sb.ToString();  
        var list = CurrentConnection.Query<T>(last\_sql).ToList();  
        return ConvertExtension.ToDataTable(list);  
    }

    /// <summary>  
    ///更新实体类记录  
    /// </summary>  
    /// <returns>操作是否成功.</returns>  
    /// <param name="entity">实体类记录.</param>  
    public bool Update<T>(T entity)  
        where T : ModelBase  
    {  
        return CurrentConnection.Update(entity, CurrentTransaction);  
    }

    /// <summary>  
    /// 更新实体类记录(异步)  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <param name="entity"></param>  
    /// <returns></returns>  
    public async Task<bool> UpdateAsync<T>(T entity)  
        where T : ModelBase  
    {  
        return await CurrentConnection.UpdateAsync(entity, CurrentTransaction);  
    }

    /// <summary>  
    /// 构建Sql Parameter  
    /// </summary>  
    /// <param name="parameters"></param>  
    /// <returns></returns>  
    private DapperParameter CreateParameter(IDbDataParameter\[\] parameters)  
    {  
        if (!(parameters?.Any() ?? false)) return null;

        var dataParameters = new DapperParameter();  
        foreach (var parameter in parameters) dataParameters.Add(parameter);  
        return dataParameters;  
    }

    /// <summary>  
    /// 获取实体的TableName  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <returns></returns>  
    private string GetTableName<T>()  
        where T : ModelBase  
    {  
        var tableCfgInfo = AttributeHelper.Get<T,TableAttribute>();  
        return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name;  
    }

    /// <summary>  
    /// 获取实体的TableName  
    /// </summary>  
    /// <typeparam name="T"></typeparam>  
    /// <returns></returns>  
    private string GetQueryTableName<T>()  
        where T : FindObjectBase  
    {  
        var tableCfgInfo = AttributeHelper.Get<T,TableAttribute>();  
        return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name;  
    }

    /// <summary>  
    ///执行与释放或重置非托管资源关联的应用程序定义的任务。  
    /// </summary>  
    public void Dispose()  
    {  
        if (CurrentTransaction != null)  
        {  
            CurrentTransaction.Dispose();  
            CurrentTransaction = null;  
        }

        CurrentConnection?.Dispose();  
    }

    public List<T> GetList<T>(T t) where T : FindObjectBase  
    {  
        string tableName = GetQueryTableName<T>();  
        StringBuilder sb = new StringBuilder();  
        sb.Append("SELECT \* FROM ");  
        sb.Append($"{tableName} WITH(NOLOCK) ");  
        sb.Append(" WHERE 1=1 AND ");  
        var props = typeof(T).GetProperties().Where(p=>!p.Name.StartsWith("Chk\_"));  
        foreach (var prop in props)  
        {  
            object obj = prop.GetValue(t, null);  
            if (obj != null)  
            {  
                if (prop.Name.ToUpper().StartsWith("LIKE\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("LIKE\_","")} LIKE {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("GT\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("GT\_", "")} > {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("ST\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("ST\_", "")} < {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("ST\_Eq\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("ST\_Eq\_", "")} <= {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("GT\_Eq\_"))  
                {  
                    sb.Append($"          {prop.Name.Replace("GT\_Eq\_", "")} >= {obj} AND ");  
                }  
                if (prop.Name.ToUpper().StartsWith("BETWEEN\_"))  
                {  
                    string\[\] array = obj.ToString().Split("|");  
                    sb.Append($"          {prop.Name.Replace("GT\_Eq\_", "")} BETWEEN {array.FirstOrDefault()} AND {array.LastOrDefault()} AND");  
                }  
                else  
                    sb.Append($"          {prop.Name}={obj} AND ");  
            }  
        }  
        string sql = sb.ToString().ToUpper().TrimEnd("AND ".ToCharArray());  
        return SqlQuery<T>(sql, null).ToList();  
    }

    public DataTable SqlQueryReturnDataTable(string sql, IDbDataParameter\[\] parameters)  
    {  
        var list = CurrentConnection.Query(sql, parameters);  
        return ConvertExtension.ToDataTable(list);  
    } 

    #endregion Methods  
}

请原谅我没有把这大段的代码收缩，如果你只是想跑起来看看，请忽略这些代码。后边有下载链接。

///

    public async Task<bool> DeleteAsync<T1>(T1 entity) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.DeleteAsync(entity);  
    }

    public async Task<bool> ExistAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.ExistAsync(predicate);  
    }

    public async Task<T1> GetByKeyIdAsync<T1>(object id) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.GetByKeyIdAsync<T1>(id);  
    }

    public async Task<List<T1>> GetListAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.GetListAsync(predicate);  
    }

    public async Task<T1> GetFirstOrDefaultAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.GetFirstOrDefaultAsync(predicate);  
    }

    public async Task<bool> CreateAsync<T1>(T1 entity) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.CreateAsync(entity);  
    }

    public async Task<IQueryable<T1>> QueryAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.QueryAsync(predicate);  
    }

    public async Task<bool> UpdateAsync<T1>(T1 entity) where T1 : ModelBase  
    {  
        return await \_dapperDbContext.UpdateAsync(entity);  
    }

    public List<T1> GetList<T1>(T1 t) where T1 : FindObjectBase  
    {  
        return \_dapperDbContext.GetList(t);  
    }  
}