object ToInPartialString(Expression memberExpr, object colName) { UnaryExpression body = Expression.Convert(memberExpr, typeof(object)); Expression <Func <object> > expression = Expression.Lambda <Func <object> >(body, new ParameterExpression[0]); Func <object> func = expression.Compile(); List <object> list = Flatten(func() as IEnumerable); StringBuilder stringBuilder = new StringBuilder(); if (list.Count > 0) { using (List <object> .Enumerator enumerator = list.GetEnumerator()) { while (enumerator.MoveNext()) { object current = enumerator.Current; if (stringBuilder.Length > 0) { stringBuilder.Append(","); } stringBuilder.Append(GetQuotedValue(current)); } } } else { stringBuilder.Append("NULL"); } return(FilterElement.Custom("[{0}] IN ({1})", colName, stringBuilder)); }
object VisitUnary(UnaryExpression u) { ExpressionType nodeType = u.NodeType; if (nodeType != ExpressionType.Convert) { if (nodeType == ExpressionType.Not) { object obj = this.Visit(u.Operand); if (!(obj is IFilterElement)) { return(!(bool)obj); } return(FilterElement.Custom("NOT (" + obj + ")")); } } else { if (u.Method != null) { return(Expression.Lambda(u, new ParameterExpression[0]).Compile().DynamicInvoke(new object[0])); } } return(this.Visit(u.Operand)); }
/// <summary> /// Initializes a new instance of the <see cref="Filter" /> class with one filter element /// of 'Equal to' condition or 'IS NULL' condition (if value is null) /// </summary> /// <param name="column">The column.</param> /// <param name="value">The value.</param> public Filter(string column, object value) { if (value == null) { this.filterElements.Add(FilterElement.Null(column)); } else { this.filterElements.Add(new FilterElement(column, FilterOperator.Eq, value)); } }
/// <summary> /// Returns a new object with same values /// </summary> /// <returns></returns> IFilterElement IFilterElement.Clone() { var obj = new FilterElement() { Key = this.Key, Value = this.Value, connOperator = this.connOperator, Operator = this.Operator, DataType = this.DataType, }; return(obj); }
object VisitBinary(BinaryExpression b) { string text = this.BindOperant(b.NodeType); object obj; object obj2; if (text == "AND" || text == "OR") { MemberExpression memberExpression = b.Left as MemberExpression; if (memberExpression != null && memberExpression.Expression != null && memberExpression.Expression.NodeType == ExpressionType.Parameter) { obj = new FilterElement(this.VisitMemberAccess(memberExpression).ToString(), FilterOperator.Eq, true); } else { obj = this.Visit(b.Left); } memberExpression = (b.Right as MemberExpression); if (memberExpression != null && memberExpression.Expression != null && memberExpression.Expression.NodeType == ExpressionType.Parameter) { obj2 = new FilterElement(this.VisitMemberAccess(memberExpression).ToString(), FilterOperator.Eq, true); } else { obj2 = this.Visit(b.Right); } if (!(obj is IFilterElement) && !(obj2 is IFilterElement)) { return(Expression.Lambda(b, new ParameterExpression[0]).Compile().DynamicInvoke(new object[0])); } return(text == "AND" ? Filter.AndElements((IFilterElement)obj, (IFilterElement)obj2) : Filter.OrElements((IFilterElement)obj, (IFilterElement)obj2)); } else { obj = this.Visit(b.Left); obj2 = this.Visit(b.Right); } string a; if ((a = text) != null && (a == "MOD" || a == "COALESCE")) { return(FilterElement.Custom("{0}({1},{2})", text, obj, obj2)); } return(new FilterElement(obj.ToString(), new FilterOperator(text), obj2)); }
/// <summary> /// Builds the filter from 'WHERE ()' statement (generated by ServiceStack.OrmLite.SqlServer.SqlServerExpression) /// ..but allows to be used as initializer with custom sql in form like 'col1 = 1 or (col2 = 3 and col1 is null)' /// </summary> public static Filter FromSql(string wheresql) { if (string.IsNullOrEmpty(wheresql)) { return(null); } if (wheresql.StartsWith("WHERE ", StringComparison.InvariantCultureIgnoreCase)) { if (wheresql[6] == '(') { wheresql = wheresql.Substring(7, wheresql.Length - 8); } else { wheresql = wheresql.Substring(6, wheresql.Length - 6); } } return(new Filter(FilterElement.Custom(wheresql))); }
/// <summary> /// Gets the filter 'DatumPlatnosti is null or DatumPlatnosti > getdate()' to this filter with "AND" /// </summary> /// <returns></returns> public static Filter NotDeleted() { return(OrElements(FilterElement.Null("DatumPlatnosti"), FilterElement.Custom("DatumPlatnosti > GETDATE()"))); }
/// <summary> /// Returns FilterElement representation of actual instance or null if not valid. /// NOTE: The LogicOperator is applied. /// </summary> public FilterElement ToFilterElement(string dbCollate) { string sqlOp = ""; FilterOperator comparison = null; string custom = null; string value; if (Value != null && Value is IEnumerable <object> enumerableValue) { value = enumerableValue.Select(x => x.ToString()).Join(","); } else { value = Value?.ToString(); } switch (this.ComparisonOperator.ToLower()) { case "eq": comparison = FilterOperator.Eq; sqlOp = "="; break; case "ne": comparison = FilterOperator.Ne; sqlOp = "<>"; break; case "gt": case ">": comparison = FilterOperator.Gt; sqlOp = ">"; break; case "lt": case "<": comparison = FilterOperator.Lt; sqlOp = "<"; break; case "ge": case ">=": comparison = FilterOperator.Ge; sqlOp = ">="; break; case "le": case "<=": comparison = FilterOperator.Le; sqlOp = "<="; break; case "=": //Support pre staru verziu comparison = (!this.OnlyIdentical && this.Type == PfeDataType.Text) ? FilterOperator.Like : FilterOperator.Eq; if (comparison.Value == FilterOperator.Like.Value) { value = string.Format("%{0}%", this.Value); } sqlOp = "="; break; case "<>": //Support pre staru verziu comparison = (!this.OnlyIdentical && this.Type == PfeDataType.Text) ? FilterOperator.NotLike : FilterOperator.Ne; if (comparison.Value == FilterOperator.NotLike.Value) { value = string.Format("%{0}%", this.Value); } sqlOp = "<>"; break; case "sw": comparison = FilterOperator.Like; value = string.Format("{0}%", this.Value); break; case "ew": comparison = FilterOperator.Like; value = string.Format("%{0}", this.Value); break; case "co": comparison = FilterOperator.Like; value = string.Format("%{0}%", this.Value); break; case "nc": comparison = FilterOperator.NotLike; value = string.Format("%{0}%", this.Value); break; case "em": case "empty": if (this.Type == PfeDataType.Text || this.Type == PfeDataType.Unknown) { custom = string.Format("(ISNULL([{0}], '') = '')", this.Field); } else { comparison = FilterOperator.Null; } break; case "en": case "notempty": if (this.Type == PfeDataType.Text || this.Type == PfeDataType.Unknown) { custom = string.Format("(ISNULL([{0}], '') <> '')", this.Field); } else { comparison = FilterOperator.NotNull; } break; } //invalid data? if (comparison == null && custom == null) { return(null); } if (Type == PfeDataType.Text && !string.IsNullOrEmpty(dbCollate) && string.IsNullOrEmpty(custom)) { custom = $"{Field} {comparison.Value} '{value}' collate {dbCollate}"; } if (this.Type == PfeDataType.Date && sqlOp != "") { custom = string.Format("CAST({0} AS DATE) {1} '{2}'", this.Field, sqlOp, this.Value); } if (this.Type == PfeDataType.Boolean && sqlOp == "<>" && this.Value.ToString() == "1") { custom = string.Format("ISNULL({0}, 0) {1} {2}", this.Field, sqlOp, this.Value); } FilterElement condition = (custom == null) ? new FilterElement(this.Field, comparison, value, this.Type) : FilterElement.Custom(custom); ((IFilterElement)condition).ConnOperator = this.LogicOperator; return(condition); }
/// <summary> /// Appends the '[NOT] NULL' condition to this filter with "AND" /// </summary> /// <param name="filter">The filter.</param> /// <param name="column">The column.</param> /// <param name="not">true for NOT NULL comparison</param> /// <returns></returns> public static Filter AndNull(this Filter filter, string column, bool not) { return(filter.And(not ? FilterElement.NotNull(column) : FilterElement.Null(column))); }