public static void ConfigureFilterCommand(ObjectDef m, string schemaName, SqlCommand cmd, Condition cond, string orderByExpression, int pageNumber, int pageSize) { cmd.CommandType = CommandType.Text; string entityName = m.EntityName; StringBuilder sb = new StringBuilder(); StringBuilder sbColumns = new StringBuilder(); var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Read)).OrderBy(p => p.PropertyOrder); //.Where(p => p.AllowRead).OrderBy(p => p.PropertyOrder); for (int i = 0; i < q.Count(); i++) { ObjectDefProperty mp = q.ElementAt(i); var entityPropName = mp.ColumnName; if (i == 0) { sbColumns.AppendLine(string.Format("\t[{0}]", entityPropName)); } else { sbColumns.AppendLine(string.Format("\t, [{0}]", entityPropName)); } } string columns = sbColumns.ToString(); string filters = string.Empty; if (cond != null) { string condString = cond.ToSqlString(true); if (!string.IsNullOrEmpty(condString)) { filters = string.Format("WHERE {0}", condString); cmd.Parameters.AddRange(cond.GetSqlParameters().ToArray()); } } sb.AppendLine($"SELECT {TotalCountParamName} = COUNT(*)"); sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName)); sb.AppendLine(filters); sb.AppendLine(string.Format(";WITH {0}CTE AS", entityName)); sb.AppendLine("("); sb.AppendLine("\tSELECT"); sb.AppendLine(columns); var orderByCols = GetOrderByCols(q); if (!string.IsNullOrEmpty(orderByExpression)) { sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByExpression)); } else { if (!string.IsNullOrEmpty(orderByCols)) { sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByCols)); } else { var key = m.GetKey(); sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", key.Name)); } } sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName)); sb.AppendLine(filters); sb.AppendLine(")"); sb.AppendLine($"SELECT TOP({PageSizeParamName})"); sb.AppendLine(columns); sb.AppendLine(string.Format("FROM {0}CTE", entityName)); sb.AppendLine($"WHERE RowNumber > ({PageNumberParamName} - 1) * {PageSizeParamName}"); cmd.Parameters.AddWithValue(PageNumberParamName, pageNumber); cmd.Parameters.AddWithValue(PageSizeParamName, pageSize); SqlParameter param = new SqlParameter(TotalCountParamName, SqlDbType.Int); param.Direction = ParameterDirection.InputOutput; param.Value = 0; cmd.Parameters.Add(param); cmd.CommandText = sb.ToString(); }