public static string StringFilter(string terms, string columnName, DataTablePropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (terms == ".*")
{
return("");
}
string parameterArg;
if (terms.StartsWith("^"))
{
if (terms.EndsWith("$"))
{
parametersForLinqQuery.Add(terms.Substring(1, terms.Length - 2));
parameterArg = $"@{parametersForLinqQuery.Count - 1}";
return($"{columnName} == {parameterArg}");
}
parametersForLinqQuery.Add(terms.Substring(1));
parameterArg = "@" + (parametersForLinqQuery.Count - 1);
return
($"({columnName} != {DataConst.Null} && {columnName} != \"\" && ({columnName} == {parameterArg} || {columnName}.{ConditionalCost.StartsWith}({parameterArg})))");
}
parametersForLinqQuery.Add(terms);
parameterArg = "@" + (parametersForLinqQuery.Count - 1);
return
($"({columnName} != {DataConst.Null} && {columnName} != \"\" && ({columnName} == {parameterArg} || {columnName}.{ConditionalCost.StartsWith}({parameterArg}) || {columnName}.{ConditionalCost.Contain}({parameterArg})))");
}
/// <summary>
/// Filter Enum by Label (Display Name ?? Description ?? Name) with conditional Equals,
/// StartsWith, Contains
/// </summary>
/// <param name="terms"> </param>
/// <param name="columnName"> </param>
/// <param name="propertyInfo"> </param>
/// <param name="parametersForLinqQuery"></param>
/// <returns></returns>
/// <remarks>
/// terms is "null" with Type is Nullable Enum work as search null value
/// </remarks>
public static string EnumFilter(string terms, string columnName, DataTablePropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (terms.StartsWith("^"))
{
terms = terms.Substring(1);
}
if (terms.EndsWith("$"))
{
terms = terms.Substring(0, terms.Length - 1);
}
if (propertyInfo.Type.IsNullableEnumType())
{
// Enum Nullable type, handle for "null" case ("null" string as null obj)
if (DataConst.Null.Equals(terms, StringComparison.OrdinalIgnoreCase) || string.IsNullOrWhiteSpace(terms))
{
return($"{columnName} == {DataConst.Null}");
}
}
else
{
if (string.IsNullOrWhiteSpace(terms))
{
return(null);
}
}
Type type = propertyInfo.Type.GetNotNullableType();
object enumObject = null;
string termsLowerCase = terms.ToLowerInvariant();
// Search condition for Enum: Equals, StartsWith, Contains
foreach (string enumName in Enum.GetNames(type))
{
Enum enumValue = (Enum)enumName.ParseTo(type);
var valueLowerCase = enumName.ToLowerInvariant();
if (valueLowerCase.Equals(termsLowerCase, StringComparison.OrdinalIgnoreCase) || valueLowerCase.StartsWith(termsLowerCase) || valueLowerCase.Contains(termsLowerCase))
{
enumObject = enumValue;
// Found, return first found item
break;
}
}
// Can't parse string to enum, return null
if (enumObject == null)
{
return(null);
}
parametersForLinqQuery.Add(enumObject);
return($"{columnName} == @{parametersForLinqQuery.Count - 1}");
}
internal static object ChangeType(string terms, DataTablePropertyInfo propertyInfo)
{
if (propertyInfo.PropertyInfo.PropertyType.GetTypeInfo().IsGenericType&&
propertyInfo.Type.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var u = Nullable.GetUnderlyingType(propertyInfo.Type);
return(Convert.ChangeType(terms, u));
}
return(Convert.ChangeType(terms, propertyInfo.Type));
}
public static string NumericFilter(string terms, string columnName, DataTablePropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (terms.StartsWith("^"))
{
terms = terms.TrimStart('^');
}
if (terms.EndsWith("$"))
{
terms = terms.TrimEnd('$');
}
if (terms == "~")
{
return(string.Empty);
}
string clause = null;
if (terms.Contains("~"))
{
var parts = terms.Split('~');
try
{
parametersForLinqQuery.Add(ChangeType(parts[0], propertyInfo));
clause = $"{columnName} >= @{parametersForLinqQuery.Count - 1}";
}
catch (FormatException)
{
}
try
{
parametersForLinqQuery.Add(ChangeType(parts[1], propertyInfo));
if (clause != null)
{
clause += " and ";
}
clause += $"{columnName} <= @{parametersForLinqQuery.Count - 1}";
}
catch (FormatException)
{
}
return(clause ?? "true");
}
try
{
parametersForLinqQuery.Add(ChangeType(terms, propertyInfo));
return($"{columnName} == @{parametersForLinqQuery.Count - 1}");
}
catch (FormatException)
{
}
return(null);
}
private static string GetFilterClause(string query, DataTablePropertyInfo column, List <object> parametersForLinqQuery)
{
string Clause(string queryPart) => Filters
.Select(f => f(queryPart, column.PropertyInfo.Name, column, parametersForLinqQuery))
.FirstOrDefault(filterPart => filterPart != null) ?? string.Empty;
var queryParts = query.Split('|').Select(Clause).Where(clause => clause != string.Empty).ToArray();
if (queryParts.Any())
{
return("(" + string.Join(") OR (", queryParts) + ")");
}
return(null);
}
public static string BoolFilter(string terms, string columnName, DataTablePropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
terms = terms?.TrimStart('^').TrimEnd('$');
var termsLowerCase = terms?.ToLowerInvariant();
if (termsLowerCase == DataConst.FalseLower || termsLowerCase == DataConst.TrueLower)
{
return(terms.ToLower() == DataConst.TrueLower
? $"{columnName} == {DataConst.TrueLower}"
: $"{columnName} == {DataConst.FalseLower}");
}
if (propertyInfo.Type != typeof(bool?))
{
return(null);
}
return(DataConst.Null.Equals(terms, StringComparison.CurrentCultureIgnoreCase)
? $"{columnName} == {DataConst.Null}"
: null);
}
public static string DateTimeOffsetFilter(string terms, string columnName, DataTablePropertyInfo propertyInfo, List <object> parametersForLinqQuery)
{
if (terms == "~")
{
return(string.Empty);
}
string filterString = null;
// Range Case
if (terms.Contains("~"))
{
var parts = terms.Split('~');
// FROM DATE TIME
DateTimeOffset?fromDateTime = ToDateTime(parts[0]);
if (fromDateTime != null)
{
parametersForLinqQuery.Add(fromDateTime.Value);
filterString = $"{columnName} >= @{parametersForLinqQuery.Count - 1}";
}
// TO DATE TIME
DateTimeOffset?toDateTime = ToDateTime(parts[1]);
if (toDateTime == null)
{
return(filterString ?? string.Empty);
}
parametersForLinqQuery.Add(toDateTime.Value);
filterString = (filterString == null ? null : $"{filterString} and ") + $"{columnName} <= @{parametersForLinqQuery.Count - 1}";
return(filterString);
}
// Single Case
DateTimeOffset?dateTime = ToDateTime(terms);
if (dateTime == null)
{
return(null);
}
// DateTime only have Date value => search value in same Date
if (dateTime.Value.Date == dateTime.Value)
{
parametersForLinqQuery.Add(dateTime.Value);
parametersForLinqQuery.Add(dateTime.Value.AddDays(1));
filterString = $"{columnName} >= @{parametersForLinqQuery.Count - 2} and {columnName} < @{parametersForLinqQuery.Count - 1}";
return(filterString);
}
// DateTime have Date and Time value => search value in same Date and Time.
parametersForLinqQuery.Add(dateTime);
// If you store DateTime in database include milliseconds => no result match. Ex: in
// Database "2017-10-16 10:51:09.9761005 +00:00" so user search "2017-10-16 10:51" will
// return 0 result, because not exactly same (even user give full datetime with
// milliseconds exactly - this is Linq2SQL issue).
filterString = $"{columnName} == @{parametersForLinqQuery.Count - 1}";
return(filterString);
}
private static bool IsDateTimeOffsetType(DataTablePropertyInfo propertyInfo)
{
bool isDateTimeOffsetType = propertyInfo.Type == typeof(DateTimeOffset) || propertyInfo.Type == typeof(DateTimeOffset?);
return(isDateTimeOffsetType);
}
private static bool IsBoolType(DataTablePropertyInfo propertyInfo)
{
bool isBoolType = propertyInfo.Type == typeof(bool) || propertyInfo.Type == typeof(bool?);
return(isBoolType);
}
private static bool IsEnumType(DataTablePropertyInfo propertyInfo)
{
bool isEnumType = propertyInfo.Type.IsEnumType() || propertyInfo.Type.IsNullableEnumType();
return(isEnumType);
}
public IQueryable <T> ApplyFiltersAndSort <T>(DataTableParamModel dtParameters, IQueryable <T> data, DataTablePropertyInfo[] columns)
{
if (!string.IsNullOrEmpty(dtParameters.Search))
{
var parts = new List <string>();
var parameters = new List <object>();
for (var i = 0; i < dtParameters.Columns; i++)
{
if (dtParameters.ListIsSearchable[i])
{
try
{
parts.Add(GetFilterClause(dtParameters.Search, columns[i], parameters));
}
catch (Exception)
{
// if the clause doesn't work, skip it!
// ex: can't parse a string to enum or datetime type
}
}
}
var values = parts.Where(p => p != null);
data = data.Where(string.Join(" or ", values), parameters.ToArray());
}
for (int i = 0; i < dtParameters.SearchValues.Count; i++)
{
if (dtParameters.ListIsSearchable[i])
{
var searchColumn = dtParameters.SearchValues[i];
if (!string.IsNullOrWhiteSpace(searchColumn))
{
DataTablePropertyInfo column = FindColumn(dtParameters, columns, i);
var parameters = new List <object>();
var filterClause = GetFilterClause(searchColumn, column, parameters);
if (!string.IsNullOrWhiteSpace(filterClause))
{
data = data.Where(filterClause, parameters.ToArray());
}
}
}
}
string sortString = "";
for (int i = 0; i < dtParameters.SortingCols; i++)
{
int columnNumber = dtParameters.SortCol[i];
DataTablePropertyInfo column = FindColumn(dtParameters, columns, columnNumber);
string columnName = column.PropertyInfo.Name;
string sortDir = dtParameters.SortDir[i];
if (i != 0)
{
sortString += ", ";
}
sortString += columnName + " " + sortDir;
}
if (string.IsNullOrWhiteSpace(sortString))
{
sortString = columns[0].PropertyInfo.Name;
}
data = data.OrderBy(sortString);
return(data);
}
private static bool IsNumericType(DataTablePropertyInfo propertyInfo)
{
bool isNumericType = propertyInfo.Type.IsNumericType();
return(isNumericType);
}