/// <summary> /// Converts the filter operator into the <see cref="Csg.Data.Sql.SqlOperator"/> equivalent /// </summary> /// <param name="operator"></param> /// <returns></returns> public static Csg.Data.Sql.SqlOperator ToSqlOperator(this ListFilterOperator @operator) { Csg.Data.Sql.SqlOperator nativeOperator; switch (@operator) { case ListFilterOperator.Equal: nativeOperator = Csg.Data.Sql.SqlOperator.Equal; break; case ListFilterOperator.GreaterThan: nativeOperator = Csg.Data.Sql.SqlOperator.GreaterThan; break; case ListFilterOperator.GreaterThanOrEqual: nativeOperator = Csg.Data.Sql.SqlOperator.GreaterThanOrEqual; break; case ListFilterOperator.LessThan: nativeOperator = Csg.Data.Sql.SqlOperator.LessThan; break; case ListFilterOperator.LessThanOrEqual: nativeOperator = Csg.Data.Sql.SqlOperator.LessThanOrEqual; break; case ListFilterOperator.NotEqual: nativeOperator = Csg.Data.Sql.SqlOperator.NotEqual; break; default: throw new NotSupportedException($"An unsupported filter operator '{@operator}' was encountered"); } return(nativeOperator); }
/// <summary> /// Creates a filter using the given generic filter operator /// </summary> /// <param name="where"></param> /// <param name="columnName"></param> /// <param name="operator"></param> /// <param name="value"></param> /// <param name="valueType"></param> /// <param name="valueTypeSize"></param> /// <param name="stringMatchType"></param> /// <param name="performDataTypeConversion"></param> /// <param name="valueConverter"></param> public static void AddFilter(this Csg.Data.IDbQueryWhereClause where, string columnName, ListFilterOperator @operator, object value, System.Data.DbType valueType, int?valueTypeSize = null, SqlWildcardDecoration stringMatchType = SqlWildcardDecoration.BeginsWith, bool performDataTypeConversion = true, Func <object, object> valueConverter = null) { var root = where.Root; IEnumerable <object> values = ValueHelpers.GetFilterValues(value, valueType, shouldPerformDataTypeConversion: performDataTypeConversion, valueConverter: valueConverter); bool isMutliValued = values != null && values.Count() > 1; var firstValue = values.Count() > 0 ? values.First() : null; var hasValues = values != null && values.Count() > 0; var dataType = valueType; int? dataTypeSize = valueTypeSize; if (!hasValues) { return; } //handle LIKE or EQUALS filtering against nchar and char data types if (dataType == DbType.AnsiStringFixedLength) { dataType = DbType.AnsiString; dataTypeSize = null; } else if (dataType == DbType.StringFixedLength) { dataType = DbType.String; dataTypeSize = null; } if (@operator == ListFilterOperator.Like) { if (isMutliValued) { var filterCollection = new Csg.Data.Sql.SqlFilterCollection(); filterCollection.Logic = Csg.Data.Sql.SqlLogic.Or; foreach (var filterValue in values) { filterCollection.Add(new Csg.Data.Sql.SqlStringMatchFilter(root, columnName, stringMatchType, filterValue.ToString()) { DataType = dataType, Size = dataTypeSize }); } where.AddFilter(filterCollection); } else if (firstValue == null) { where.AddFilter(new Csg.Data.Sql.SqlNullFilter(root, columnName, true)); } else { where.AddFilter(new Csg.Data.Sql.SqlStringMatchFilter(root, columnName, stringMatchType, firstValue.ToString()) { DataType = dataType, Size = dataTypeSize }); } } else if (@operator == ListFilterOperator.Between) { if (!isMutliValued || values.Count() != 2) { throw new Exception(string.Format("Error while processing the filter values for column '{0}': A between filter must have exactly two values.", columnName)); } var valueArray = values.ToArray(); if (valueArray[0] != null) { where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(root, columnName, Csg.Data.Sql.SqlOperator.GreaterThanOrEqual, dataType, valueArray[0]) { Size = dataTypeSize }); } if (valueArray[1] != null) { where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(root, columnName, Csg.Data.Sql.SqlOperator.LessThanOrEqual, dataType, valueArray[1]) { Size = dataTypeSize }); } } else if (@operator == ListFilterOperator.IsNull) { var filterCollection = new Csg.Data.Sql.SqlFilterCollection(); filterCollection.Logic = Csg.Data.Sql.SqlLogic.Or; foreach (var filterValue in values) { filterCollection.Add(new Csg.Data.Sql.SqlNullFilter(root, columnName, Convert.ToBoolean(filterValue)) { DataType = dataType }); } where.AddFilter(filterCollection); } else { var nativeOperator = @operator.ToSqlOperator(); // Handle filtering on a list for exact matches if (isMutliValued && nativeOperator == Csg.Data.Sql.SqlOperator.Equal) { where.AddFilter(new Csg.Data.Sql.SqlListFilter(root, columnName, dataType, values) { Size = dataTypeSize }); } // or not-in else if (isMutliValued && nativeOperator == Csg.Data.Sql.SqlOperator.NotEqual) { where.AddFilter(new Csg.Data.Sql.SqlListFilter(root, columnName, dataType, values) { Size = dataTypeSize, NotInList = true }); } // handle filtering on a list for other operators (rare case) else if (isMutliValued) { var filterCollection = new Csg.Data.Sql.SqlFilterCollection(); filterCollection.Logic = Csg.Data.Sql.SqlLogic.Or; foreach (var filterValue in values) { filterCollection.Add(root, columnName, nativeOperator, dataType, filterValue); } where.AddFilter(filterCollection); } // handle filtering on NULL else if (firstValue == null) { //TODO: Should we handle filtering on null this way, or add an explicit IsNull on the filter object? where.AddFilter(new Csg.Data.Sql.SqlNullFilter(root, columnName, @operator == ListFilterOperator.Equal ? true : false)); } // handle filtering on exactly one value else { where.AddFilter(new Csg.Data.Sql.SqlCompareFilter(root, columnName, nativeOperator, dataType, firstValue) { Size = dataTypeSize }); } } }
/// <summary> /// Adds a filter to the given request builder. /// </summary> /// <typeparam name="TValue"></typeparam> /// <param name="builder"></param> /// <param name="fieldName"></param> /// <param name="operator"></param> /// <param name="value"></param> /// <returns></returns> public static ListRequestBuilder <T> Where <T, TValue>(this ListRequestBuilder <T> builder, string fieldName, ListFilterOperator @operator, TValue value) { builder.Request.Filters.Add(new ListQuery.ListFilter() { Name = fieldName, Operator = @operator, Value = value }); return(builder); }