public static int FilterAllFields(Type type, List <object> args, StringBuilder whereArgs, int i, string value,
string parentModel = "", bool isList = false)
{
if (whereArgs.Length > 0)
{
whereArgs.Append(" AND ");
}
var lastValid = false;
foreach (var(propertyInfo, j) in type.GetProperties().Select((v, j) => (v, j)))
{
if (!propertyInfo.GetCustomAttributes(true).Any(x => x.GetType() == typeof(JsonIgnoreAttribute)) &&
!propertyInfo.GetCustomAttributes(true).Any(x => x.GetType() == typeof(NotMappedAttribute)) &&
!propertyInfo.GetCustomAttributes(true).Where(x => x.GetType() == typeof(ColumnAttribute)).Any(attr => ((ColumnAttribute)attr).TypeName == "geography" ||
((ColumnAttribute)attr).TypeName == "jsonb") &&
!(propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(IList <>)) &&
!(propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(IEnumerable <>)) &&
!(propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(ICollection <>)) &&
!(typeof(ICollection).IsAssignableFrom(propertyInfo.PropertyType))
)
{
if (j == 0)
{
whereArgs.Append("( ");
}
var key = propertyInfo.Name;
if (!string.IsNullOrEmpty(parentModel))
{
key = $"{parentModel}.{propertyInfo.Name}";
}
if (isList)
{
key = $"{parentModel}.Any({propertyInfo.Name}";
}
var isValid = SqlCommandExtension.ConcatFilter(args, whereArgs, $"@{i}", key, value, "¬",
typeProperty: propertyInfo.PropertyType, index: j, isList: isList, isValid: lastValid);
if (isValid)
{
lastValid = true;
i++;
}
}
}
if (isList)
{
whereArgs.Append(")");
}
if (whereArgs.Length > 0)
{
whereArgs.Append(" )");
}
return(i);
}
public static async Task <IEnumerable <T> > GetPagedAsync <T>(this IQueryable <T> source, IHttpContextAccessor _httpContextAccessor,
FillDataExtensions _fillDataExtensions) where T : class
{
bool allowCache = true;
// Filter By
if (_httpContextAccessor.HttpContext.Request.Query.Any(x => x.Key.Equals("filter_by")))
{
var properties = new Dictionary <string, Type>();
foreach (var propertyInfo in typeof(T).GetProperties())
{
//Console.WriteLine($"_________________TRACEEEEEEEEEEEEEEEEE____________: key: {propertyInfo.Name} value: {propertyInfo.PropertyType.Name}");
if (!propertyInfo.GetCustomAttributes(true).Any(x => x.GetType() == typeof(JsonIgnoreAttribute)) &&
!propertyInfo.GetCustomAttributes(true).Any(x => x.GetType() == typeof(NotMappedAttribute)) &&
!(propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(IList <>)) &&
!(propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(IEnumerable <>)) &&
!(propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(ICollection <>)) &&
!(typeof(ICollection).IsAssignableFrom(propertyInfo.PropertyType))
)
{
properties.Add(propertyInfo.Name, propertyInfo.PropertyType);
}
}
var columnStr = _httpContextAccessor.HttpContext.Request.Query.FirstOrDefault(x => x.Key.Equals("filter_by")).Value.ToString();
string pattern = @"\/|\|";
string[] columns = Regex.Split(columnStr, pattern);
var divider = new List <string>();
Match match = Regex.Match(columnStr, pattern);
// query filtro por AND o OR
foreach (var(value, index) in columns.Select((v, i) => (v, i)))
{
if (index > 0)
{
match = match.NextMatch();
if (match.Success)
{
if (match.Value == "/")
{
divider.Add(" AND ");
}
else
{
divider.Add(" OR ");
}
}
}
else
{
if (match.Success)
{
if (match.Value == "/")
{
divider.Add(" AND ");
}
else
{
divider.Add(" OR ");
}
}
}
}
var expresion = new StringBuilder();
List <object> values = new List <object>();
//Procesamiento query
string dividerOld = "";
foreach (var(column, index) in columns.Select((v, i) => (v, i)))
{
allowCache = false;
if (index > 0)
{
if (index > 1 && dividerOld != divider[index - 1])
{
expresion.Append(")");
expresion.Append(divider[index - 1]);
expresion.Append("(");
}
else
{
expresion.Append(divider[index - 1]);
}
dividerOld = divider[index - 1];
}
else
{
expresion.Append("(");
}
var patternStr = @"\=|¬";
string[] value = Regex.Split(column, patternStr);
if (string.IsNullOrEmpty(value[1]))
{
break;
}
if (value[0] == "$")
{
try
{
foreach (var(field, i) in properties.Select((v, i) => (v, i)))
{
SqlCommandExtension.ConcatFilter(values, expresion, string.Format("@{0}", i + index), field.Key, value[1], column,
typeProperty: field.Value, index: i);
}
break;
}
catch (Exception)
{
throw;
}
}
var paramName = string.Format("@{0}", index);
SqlCommandExtension.ConcatFilter(values, expresion, paramName, value[0], value[1], column);
}
expresion.Append(")");
Console.WriteLine(expresion.ToString());
source = source.Where(expresion.ToString().ToLower(), values.ToArray());
}
// Order By
if (_httpContextAccessor.HttpContext.Request.Query.Any(x => x.Key.Equals("order_by")))
{
source = source.OrderBy(_httpContextAccessor.HttpContext.Request.Query.FirstOrDefault(x => x.Key.Equals("order_by")).Value.ToString());
}
// pagination
if (_httpContextAccessor.HttpContext.Request.Query.Any(x => x.Key.Equals("enable_pagination")) &&
bool.TryParse(_httpContextAccessor.HttpContext.Request.Query.FirstOrDefault(x => x.Key.Equals("enable_pagination")).Value.ToString(),
out bool enablePagination))
{
var pageSizeRequest = _httpContextAccessor.HttpContext.Request.Query.FirstOrDefault(x => x.Key.Equals("page_size")).Value;
var currentPageRequest = _httpContextAccessor.HttpContext.Request.Query.FirstOrDefault(x => x.Key.Equals("current_page")).Value;
int pageSize = string.IsNullOrEmpty(pageSizeRequest) ? 20 : int.Parse(pageSizeRequest);
int pageNumber = string.IsNullOrEmpty(currentPageRequest) ? 1 : int.Parse(currentPageRequest);
var result = new PagedResultBase();
result.current_page = pageNumber;
result.page_size = pageSize;
int?rowCount = null;
if (allowCache)
{
rowCount = source.CacheGetOrCreate(_httpContextAccessor);
}
result.row_count = rowCount ?? source.CountAsync().Result;
var pageCount = (double)result.row_count / pageSize;
result.page_count = (int)Math.Ceiling(pageCount);
_fillDataExtensions.Add($"{typeof(T).Name.ToLower()}_list", result);
//foreach (var propertyInfo in typeof(PagedResultBase).GetProperties())
//{
// var currentValue = propertyInfo.GetValue(result);
// _fillDataExtensions.Add(propertyInfo.Name, currentValue);
//}
return(await source.Skip((pageNumber - 1) *pageSize).Take(pageSize).AsNoTracking().ToListAsync());
}
return(await source.AsNoTracking().ToListAsync());
}
