Exemplo n.º 1
0
        /// <summary>
        /// EF单实体查询封装
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="Query">IQueryable对象</param>
        /// <param name="gridParam">过滤条件</param>
        /// <returns>查询结果</returns>
        public static PaginationResult <T> PageQuery <T>(this IQueryable <T> Query, QueryCondition gridParam)
        {
            //查询条件
            EFFilter filter = GetParameterSQL <T>(gridParam);
            var      query  = Query.Where(filter.Filter, filter.ListArgs.ToArray());
            //查询结果
            PaginationResult <T> result = new PaginationResult <T>();

            if (gridParam.IsPagination)
            {
                int PageSize  = gridParam.PageSize;
                int PageIndex = gridParam.PageIndex < 0 ? 0 : gridParam.PageIndex;
                //获取排序信息
                string sort = GetSort(gridParam, typeof(T).FullName);
                result.Data = query.OrderBy(sort).Skip(PageIndex * PageSize).Take(PageSize).ToList <T>();
                if (gridParam.IsCalcTotal)
                {
                    result.Total     = query.Count();
                    result.TotalPage = Convert.ToInt32(Math.Ceiling(result.Total * 1.0 / PageSize));
                }
                else
                {
                    result.Total = result.Data.Count();
                }
            }
            else
            {
                result.Data  = query.ToList();
                result.Total = result.Data.Count();
            }
            return(result);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 根据过滤条件统计总数
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="Query">IQueryable对象</param>
        /// <param name="gridParam">过滤条件</param>
        /// <returns>符合条件总数</returns>
        public static int Count <T>(this IQueryable <T> Query, QueryCondition gridParam)
        {
            //查询条件
            EFFilter filter = GetParameterSQL <T>(gridParam);
            var      query  = Query.Where(filter.Filter, filter.ListArgs.ToArray());

            if (gridParam.IsPagination)
            {
                if (gridParam.IsCalcTotal)
                {
                    return(query.Count());
                }
            }
            return(0);
        }
Exemplo n.º 3
0
        /// <summary>
        /// 通过查询条件,获取参数化查询SQL
        /// </summary>
        /// <param name="gridParam">过滤条件</param>
        /// <returns>过滤条件字符</returns>
        private static EFFilter GetParameterSQL <T>(QueryCondition gridParam)
        {
            EFFilter result = new EFFilter();
            //参数值集合
            List <object> listArgs = new List <object>();
            string        filter   = "1=1";

            #region "处理动态过滤条件"
            if (gridParam.FilterList != null && gridParam.FilterList.Count > 0)
            {
                StringBuilder sb = new StringBuilder();

                int      paramCount = 0;
                DateTime dateTime;
                //操作符
                string strOperator = string.Empty;
                foreach (var item in gridParam.FilterList)
                {
                    //字段名称为空则跳过
                    if (string.IsNullOrEmpty(item.FieldName))
                    {
                        continue;
                    }
                    //匹配枚举,防止SQL注入
                    Operator operatorEnum = (Operator)Enum.Parse(typeof(Operator), item.Operator, true);

                    //跳过字段值为空的
                    if (operatorEnum != Operator.Null && operatorEnum != Operator.NotNull && string.IsNullOrEmpty(item.FieldValue))
                    {
                        continue;
                    }
                    strOperator = operatorEnum.GetDescription();
                    if (item.IgnoreCase && !item.IsDateTime)
                    {
                        //2016-07-19添加查询时忽略大小写比较
                        item.FieldValue = item.FieldValue.ToLower();
                        item.FieldName  = string.Format("{0}.ToLower()", item.FieldName);
                    }
                    switch (operatorEnum)
                    {
                    //等于,不等于,小于,大于,小于等于,大于等于
                    case Operator.EQ:
                    case Operator.NE:
                    case Operator.GT:
                    case Operator.GE:
                    case Operator.LT:
                    case Operator.LE:
                        if (item.IsDateTime)
                        {
                            if (DateTime.TryParse(item.FieldValue, out dateTime))
                            {
                                if (!item.FieldValue.Contains("00:00:00") && dateTime.ToString("HH:mm:ss") == "00:00:00")
                                {
                                    if (operatorEnum == Operator.LE)
                                    {
                                        listArgs.Add(DateTime.Parse(dateTime.ToString("yyyy-MM-dd") + " 23:59:59"));
                                    }
                                    else
                                    {
                                        listArgs.Add(dateTime);
                                    }
                                }
                                else
                                {
                                    listArgs.Add(dateTime);
                                }
                                sb.AppendFormat(" AND {0} {1} @{2}", item.FieldName, strOperator, paramCount);
                            }
                        }
                        else
                        {
                            listArgs.Add(ConvertToType(item.FieldValue, GetPropType <T>(item.FieldName)));
                            sb.AppendFormat(" AND {0} {1} @{2}", item.FieldName, strOperator, paramCount);
                        }
                        paramCount++;
                        break;

                    case Operator.Like:
                    case Operator.NotLike:
                    case Operator.LLike:
                    case Operator.RLike:
                        listArgs.Add(item.FieldValue);
                        if (operatorEnum == Operator.Like)
                        {
                            sb.AppendFormat(" AND {0}.Contains(@{1})", item.FieldName, paramCount);
                        }
                        else if (operatorEnum == Operator.NotLike)
                        {
                            sb.AppendFormat(" AND !{0}.Contains(@{1})", item.FieldName, paramCount);
                        }
                        else if (operatorEnum == Operator.LLike)
                        {
                            sb.AppendFormat(" AND {0}.EndsWith(@{1})", item.FieldName, paramCount);
                        }
                        else if (operatorEnum == Operator.RLike)
                        {
                            sb.AppendFormat(" AND {0}.StartsWith(@{1})", item.FieldName, paramCount);
                        }
                        paramCount++;
                        break;

                    case Operator.Null:
                        listArgs.Add(item.FieldValue);
                        sb.AppendFormat(" AND {0}=null", item.FieldName);
                        paramCount++;
                        break;

                    case Operator.NotNull:
                        listArgs.Add(item.FieldValue);
                        sb.AppendFormat(" AND {0}!=null", item.FieldName);
                        paramCount++;
                        break;

                    case Operator.In:
                        sb.AppendFormat(" AND (");
                        foreach (var schar in item.FieldValue.Split(','))
                        {
                            listArgs.Add(schar);
                            sb.AppendFormat("{0}=@{1} or ", item.FieldName, paramCount);
                            paramCount++;
                        }
                        sb.Remove(sb.Length - 3, 3);
                        sb.AppendFormat(" )");
                        break;

                    case Operator.NotIn:
                        sb.AppendFormat(" AND (");
                        foreach (var schar in item.FieldValue.Split(','))
                        {
                            listArgs.Add(schar);
                            sb.AppendFormat("{0}!=@{1} and ", item.FieldName, paramCount);
                            paramCount++;
                        }
                        sb.Remove(sb.Length - 3, 3);
                        sb.AppendFormat(" )");
                        break;
                    }
                    if (sb.ToString().Length > 0)
                    {
                        filter = sb.ToString().Substring(4, sb.Length - 4);
                    }
                }
                #endregion
            }
            result.Filter   = filter;
            result.ListArgs = listArgs;
            return(result);
        }