public static string BoolFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (query != null)
{
query = query.TrimStart('^').TrimEnd('$');
}
var lowerCaseQuery = query.ToLowerInvariant();
if (lowerCaseQuery == "false" || lowerCaseQuery == "true")
{
if (query.ToLower() == "true")
{
return(columnname + " == true");
}
return(columnname + " == false");
}
if (propertyInfo.Type == typeof(bool?))
{
if (lowerCaseQuery == "null")
{
return(columnname + " == null");
}
}
return(null);
}
public static string StringFilter(string q, string columnname, DataTablesPropertyInfo columntype, List <object> parametersforlinqquery)
{
if (q == ".*")
{
return("");
}
if (q.StartsWith("^"))
{
if (q.EndsWith("$"))
{
parametersforlinqquery.Add(q.Substring(1, q.Length - 2));
var parameterArg = "@" + (parametersforlinqquery.Count - 1);
return(string.Format("{0} == {1}", columnname, parameterArg));
}
else
{
parametersforlinqquery.Add(q.Substring(1));
var parameterArg = "@" + (parametersforlinqquery.Count - 1);
return(string.Format("({0} != null && {0} != \"\" && ({0} == {1} || {0}.StartsWith({1})))", columnname, parameterArg));
}
}
else
{
parametersforlinqquery.Add(q);
var parameterArg = "@" + (parametersforlinqquery.Count - 1);
//return string.Format("{0} == {1}", columnname, parameterArg);
return
(string.Format(
"({0} != null && {0} != \"\" && ({0} == {1} || {0}.StartsWith({1}) || {0}.Contains({1})))",
columnname, parameterArg));
}
}
public IQueryable<T> ApplyFiltersAndSort<T>(DataTablesParam dtParameters, IQueryable<T> data, DataTablesPropertyInfo[] columns)
{
if (!String.IsNullOrEmpty(dtParameters.sSearch))
{
var parts = new List<string>();
var parameters = new List<object>();
for (var i = 0; i < dtParameters.iColumns; i++)
{
if (dtParameters.bSearchable[i])
{
try
{
parts.Add(GetFilterClause(dtParameters.sSearch, columns[i], parameters));
}
catch (Exception)
{
//if the clause doesn't work, skip it!
}
}
}
var values = parts.Where(p => p != null);
data = data.Where(string.Join(" or ", values), parameters.ToArray());
}
for (int i = 0; i < dtParameters.sSearchValues.Count; i++)
{
if (dtParameters.bSearchable[i])
{
var searchColumn = dtParameters.sSearchValues[i];
if (!string.IsNullOrWhiteSpace(searchColumn))
{
DataTablesPropertyInfo column = FindColumn(dtParameters, columns, i);
var parameters = new List<object>();
var filterClause = GetFilterClause(searchColumn, column, parameters);
if (string.IsNullOrWhiteSpace(filterClause) == false)
{
data = data.Where(filterClause, parameters.ToArray());
}
}
}
}
string sortString = "";
for (int i = 0; i < dtParameters.iSortingCols; i++)
{
int columnNumber = dtParameters.iSortCol[i];
DataTablesPropertyInfo column = FindColumn(dtParameters, columns, columnNumber);
string columnName = column.PropertyInfo.Name;
string sortDir = dtParameters.sSortDir[i];
if (i != 0)
sortString += ", ";
sortString += columnName + " " + sortDir;
}
if (string.IsNullOrWhiteSpace(sortString))
{
sortString = columns[0].PropertyInfo.Name;
}
data = data.OrderBy(sortString);
return data;
}
public static string DateTimeFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (query == "~")
{
return(string.Empty);
}
if (query.Contains("~"))
{
var parts = query.Split('~');
var filterString = null as string;
DateTime start, end;
if (DateTime.TryParse(parts[0] ?? "", out start))
{
filterString = columnname + " >= @" + parametersForLinqQuery.Count;
parametersForLinqQuery.Add(start);
}
if (DateTime.TryParse(parts[1] ?? "", out end))
{
filterString = (filterString == null ? null : filterString + " and ") + columnname + " <= @" + parametersForLinqQuery.Count;
parametersForLinqQuery.Add(end);
}
return(filterString ?? "");
}
else
{
return(string.Format("{1}.ToString(\"u\").{0}", FilterMethod(query, parametersForLinqQuery, propertyInfo.Type), columnname));
}
}
public static string NumericFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo,
List <object> parametersForLinqQuery)
{
if (query.StartsWith("^"))
{
query = query.TrimStart('^');
}
if (query.EndsWith("$"))
{
query = query.TrimEnd('$');
}
if (query == "~")
{
return(string.Empty);
}
if (query.Contains("~"))
{
var parts = query.Split('~');
var clause = null as string;
try
{
parametersForLinqQuery.Add(ChangeType(propertyInfo, parts[0]));
clause = string.Format("{0} >= @{1}", columnname, parametersForLinqQuery.Count - 1);
}
catch (FormatException)
{
}
try
{
parametersForLinqQuery.Add(ChangeType(propertyInfo, parts[1]));
if (clause != null)
{
clause += " and ";
}
clause += string.Format("{0} <= @{1}", columnname, parametersForLinqQuery.Count - 1);
}
catch (FormatException)
{
}
return(clause ?? "true");
}
else
{
try
{
parametersForLinqQuery.Add(ChangeType(propertyInfo, query));
return(string.Format("{0} == @{1}", columnname, parametersForLinqQuery.Count - 1));
}
catch (FormatException)
{
}
return(null);
}
}
public static string DateTimeFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (query == "~")
{
return(string.Empty);
}
var filterString = null as string;
if (query.Contains("~"))
{
var parts = query.Split('~');
DateTime start, end;
if (DateTime.TryParse(parts[0] ?? "", out start))
{
start = start.ToUniversalTime();
filterString = columnname + " >= @" + parametersForLinqQuery.Count;
parametersForLinqQuery.Add(start);
}
if (DateTime.TryParse(parts[1] ?? "", out end))
{
if (end == end.Date)
{
end = end.AddHours(23).AddMinutes(59).AddSeconds(59);
}
end = end.ToUniversalTime();
filterString = (filterString == null ? null : filterString + " and ") + columnname + " <= @" + parametersForLinqQuery.Count;
parametersForLinqQuery.Add(end);
}
return(filterString ?? "");
}
else
{
DateTime dateTime;
if (DateTime.TryParse(query, out dateTime))
{
if (dateTime.Date == dateTime)
{
dateTime = dateTime.ToUniversalTime();
parametersForLinqQuery.Add(dateTime);
parametersForLinqQuery.Add(dateTime.AddDays(1));
filterString = string.Format("({0} >= @{1} and {0} < @{2})", columnname, parametersForLinqQuery.Count - 2, parametersForLinqQuery.Count - 1);
}
else
{
dateTime = dateTime.ToUniversalTime();
filterString = string.Format("{0} == @" + parametersForLinqQuery.Count, columnname);
parametersForLinqQuery.Add(dateTime);
}
}
return(filterString);
}
}
private static object ChangeType(DataTablesPropertyInfo propertyInfo, string query)
{
if (propertyInfo.PropertyInfo.PropertyType.GetTypeInfo().IsGenericType&& propertyInfo.Type.GetGenericTypeDefinition() == typeof(Nullable <>))
{
Type u = Nullable.GetUnderlyingType(propertyInfo.Type);
return(Convert.ChangeType(query, u));
}
else
{
return(Convert.ChangeType(query, propertyInfo.Type));
}
}
public static string EnumFilter(string q, string columnname, DataTablesPropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (q.StartsWith("^"))
{
q = q.Substring(1);
}
if (q.EndsWith("$"))
{
q = q.Substring(0, q.Length - 1);
}
parametersForLinqQuery.Add(ParseValue(q, propertyInfo.Type));
return(columnname + " == @" + (parametersForLinqQuery.Count - 1));
}
private static string GetFilterClause(string query, DataTablesPropertyInfo column, List<object> parametersForLinqQuery)
{
Func<string, string> filterClause = (queryPart) =>
Filters.Select(
f => f(queryPart, column.PropertyInfo.Name, column, parametersForLinqQuery))
.FirstOrDefault(filterPart => filterPart != null) ?? "";
var queryParts = query.Split('|').Select(filterClause).Where(fc => fc != "").ToArray();
if (queryParts.Any())
{
return "(" + string.Join(") OR (", queryParts) + ")";
}
return null;
}
public static string NumericFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo, List<object> parametersForLinqQuery)
{
if (query.StartsWith("^")) query = query.TrimStart('^');
if (query.EndsWith("$")) query = query.TrimEnd('$');
if (query == "~") return string.Empty;
if (query.Contains("~"))
{
var parts = query.Split('~');
var clause = null as string;
try
{
parametersForLinqQuery.Add(ChangeType(propertyInfo, parts[0]));
clause = string.Format("{0} >= @{1}", columnname, parametersForLinqQuery.Count - 1);
}
catch (FormatException)
{
}
try
{
parametersForLinqQuery.Add(ChangeType(propertyInfo, parts[1]));
if (clause != null) clause += " and ";
clause += string.Format("{0} <= @{1}", columnname, parametersForLinqQuery.Count - 1);
}
catch (FormatException)
{
}
return clause ?? "true";
}
else
{
try
{
parametersForLinqQuery.Add(ChangeType(propertyInfo, query));
return string.Format("{0} == @{1}", columnname, parametersForLinqQuery.Count - 1);
}
catch (FormatException)
{
}
return null;
}
}
public static string EnumFilter(string q, string columnname, DataTablesPropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (q.StartsWith("^"))
{
q = q.Substring(1);
}
if (q.EndsWith("$"))
{
q = q.Substring(0, q.Length - 1);
}
var values = Enum.GetValues(propertyInfo.Type);
foreach (Enum val in values)
{
var displayName = val.GetDisplayName().ToLower();
if (displayName.StartsWith(q.ToLower()))
{
parametersForLinqQuery.Add(val);
return(columnname + " == @" + (parametersForLinqQuery.Count - 1));
}
}
return(null);
}
public static string StringFilter(string q, string columnname, DataTablesPropertyInfo columntype, List<object> parametersforlinqquery)
{
if (q == ".*") return "";
if (q.StartsWith("^"))
{
if (q.EndsWith("$"))
{
parametersforlinqquery.Add(q.Substring(1, q.Length - 2));
var parameterArg = "@" + (parametersforlinqquery.Count - 1);
return string.Format("{0} == {1}", columnname, parameterArg);
}
else
{
parametersforlinqquery.Add(q.Substring(1));
var parameterArg = "@" + (parametersforlinqquery.Count - 1);
return string.Format("({0} != null && {0} != \"\" && ({0} == {1} || {0}.StartsWith({1})))", columnname, parameterArg);
}
}
else
{
parametersforlinqquery.Add(q);
var parameterArg = "@" + (parametersforlinqquery.Count - 1);
//return string.Format("{0} == {1}", columnname, parameterArg);
return
string.Format(
"({0} != null && {0} != \"\" && ({0} == {1} || {0}.StartsWith({1}) || {0}.Contains({1})))",
columnname, parameterArg);
}
}
public static bool IsEnumType(DataTablesPropertyInfo propertyInfo)
{
return propertyInfo.Type.GetTypeInfo().IsEnum;
}
public static bool IsDateTimeOffsetType(DataTablesPropertyInfo propertyInfo)
{
return(propertyInfo.Type == typeof(DateTimeOffset) || propertyInfo.Type == typeof(DateTimeOffset?));
}
public static bool IsBoolType(DataTablesPropertyInfo propertyInfo)
{
return(propertyInfo.Type == typeof(bool) || propertyInfo.Type == typeof(bool?));
}
public static bool IsEnumType(DataTablesPropertyInfo propertyInfo)
{
return(propertyInfo.Type.IsEnum);
}
public static bool IsNumericType(DataTablesPropertyInfo propertyInfo)
{
var type = propertyInfo.Type;
return(IsNumericType(type));
}
private static object ChangeType(DataTablesPropertyInfo propertyInfo, string query)
{
if (propertyInfo.PropertyInfo.PropertyType.IsGenericType && propertyInfo.Type.GetGenericTypeDefinition() == typeof(Nullable<>))
{
Type u = Nullable.GetUnderlyingType(propertyInfo.Type);
return Convert.ChangeType(query, u);
}
else
{
return Convert.ChangeType(query, propertyInfo.Type);
}
}
public static string EnumFilter(string q, string columnname, DataTablesPropertyInfo propertyInfo, List<object> parametersForLinqQuery)
{
if (q.StartsWith("^")) q = q.Substring(1);
if (q.EndsWith("$")) q = q.Substring(0, q.Length - 1);
parametersForLinqQuery.Add(ParseValue(q, propertyInfo.Type));
return columnname + " == @" + (parametersForLinqQuery.Count - 1);
}
public static bool IsDateTimeType(DataTablesPropertyInfo propertyInfo)
{
return propertyInfo.Type == typeof(DateTime) || propertyInfo.Type == typeof(DateTime?);
}
public static string BoolFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo, List<object> parametersForLinqQuery)
{
if (query != null)
query = query.TrimStart('^').TrimEnd('$');
var lowerCaseQuery = query.ToLowerInvariant();
if (lowerCaseQuery == "false" || lowerCaseQuery == "true")
{
if (query.ToLower() == "true") return columnname + " == true";
return columnname + " == false";
}
if (propertyInfo.Type == typeof(bool?))
{
if (lowerCaseQuery == "null") return columnname + " == null";
}
return null;
}
public static string DateTimeFilter(string query, string columnname, DataTablesPropertyInfo propertyInfo, List<object> parametersForLinqQuery)
{
if (query == "~") return string.Empty;
if (query.Contains("~"))
{
var parts = query.Split('~');
var filterString = null as string;
DateTime start, end;
if (DateTime.TryParse(parts[0] ?? "", out start))
{
filterString = columnname + " >= @" + parametersForLinqQuery.Count;
parametersForLinqQuery.Add(start);
}
if (DateTime.TryParse(parts[1] ?? "", out end))
{
filterString = (filterString == null ? null : filterString + " and ") + columnname + " <= @" + parametersForLinqQuery.Count;
parametersForLinqQuery.Add(end);
}
return filterString ?? "";
}
else
{
return string.Format("{1}.ToLocalTime().ToString(\"g\").{0}", FilterMethod(query, parametersForLinqQuery, propertyInfo.Type), columnname);
}
}
internal static string StringListFilter(string query, string columnName, DataTablesPropertyInfo columnType, List<object> parametersForLinqQuery)
{
var filterString = $"@{parametersForLinqQuery.Count}";
parametersForLinqQuery.Add(query);
return filterString;
}
