//处理排序 "@order":"name-,id"查询按 name降序、id默认顺序 排序的User数组 private void ProcessOrder(string subtable, JObject values, ISugarQueryable <ExpandoObject> tb) { if (values["@order"].IsValue()) { foreach (var item in values["@order"].ToString().Split(',')) { string col = item.Replace("-", "").Replace("+", ""); if (IsCol(subtable, col)) { if (item.EndsWith("-")) { tb.OrderBy($"{col} desc"); } else if (item.EndsWith("+")) { tb.OrderBy($"{col} asc"); } else { tb.OrderBy($"{col}"); } } } } }
public static PagedInfo <T> ToPage <T>(this ISugarQueryable <T> source, Expression <Func <T, bool> > where, PageParm parm) { source = source.Where(where); var page = new PagedInfo <T>(); var total = source.Count(); page.TotalCount = total; page.TotalPages = total / parm.PageSize; if (total % parm.PageSize > 0) { page.TotalPages++; } page.PageSize = parm.PageSize; page.PageIndex = parm.PageIndex; //排序字段新增中括号([])防止命名为数据库内置字段报错 //新增多字段排序 if (!string.IsNullOrEmpty(parm.OrderBy)) { string[] orderbys = parm.OrderBy.Split(','); string[] sorts = parm.Sort.Split(','); for (int i = 0; i < orderbys.Length; i++) { string sort = sorts.Length == orderbys.Length ? sorts[i] : "asc"; source.OrderBy($"[{orderbys[i]}] {(sort == "desc" ? "desc" : "asc")}"); } } page.DataSource = source.ToPageList(parm.PageIndex, parm.PageSize); return(page); }
/// <summary> /// 获取用户集合,包含角色id /// </summary> /// <param name="name">用户名</param> /// <param name="roleId">角色id</param> /// <param name="pageSize">页大小</param> /// <param name="pageIndex">页索引</param> /// <returns></returns> public ListResult <object> GetUsersRefRole(string name, int roleId, int pageSize, int pageIndex) { ListResult <object> result = new ListResult <object> { Result = new List <object>() }; int totalRs = 0; MenuManager MenuManager = new MenuManager(); ISugarQueryable <Enties.Users> u1 = MenuManager.Db.Queryable <Enties.Users>().WhereIF(!string.IsNullOrEmpty(name), u => SqlFunc.Contains(u.NickName, name) || SqlFunc.Contains(u.UserName, name)); ISugarQueryable <Enties.UserRole> ud1 = MenuManager.Db.Queryable <Enties.UserRole>().Where(ud => ud.RoleId == roleId); var lt = MenuManager.Db.Queryable(u1, ud1, JoinType.Left, (j1, j2) => j1.UsersId == j2.UserId).OrderBy((j1, j2) => j2.RoleId, OrderByType.Desc).Select((j1, j2) => new { j1.UsersId, j1.UserName, j1.NickName, j2.RoleId }).ToPageList(pageIndex, pageSize, ref totalRs); if (totalRs > 0) { result.Result = new List <object>(); foreach (var u in lt) { result.Result.Add(u); } } result.PageSize = pageSize; result.PageIndex = pageIndex; result.Total = totalRs; return(result); }
/// <summary> /// /// </summary> /// <param name="roleId"></param> /// <returns></returns> public async Task <ISugarQueryable <PermissionDto> > GetPermissionByRole(int roleId, string permissionType) { ISugarQueryable <PermissionDto> permissions = null; if (permissionType == SysConst.MENU) { permissions = _db.Queryable <SysRole, SysRolePermission, SysPermission, SysPermissionMenu, SysMenu>((r, rp, p, pm, m) => new object[] { JoinType.Inner, r.Id == rp.RoleId, JoinType.Inner, rp.PermissionId == p.Id, JoinType.Inner, p.Id == pm.PermissionId, JoinType.Inner, pm.MenuId == m.Id }).Where((r, rp, p, pm, m) => r.Id == roleId && p.Type == permissionType).Select((r, rp, p, pm, m) => new PermissionDto { Id = p.Id, ParentId = m.ParentId }); } else if (permissionType == SysConst.OPERATION) { permissions = _db.Queryable <SysRole, SysRolePermission, SysPermission, SysPermissionOperation, SysOperation>((r, rp, p, po, o) => new object[] { JoinType.Inner, r.Id == rp.RoleId, JoinType.Inner, rp.PermissionId == p.Id, JoinType.Inner, p.Id == po.PermissionId, JoinType.Inner, po.OperationId == o.Id }).Where((r, rp, p, po, o) => r.Id == roleId && p.Type == permissionType).Select((r, rp, p, po, o) => new PermissionDto { Id = p.Id, ParentId = o.ParentId }); } return(await Task.Run(() => permissions)); }
/// <summary> /// /// </summary> /// <param name="queryable"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static IPagedResponseDataDto <T> ToPageList <T>(this ISugarQueryable <T> queryable, int pageIndex, int pageSize) { int total = 0; var list = queryable.ToPageList(pageIndex, pageSize, ref total); return(new PagedResponseDataDto <T>(list, total, pageIndex, pageSize)); }
public async Task <string> List([FromForm] PubParams.MesTaskBootstrapParams bootstrap) { ISugarQueryable <Wms_mestask> query = _client.Queryable <Wms_mestask>(); if (!string.IsNullOrWhiteSpace(bootstrap.search)) { query = query.Where(x => x.WarehousingId.ToString().Contains(bootstrap.search) || x.BatchPlanId.Contains(bootstrap.search)); } DateTime minDate; if (!string.IsNullOrWhiteSpace(bootstrap.datemin) && DateTime.TryParse(bootstrap.datemin, out minDate)) { query = query.Where(x => x.ModifiedDate >= minDate || x.CreateDate >= minDate); } DateTime maxDate; if (!string.IsNullOrWhiteSpace(bootstrap.datemax) && DateTime.TryParse(bootstrap.datemax, out maxDate)) { query = query.Where(x => x.ModifiedDate <= maxDate || x.CreateDate <= maxDate); } query = query.Sort <Wms_mestask>(new string[] { bootstrap.sort + " " + bootstrap.order }); //Order RefAsync <int> totalCount = new RefAsync <int>(); List <Wms_mestask> result = await query.ToPageListAsync(bootstrap.pageIndex, bootstrap.limit, totalCount); return(new PageGridData(result.ToArray(), totalCount.Value).JilToJson()); }
/// <summary> /// 分页结果 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="queryable"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static async Task <IPagedResponseDataDto <T> > ToPageListAsync <T>(this ISugarQueryable <T> queryable, int pageIndex, int pageSize) { RefAsync <int> total = new RefAsync <int>(); var list = await queryable.ToPageListAsync(pageIndex, pageSize, total); return(new PagedResponseDataDto <T>(list, total.Value, pageIndex, pageSize)); }
public ActionResult AjaxList(jQueryDataTableParamModel param) { ISugarQueryable <P_CommodityType> sqable = SugarFactory.GetInstance().Queryable <P_CommodityType>(); //获取总记录数 this.TotalRecordCount = sqable.Count(); this.PageSize = 15; int allcount = this.TotalRecordCount; List <P_CommodityType> CommodityAll = sqable.ToPageList(param.iDisplayStart / param.iDisplayLength + 1, this.PageSize, ref allcount); //转化数据格式 var result = from m in CommodityAll select new[] { m.Type_Code.ToString(), m.Type_Name, }; //返回json数据 return(Json( new { sEcho = param.sEcho, iTotalRecords = this.TotalRecordCount, iTotalDisplayRecords = this.TotalRecordCount, aaData = result } )); }
/// <summary> /// 对查询的命令进行自定义的属性扩展 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sugarQueryable"></param> /// <returns></returns> public static ISugarQueryable <T> QueryableHandleAttribute <T>(this ISugarQueryable <T> sugarQueryable) where T : class, new() { /* * 这组反射一般可以拿到外边,通过传入的方式,这样可以提高数据库执行效率 * 但是放在这里,可以做到更好的自定义与扩展 */ var propIgnores = PropertyExtension.GetPropertyByAttribute <T, IgnoreAttribute>()?.Select(x => x.Name); var orderByAscs = PropertyExtension.GetPropertyByAttribute <T, OrderByAttribute>()?.Select(x => x.Name); var orderByDescs = PropertyExtension.GetPropertyByAttribute <T, OrderByDescAttribute>()?.Select(x => x.Name); //对标记忽略的字段进行过滤 if (propIgnores != null && propIgnores.Any()) { sugarQueryable = sugarQueryable.IgnoreColumns(propIgnores.ToArray()); } //按正序标记进行排序 if (orderByAscs != null && orderByAscs.Any()) { sugarQueryable = sugarQueryable.OrderBy($" {string.Join(",", orderByAscs)} asc "); } //按倒序标记进行排序 if (orderByDescs != null && orderByDescs.Any()) { sugarQueryable = sugarQueryable.OrderBy($" {string.Join(",", orderByDescs)} desc "); } return(sugarQueryable); }
/// <summary> /// 评论获得的赞 /// </summary> /// <param name="query"></param> /// <returns></returns> private ISugarQueryable <VueUserPraize> sql_UserCommentPraize(QUserPraize query) { ISugarQueryable <VueUserPraize> prSql = Db.Queryable <EPraize_Comment, EComment_Res, EBookInfo>((p, c, b) => new object[] { JoinType.Inner, p.commentId == c.Id, JoinType.Inner, c.parentRefCode == b.Code }) .Where(p => p.userId == query.userId) .OrderBy(p => p.praizeDate, OrderByType.Desc) .Select((p, c, b) => new VueUserPraize { id = p.Id, CreateDateTime = p.praizeDate, code = c.Id.ToString(), resCode = c.refCode, commentId = c.Id, bookCode = b.Code, bookName = b.Title, bookUrl = b.CoverUrl, content = c.content, }); return(prSql); }
public async Task <List <TEntity> > QueryPageAsync(ISugarQueryable <TEntity> quey, Expression <Func <TEntity, bool> > whereExpression, int intPageIndex = 0, int intPageSize = 20, string strOrderByFileds = null, bool isAsc = true) { return(await quey .WhereIF(whereExpression != null, whereExpression) .OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds) .ToPageListAsync(intPageIndex, intPageSize)); }
protected virtual void ApplyQueryFilterList(ISugarQueryable <TEntity> queryable) { if (_filters[GetFilterIndex(nameof(ISoftDelete))].IsEnabled && GetInterfacesOfTEntity().Contains(typeof(ISoftDelete))) { var dbColumnName = Context.EntityMaintenance.GetDbColumnName <TEntity>(nameof(ISoftDelete.IsDeleted)); queryable = queryable.Where($"{dbColumnName} = 0"); } }
/// <summary> /// /// </summary> /// <param name="isWhere"></param> /// <param name="expression"></param> /// <returns></returns> public IWQueryable <T> WhereIF(bool isWhere, Expression <Func <T, bool> > expression) { if (isWhere) { _queryable = _queryable.Where(expression); } return(this); }
/// <summary> /// SqlSugar page /// </summary> /// <param name="query"></param> /// <param name="currentPageNumber"></param> /// <param name="pageSize"></param> /// <param name="totalMemberCount"></param> public SqlSugarPage(ISugarQueryable <T> query, int currentPageNumber, int pageSize, int totalMemberCount) : base() { var skip = (currentPageNumber - 1) * pageSize; var state = new SqlSugarQueryState <T>(query, currentPageNumber, pageSize); InitializeMetaInfo()(currentPageNumber)(pageSize)(totalMemberCount)(skip)(); base._initializeAction = InitializeMemberList()(state)(CurrentPageSize)(skip); }
/// <summary> /// /// </summary> /// <param name="isWhere"></param> /// <param name="expression"></param> /// <returns></returns> public IWQueryable <T> WhereIF(bool isWhere, string expression) { if (isWhere) { _queryable = _queryable.Where(expression); } return(this); }
/// <summary> /// /// </summary> /// <param name="isOrderBy"></param> /// <param name="expression"></param> /// <param name="isAsc"></param> /// <returns></returns> public IWQueryable <T> OrderByIF(bool isOrderBy, Expression <Func <T, object> > expression, bool isAsc = true) { if (isOrderBy) { _queryable = _queryable.OrderBy(expression, isAsc ? OrderByType.Asc : OrderByType.Desc); } return(this); }
public static ISugarQueryable <T, T2, T3, T4, T5, T6, T7> GetQueryable <T, T2, T3, T4, T5, T6, T7>(ConnectionConfig currentConnectionConfig) { string className = "Queryable"; className = GetClassName(currentConnectionConfig.DbType.ToString(), className); ISugarQueryable <T, T2, T3, T4, T5, T6, T7> result = CreateInstance <T, T2, T3, T4, T5, T6, T7, ISugarQueryable <T, T2, T3, T4, T5, T6, T7> >(className); return(result); }
public static ISugarQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12> GetQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12>(ConnectionConfig currentConnectionConfig) { string className = "Queryable"; className = GetClassName(currentConnectionConfig.DbType, className); ISugarQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12> result = CreateInstance <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, ISugarQueryable <T, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12> >(className); return(result); }
public ISugarQueryable <T, T2, T3> Queryable <T, T2, T3>(ISugarQueryable <T> joinQueryable1, ISugarQueryable <T2> joinQueryable2, ISugarQueryable <T3> joinQueryable3, JoinType joinType1, Expression <Func <T, T2, T3, bool> > joinExpression1, JoinType joinType2, Expression <Func <T, T2, T3, bool> > joinExpression2) where T : class, new() where T2 : class, new() where T3 : class, new() { return(this.Context.Queryable(joinQueryable1, joinQueryable2, joinQueryable3, joinType1, joinExpression1, joinType2, joinExpression2).With(SqlWith.Null)); }
public static T Single <T>(this ISugarQueryable <T> queryable, Expression <Func <T, bool> > expression, T defaultValue) where T : class, new() { T result = queryable.Single(expression); if (result == null) { return(defaultValue); } return(result); }
public static T First <T>(this ISugarQueryable <T> queryable, T defaultValue) { T result = queryable.First(); if (result == null) { return(defaultValue); } return(result); }
public static T First <T>(this ISugarQueryable <T> queryable, Expression <Func <T, bool> > expression, T defaultValue) { T result = queryable.First(expression); if (result == null) { return(defaultValue); } return(result); }
public ISugarQueryable <T> GetSugarQueryable(Expression <Func <T, bool> > filter = null) { ISugarQueryable <T> query = Db.Queryable <T>(); if (filter != null) { query = query.Where(filter); } return(query); }
public static T Single <T>(this ISugarQueryable <T> queryable, T defaultValue) where T : class, new() { T result = queryable.Single(); if (result == null) { return(defaultValue); } return(result); }
public ISugarQueryable <T> Queryable <T>(ISugarQueryable <T> queryable) where T : class, new() { var result = this.Context.Queryable <T>(queryable); var QueryBuilder = queryable.QueryBuilder; result.QueryBuilder.WhereIndex = QueryBuilder.WhereIndex++; result.QueryBuilder.LambdaExpressions.ParameterIndex = QueryBuilder.LambdaExpressions.ParameterIndex++; result.QueryBuilder.LambdaExpressions.Index = QueryBuilder.LambdaExpressions.Index++; return(result); }
public ActionResult AjaxClassDetailList(jQueryDataTableParamModel param) { ISugarQueryable <P_ConsumptionInfo, P_CommodityInfo, P_MerchantInfo> sqable = SugarFactory.GetInstance().Queryable <P_ConsumptionInfo, P_CommodityInfo, P_MerchantInfo>((ct, ci, mi) => new object[] { JoinType.Left, ct.Barcode == ci.BarCode, JoinType.Left, ct.Merchant_Code == mi.Merchant_Code }); if (!String.IsNullOrEmpty(Request.Params.Get("start_time"))) { sqable.Where(ct => ct.Consumption_Time >= Convert.ToDateTime(Request.Params.Get("start_time"))); } if (!String.IsNullOrEmpty(Request.Params.Get("end_time"))) { sqable.Where(ct => ct.Consumption_Time <= Convert.ToDateTime(Request.Params.Get("end_time"))); } if (!String.IsNullOrEmpty(Request.Params.Get("chname"))) { sqable.Where(mi => mi.Merchant_Name.Contains(Request.Params.Get("chname"))); } if (!String.IsNullOrEmpty(Request.Params.Get("barcode"))) { sqable.Where(ct => ct.Barcode == Request.Params.Get("barcode")); } //获取总记录数 this.TotalRecordCount = sqable.Count(); this.PageSize = 15; int allcount = this.TotalRecordCount; List <P_ConsumptionInfo> ciAll = sqable.OrderBy(ct => ct.Consumption_Time, SqlSugar.OrderByType.Desc).Select((ct, ci, mi) => new P_ConsumptionInfo { Barcode = ct.Barcode, Consumption_Money = ct.Consumption_Money, Consumption_Time = ct.Consumption_Time, Commodity_Name = ci.Commodity_Name, Merchant_Name = mi.Merchant_Name }).ToPageList(param.iDisplayStart / param.iDisplayLength + 1, this.PageSize, ref allcount); //转化数据格式 var result = from m in ciAll select new[] { m.Id.ToString(), m.Merchant_Name, m.Barcode, m.Commodity_Name, m.Consumption_Money.ToString(), m.Consumption_Time.Value.ToString() }; //返回json数据 return(Json( new { sEcho = param.sEcho, iTotalRecords = this.TotalRecordCount, iTotalDisplayRecords = this.TotalRecordCount, aaData = result } )); }
/// <summary> /// 查询汇总条数 /// </summary> /// <typeparam name="T">表实体</typeparam> /// <param name="where">查询条件,如 "id=@id"</param> /// <param name="parameters">条件参数,如new SugarParameter("@id",1) 或 new { id = 1 }</param> /// <returns></returns> public int GetCount <T>(string where, object parameters) where T : class, new() { ISugarQueryable <T> r = _db.Queryable <T>(); if (!string.IsNullOrEmpty(where)) { r = r.Where(where, parameters); } return(r.Count()); }
/// <summary> /// 查询列表,返回分页结果 /// </summary> /// <param name="data">查询条件</param> /// <param name="pageModel">分页实体</param> /// <returns>返回结果</returns> public List <DST_PATIENT_INFO> GetPageListByCondition(DST_PATIENT_INFO data, CustomPageModel pageModel) { int totalNum = 0; int totalPage = 1; string sql = string.Format(@"SELECT M.* FROM DST_PATIENT_INFO M WHERE 1=1"); if (!string.IsNullOrEmpty(data.SLIDE_ID)) { sql += string.Format(" AND M.SLIDE_ID like '%{0}%'", data.SLIDE_ID); } if (!string.IsNullOrEmpty(data.NAME)) { sql += string.Format(" AND M.name like '%{0}%'", data.NAME); } if (data.SAMPLING_DATE != null && data.SAMPLING_DATE.Value != DateTime.MinValue) { sql += string.Format(" AND date(M.SAMPLING_DATE) = '{0}'", data.SAMPLING_DATE.Value.ToString("yyyy-MM-dd")); } if (!string.IsNullOrEmpty(data.ITEM_NAME)) { sql += string.Format(" AND M.ITEM_NAME='{0}'", data.ITEM_NAME); } if (!string.IsNullOrEmpty(data.SCAN_RESULT)) { sql += string.Format(" AND M.SCAN_RESULT='{0}'", data.SCAN_RESULT); } if (data.SCAN_DATE_TIME != null && data.SCAN_DATE_TIME.Value != DateTime.MinValue) { sql += string.Format(" AND date(M.SCAN_DATE_TIME) = '{0}'", data.SCAN_DATE_TIME.Value.ToString("yyyy-MM-dd")); } if (null != data.MinAge) { sql += string.Format(" AND M.AGE >= {0}", data.MinAge); } if (null != data.MaxAge) { sql += string.Format(" AND M.AGE <= {0}", data.MaxAge); } ISugarQueryable <DST_PATIENT_INFO> queryable = simpleClient.AsSugarClient().SqlQueryable <DST_PATIENT_INFO>(sql); List <DST_PATIENT_INFO> tmpList = queryable.ToPageList(pageModel.PageIndex, pageModel.PageSize, ref totalNum, ref totalPage); pageModel.TotalNum = totalNum; pageModel.TotalPage = totalPage; return(tmpList); }
/// <summary> /// 查询单个实体 /// </summary> /// <typeparam name="T">表实体</typeparam> /// <param name="where">查询条件</param> /// <returns></returns> public int GetCount <T>(Expression <Func <T, bool> > where) where T : class, new() { ISugarQueryable <T> r = _db.Queryable <T>(); if (where != null) { r = r.Where(where); } return(r.Count()); }
public async Task <PageModel <TEntity> > QueryPageModel(ISugarQueryable <TEntity> sugarQueryable, int intPageIndex = 1, int intPageSize = 20, string strOrderByFileds = null) { RefAsync <int> totalCount = 0; var list = await sugarQueryable.OrderByIF(!string.IsNullOrEmpty(strOrderByFileds), strOrderByFileds).ToPageListAsync(intPageIndex, intPageSize, totalCount); int pageCount = (Math.Ceiling(totalCount.ObjToDecimal() / intPageSize.ObjToDecimal())).ObjToInt(); return(new PageModel <TEntity>() { DataCount = totalCount, PageCount = pageCount, Page = intPageIndex, PageSize = intPageSize, Data = list }); }