1.该表达式插件通过模型继承获取对应的能力。目前提供的模型有
-
QueryBase :基础查询类
-
PageModel:分页查询参数类(默认每页分页20条)
-
QueryModel:查询模型,对比QueryBase 多了查询集合QueryItems和过滤FilterFields。前端可以在后端给与的基础上,添加查询参数,对于前端来说权限有点大,慎用。
-
QueryPageModel:查询模型,对比PageModel多了查询集合QueryItems和过滤FilterFields。前端可以在后端给与的基础上,添加查询参数,对于前端来说权限有点大,慎用。
2.属性名约束
-
编号查询:编号查询最好是以Id结尾,不然如果编号为字符串的话,查询方式会以Contains形式查询。
-
时间格式:以 Start,End 结尾 ,生成条件为 >= 和<=。
-
数字范围:属性名称 以 Min,Max 结尾 ,生成条件为 >= 和<=。
-
字符串查询: 名字需要和表字段一致,生成条件为 Contains
3.特性约束。这部分主要为了弥补属性名的不足。目前提供特性的构造函数以及特性属性如下
/// <summary> /// 字段属性 /// </summary> /// <param name="condition">查询方法</param> /// <param name="conditionType">查询类型</param> /// <param name="value">属性值</param> public ConditionAttribute(EnumCondition condition = EnumCondition.Equal, EnumConditionType conditionType = EnumConditionType.And, object value = null) { Condition = condition; ConditionType = conditionType; Value = value; } /// <summary> /// 字段属性 /// </summary> /// <param name="propertyName">PropertyName</param> /// <param name="condition">查询方法</param> /// <param name="conditionType">查询类型</param> /// <param name="value">属性值</param> public ConditionAttribute(string propertyName, EnumCondition condition = EnumCondition.Equal, EnumConditionType conditionType = EnumConditionType.And, object value = null) { Condition = condition; ConditionType = conditionType; PropertyName = propertyName; Value = value; }
-
属性名:PropertyName
-
属性值:Value
-
查询方法:Condition
-
查询类型:ConditionType(and/or)
-
是否唯一属性名称:IsSinglePropertyName
-
是否区分大小写:IsCaseSensitive
4.排序处理
-
默认排序方法 DefaultOrderBy(string propName, bool isDesc = true, bool isClearReserved = true)。参数解析:属性名、降序升序、是否清掉之前的排序
-
默认排序方法 AddOrderByItem(string propName, bool isDesc = true)。参数解析:属性名、降序升序。可添加多个,优先级按照,添加的顺序来。
5.分页配置
-
重置当前页:Page(this PageModel model, object page)
-
重置当前每页显示:PageSize(this PageModel model, object pageSize)
-
重置每页配置: public static PageModel Paging<Input>(this PageModel model, Input input) where Input : class, IPageModel
IPageModel对应代码
/// <summary> /// 分页相关 /// </summary> public interface IPageModel { /// <summary> /// 当前页 /// </summary> [NotQuery] int Page { get; set; } /// <summary> /// 页码 /// </summary> [NotQuery] int PageSize { get; set; } }
6.参数忽略
-
提供了特性NotQueryAttribute,在对于属性添加即可忽略构建查询。
7.构建表达式
-
只要类继承了QueryBase 等这四个模型,即可以通过ToExpression<TSource>(),构建对于自己模型的表达式。
8.查询扩展之返回IQueryable数据,插件基于IQueryable类型数据提供了扩展方法 如下
-
QueryBase、QueryModel、PageModel、QueryPageModel 类作为参数方法如下
/// <summary> /// 获取条件下数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="source"></param> /// <param name="input"></param> /// <returns></returns> public static IQueryable<T> WhereExt<T>(this IQueryable<T> source, QueryBase input) {} /// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static (IQueryable<T> query, int total) PageWhere<T>(this IQueryable<T> source, QueryBase input) /// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static IQueryable<T> WhereExt<T>(this IQueryable<T> source, QueryBase input, out int total)
-
表达式树作为参数扩展查询
/// <summary> /// 通过表达式树查询扩展 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <param name="source">待筛选和排序的数据源</param> /// <param name="expression">查询表达式</param> /// <param name="total">总页数</param> /// <returns></returns> public static IQueryable<T> WhereExt<T>(this IQueryable<T> source, Expression<Func<T, bool>> expression, out int total) /// <summary> /// 查询扩展 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <typeparam name="TKey">排序参数</typeparam> /// <param name="source">待筛选和排序的数据源</param> /// <param name="expression">查询表达式</param> /// <param name="orderBy">排序表达式</param> /// <returns></returns> public static IQueryable<T> WhereExt<T, TKey>(this IQueryable<T> source, Expression<Func<T, bool>> expression, Expression<Func<T, TKey>> orderBy) /// <summary> /// 查询扩展 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <typeparam name="TKey">排序参数</typeparam> /// <param name="source">待筛选和排序的数据源</param> /// <param name="expression">查询表达式</param> /// <param name="orderBy">排序表达式</param> /// <param name="IsOrderByDesc">是否降序</param> /// <returns></returns> public static IQueryable<T> WhereExt<T, TKey>(this IQueryable<T> source, Expression<Func<T, bool>> expression, Expression<Func<T, TKey>> orderBy, bool IsOrderByDesc = true) /// <summary> /// 查询扩展 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <param name="source">数据源</param> /// <param name="expression">表达式树</param> /// <param name="orderByItems">排序集合</param> /// <returns></returns> public static IQueryable<T> WhereExt<T>(this IQueryable<T> source, Expression<Func<T, bool>> expression, params OrderByItem[] orderByItems) /// <summary> /// 查询扩展 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <param name="source">数据源</param> /// <param name="expression">表达式树</param> /// <param name="total">总页数</param> /// <param name="orderByItems">排序集合</param> /// <returns></returns> public static IQueryable<T> WhereExt<T>(this IQueryable<T> source, Expression<Func<T, bool>> expression, out int total, params OrderByItem[] orderByItems) /// <summary> /// 查询扩展 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <param name="source">数据源</param> /// <param name="expression">表达式树</param> /// <param name="total">总页数</param> /// <param name="page">当前页</param> /// <param name="pageSize">每页显示</param> /// <param name="orderByItems">排序集合</param> /// <returns></returns> public static IQueryable<T> WhereExt<T>(this IQueryable<T> source, Expression<Func<T, bool>> expression, out int total, int page, int pageSize, params OrderByItem[] orderByItems)
-
排序。QueryBase、QueryModel、PageModel、QueryPageModel4 都提供了扩展方法,参考代码如:
/// <summary> /// 排序 /// </summary> /// <typeparam name="T">数据源的元素类型</typeparam> /// <param name="query">资源</param> /// <param name="input">查询模型</param> /// <returns></returns> public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, QueryBase input)
-
分页。PageModel、QueryPageModel 提供了分页扩展方法,参考代码如:
/// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static IQueryable<T> Page<T>(this IQueryable<T> query, PageModel input) /// <summary> /// 分页 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static IQueryable<T> Page<T>(this IQueryable<T> query, QueryPageModel input)
-
分页且排序。PageModel、QueryPageModel4个模型也提供了扩展方法,参考代码如:
/// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static IQueryable<T> OrderByPage<T>(this IQueryable<T> query, PageModel input) /// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static IQueryable<T> OrderByPage<T>(this IQueryable<T> query, QueryPageModel input)
9.查询扩展之返回List数据(需要引入Sy.ExpressionBuilder.Sql组件),插件基于IQueryable类型数据提供了扩展方法 如下
-
ToList()方法扩展。对应模型 QueryBase,QueryModel。扩展方法如下
/// <summary> /// 获取条件下列表 /// </summary> /// <param name="queryModel"></param> /// <param name="total"></param> /// <returns></returns> public static List<T> ToList<T>(this IQueryable<T> source, QueryBase input) /// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static List<T> ToList<T>(this IQueryable<T> source, QueryModel input)
-
PageToList()方法扩展。对应模型 QueryPageModel,PageModel。扩展方法如下
/// <summary> /// 获取条件下列表 /// </summary> /// <param name="input"></param> /// <param name="total"></param> /// <returns></returns> public static (List<T> list, int total) PageToList<T>(this IQueryable<T> source, QueryPageModel input) /// <summary> /// 获取条件下列表 /// </summary> /// <param name="queryModel"></param> /// <param name="total"></param> /// <returns></returns> public static (List<T> list, int total) PageToList<T>(this IQueryable<T> source, PageModel input)
10.举个例子
-
构建用户基础数据,给个代码
[TestMethod()] public void ToExpressionTest() { var userlist = new List<User>(); var sysList = new List<Sys>(); var sysList2 = new List<Sys>(); sysList.Add(new Sys { Id = "1", SysName = "仓管系统" }); sysList.Add(new Sys { Id = "2", SysName = "人力资源系统" }); sysList2.Add(new Sys { Id = "1", SysName = "仓管系统" }); var user1 = new User { Id = "1", UserName = "张三", Tel = "18888888888", Gender = EnumGender.Man, RoleId = 1, CreateTime = DateTime.Parse("2021-9-22 10:50:50"), Sys = sysList, Role = new Role { RoleName = "超级管理员", Id = 1 } }; var user2 = new User { Id = "2", UserName = "李四", Tel = "16666666666", Gender = (EnumGender)3, RoleId = 2, CreateTime = DateTime.Parse("2021-9-21 10:50:50"), Sys = sysList2, Role = new Role { RoleName = "管理员", Id = 2 } }; userlist.Add(user1); userlist.Add(user2); Expression<Func<User, bool>> query; AllUserDto input = new AllUserDto(); input.RoleIds = new List<long>() { 1, 2 }; input.UserName = "1".ToLower(); input.SysName = "仓管系统"; input.RoleName = "管理员"; input.CreateTimeStart = DateTime.Parse("2021-9-22"); input.CreateTimeEnd = DateTime.Parse("2021-9-25"); input.Gender = EnumGender.Man; input.IsMan = (EnumGender)2; query = input.ToExpression<User>(); var result = userlist.Where(query.Compile()).ToList(); // BenchmarkRunner.Run<TestExpress>(); }
-
给个效果图吧
-
声明基础表。用户表、角色表、系统表、以及性别枚举
/// <summary> /// 用户表 /// </summary> public class User { /// <summary> /// 编号 /// </summary> [Key] public string Id { get; set; } /// <summary> /// 名称 /// </summary> [DependenceProp(PropName = "UserName123213", Ext1 = "名称")] public string UserName { get; set; } /// <summary> /// 手机 /// </summary> public string Tel { get; set; } /// <summary> /// 角色编号 /// </summary> [ForeignKey("RoleId")] public long RoleId { get; set; } /// <summary> /// 角色 /// </summary> public Role Role { get; set; } /// <summary> /// 系统 /// </summary> public List<Sys> Sys { get; set; } /// <summary> /// 创建时间 /// </summary> public DateTime CreateTime { get; set; } /// <summary> /// 性别 /// </summary> public EnumGender Gender { get; set; } } /// <summary> /// 角色表 /// </summary> public class Role { /// <summary> /// 角色名 /// </summary> [DisplayName("角色名")] public string? RoleName { get; set; } /// <summary> /// 角色编号 /// </summary> [DisplayName("角色编号")] [Key] public long Id { get; set; } } /// <summary> /// 系统表 /// </summary> public class Sys { /// <summary> /// 系统名称 /// </summary> [Key] public string SysName { get; set; } /// <summary> /// 编号 /// </summary> public string Id { get; set; } } /// <summary> /// 性别 /// </summary> [Flags] public enum EnumGender { /// <summary> /// 男 /// </summary> [Description("男")] Man = 1 << 0, /// <summary> /// 女 /// </summary> [Description("女")] Women = 1 << 1, /// <summary> /// 其他 /// </summary> [Description("其他")] Other = 1 << 2, }
-
声明要查询参数表。如表AllUserDto,代码如下
/// <summary> /// 查询参数实体 /// </summary> public class AllUserDto : QueryPageModel { /// <summary> /// 创建时间 开始 /// </summary> [TimeSpan(4, EnumTimeType.Day)] public DateTime? CreateTimeStart { get; set; } /// <summary> /// 创建时间 结束 /// </summary> public DateTime? CreateTimeEnd { get; set; } /// <summary> /// 角色编号 /// </summary> public string? Tel { get; set; } /// <summary> /// 角色编号 /// </summary> [Condition("Role.Id", EnumCondition.In)] public List<long> RoleIds { get; set; } = new List<long>(); /// <summary> /// 角色名称 /// </summary> [Condition("Role.RoleName", EnumCondition.Contains, EnumConditionType.And)] public string? RoleName { get; set; } /// <summary> /// 系统名称 /// </summary> [Condition("Sys[SysName]", EnumCondition.Contains)] [Group(1)] public string? SysName { get; set; } /// <summary> /// 名称 /// </summary> [Condition(EnumCondition.Contains, EnumConditionType.Or)] [Group(1, EnumConditionType.Or)] public string? UserName { get; set; } /// <summary> /// 性别 /// </summary> //[Group(0, EnumConditionType.Or)] [Condition(conditionType: EnumConditionType.Or, IsSinglePropertyName = false)] public EnumGender? Gender { get; set; } /// <summary> /// 男 /// </summary> [Condition("Gender", EnumCondition.HasFlag, conditionType: EnumConditionType.Or, IsSinglePropertyName = false)] public EnumGender? IsMan { get; set; } /// <summary> /// 是否是女的 /// </summary> [Condition($"Gender", EnumCondition.NotEqual, value: EnumGender.Women)] public bool? IsWomen { get; set; } = true; /// <summary> /// 不参与查询 /// </summary> [NotQuery] public string TreeId { get; set; } /// <summary> /// 当前页 /// </summary> [NotQuery] public int Page { get; set; } = 1; /// <summary> /// 页码 /// </summary> [NotQuery] public int PageSize { get; set; } = 20; }
-