public static string FilterCondition <TEntity>(this DbCommand command, RelationalDbType relationalDbType, IEnumerable <SearchyCondition> conditions = null)
{
if (conditions == null)
{
return(string.Empty);
}
var where = string.Empty;
int index = 0;
bool validFiltersFound = false;
foreach (var condition in conditions)
{
if (where == string.Empty)
{
where = " WHERE (";
}
else
{
where = $"{where} or ( ";
}
foreach (var filter in condition.Filters.Where(i => i.IsValid()))
{
validFiltersFound = true;
index += 1;
var filterColName = ColumnAttribute.Get <TEntity>(filter.Field).ColumnNameEscaped(relationalDbType);
var filterColumnParameter = ColumnAttribute.Get <TEntity>(filter.Field).ColumnName;
var parameter = command.AddParameter(filterColumnParameter + index.ToString());
switch (filter.Rule)
{
case SearchyRule.EqualsTo:
where = $"{where} ({filterColName}={parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.LessThan:
where = $"{where} ({filterColName}<{parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.LessThanOrEquals:
where = $"{where} ({filterColName}<={parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.GreaterThan:
where = $"{where} ({filterColName}>{parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.GreaterThanOrEquals:
where = $"{where} ({filterColName}>={parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.NotEqualsTo:
where = $"{where} ({filterColName}<>{parameter.ParameterName}) AND ";
parameter.Value = filter.Value;
break;
case SearchyRule.StartsWith:
where = $"{where} ({filterColName} like {parameter.ParameterName}) AND ";
parameter.Value = string.Concat(filter.Value, "%");
break;
case SearchyRule.Contains:
where = $"{where} ({filterColName} like {parameter.ParameterName}) AND ";
parameter.Value = string.Concat("%", filter.Value, "%");
break;
//case SearchyRule.EqualsToList:
// {
// var _ListType = _e.Value.GetType();
// var _ItemType = _ListType.GetGenericArguments();
// var _GenericListType = typeof(List<>);
// var _GenericList = _GenericListType.MakeGenericType(_ItemType);
// if (_GenericList != _ListType)
// throw new Exception(string.Format("The value for the filter {0} is not a generic list", _filtercolname));
// var _Values = new StringBuilder();
// if (_ItemType.Contains(Type.GetType("System.String")) || _ItemType.Contains(Type.GetType("System.Guid")))
// {
// foreach (var _Value in _e.Value)
// _Values.Append(string.Concat("'", _Value.ToString().Replace("'", "''"), "'", ","));
// }
// else
// foreach (var _Value in _e.Value)
// _Values.Append(string.Concat(_Value, ","));
// _whereclause = string.Format(_whereclause + " ({0} IN ({1})) AND ", _filtercolname, _Values.ToString().TrimEnd(new char[] { ',' }));
// break;
// }
}
}
where = where.Remove(where.Length - 5);
where = $"{where})";
}
return(validFiltersFound ? where : string.Empty);
}
public string ColumnNameEscaped(RelationalDbType relationalDbType) =>
relationalDbType == RelationalDbType.MySql ? @$ "`{ColumnName}`" : @$ "" "{ColumnName}" "";
