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); }