/// <summary> /// Renders an SQL where clause from a query element /// </summary> /// <param name="element">The element to use</param> /// <returns>The SQL where clause</returns> private string RenderWhereClause(Type type, object element, List <object> args) { if (element == null || element is Empty) { return(string.Empty); } if (element is And andElement) { return(string.Join( " AND ", andElement .Items .Select(x => RenderWhereClause(type, x, args)) .Where(x => !string.IsNullOrWhiteSpace(x)) .Select(x => $"({x})") )); } else if (element is Or orElement) { return(string.Join( " OR ", orElement .Items .Select(x => RenderWhereClause(type, x, args)) .Where(x => !string.IsNullOrWhiteSpace(x)) .Select(x => $"({x})") )); } else if (element is Property property) { return(GetTypeMap(type).QuotedColumnName(property.PropertyName)); } else if (element is UnaryOperator unop) { return($"{unop.Operator} ({RenderWhereClause(type, unop.Expression, args)})"); } else if (element is ParenthesisExpression pex) { return($"({RenderWhereClause(type, pex.Expression, args)})"); } else if (element is CustomQuery cq) { args.AddRange(cq.Arguments ?? new object[0]); return(cq.Value); } else if (element is Compare compare) { if ( string.Equals(compare.Operator, "IN", StringComparison.OrdinalIgnoreCase) || string.Equals(compare.Operator, "NOT IN", StringComparison.OrdinalIgnoreCase) ) { // Support for "IN" with sub-query if (compare.RightHandSide is Query rhq) { if (rhq.Parsed.Type != QueryType.Select) { throw new ArgumentException("The query must be a select statement for exactly one column", nameof(compare.RightHandSide)); } if (rhq.Parsed.SelectColumns.Count() != 1) { throw new ArgumentException("The query must be a select statement for exactly one column", nameof(compare.RightHandSide)); } var rvp = RenderStatement(rhq); args.AddRange(rvp.Value); return($"{RenderWhereClause(type, compare.LeftHandSide, args)} {compare.Operator} ({rvp.Key})"); } var rhsel = compare.RightHandSide; IEnumerable items = null; // Unwrap a list in parenthesis if (rhsel is ParenthesisExpression rhspe) { var ve = (rhspe.Expression is Value rhspev) ? rhspev.Item : rhspe.Expression; if (ve is IEnumerable enve) { items = enve; } else { var a = Array.CreateInstance(ve?.GetType() ?? typeof(object), 1); a.SetValue(ve, 0); items = a; } } // If no parenthesis, look for a sequence inside if (items == null && compare.RightHandSide is Value rhsv) { items = rhsv.Item as IEnumerable; } // No value, check for sequnence as a plain object if (items == null && compare.RightHandSide is IEnumerable rhsen) { items = rhsen; } // Bounce back attempts to use a string as a char[] sequence (it implements IEnumerable) if (items is string its) { items = new string[] { its } } ; if (items == null) { return(RenderWhereClause(type, QueryUtil.Equal(compare.LeftHandSide, null), args)); } var op = string.Equals(compare.Operator, "IN", StringComparison.OrdinalIgnoreCase) ? "=" : "!="; // Special handling of null in lists if (items.Cast <object>().Any(x => x == null)) { return(RenderWhereClause( type, QueryUtil.Or( QueryUtil.In(compare.LeftHandSide, items.Cast <object>().Where(x => x != null)), QueryUtil.Compare(compare.LeftHandSide, op, null) ), args )); } // No nulls, just return plain "IN" or "NOT IN" // Does not work, it does not bind correctly to the array for some reason // args.Add(items); // return $"{RenderWhereClause(type, compare.LeftHandSide, args)} {compare.Operator} (?)"; // Render before, in case LHS needs to be in the args list var lhsstr = RenderWhereClause(type, compare.LeftHandSide, args); // Workaround is to expand to comma separated list var qs = new List <string>(); foreach (var n in items) { args.Add(n); qs.Add("?"); } return($"{lhsstr} {compare.Operator} ({string.Join(",", qs)})"); } // Extract the arguments, if they are arguments var lhs = compare.LeftHandSide is Value lhsVal ? lhsVal.Item : compare.LeftHandSide; var rhs = compare.RightHandSide is Value rhsVal ? rhsVal.Item : compare.RightHandSide; // Special handling for enums, as they are string serialized in the database if (IsQueryItemEnum(type, lhs) || IsQueryItemEnum(type, rhs)) { if (!new string[] { "=", "LIKE", "!=", "NOT LIKE" }.Any(x => string.Equals(x, compare.Operator, StringComparison.InvariantCultureIgnoreCase))) { throw new ArgumentException("Can only compare enums with equal or not equal as they are stored as strings in the database"); } // Force enum arguments to strings if (lhs != null && !(lhs is QueryElement)) { lhs = lhs.ToString(); } if (rhs != null && !(rhs is QueryElement)) { rhs = rhs.ToString(); } } // Special handling of null values to be more C# like var anyNulls = lhs == null || rhs == null; // Rewire gteq and lteq to handle nulls like C# if (anyNulls && string.Equals(compare.Operator, "<=")) { return(RenderWhereClause(type, QueryUtil.Or( QueryUtil.Compare(lhs, "<", rhs), QueryUtil.Compare(lhs, "=", rhs) ) , args)); } if (anyNulls && string.Equals(compare.Operator, ">=")) { return(RenderWhereClause(type, QueryUtil.Or( QueryUtil.Compare(lhs, ">", rhs), QueryUtil.Compare(lhs, "=", rhs) ) , args)); } // Rewire compare operator to also match nulls if (anyNulls && (string.Equals(compare.Operator, "=") || string.Equals(compare.Operator, "LIKE", StringComparison.OrdinalIgnoreCase))) { if (lhs == null) { return($"{RenderWhereClause(type, rhs, args)} IS NULL"); } else { return($"{RenderWhereClause(type, lhs, args)} IS NULL"); } } if (anyNulls && (string.Equals(compare.Operator, "!=") || string.Equals(compare.Operator, "NOT LIKE", StringComparison.OrdinalIgnoreCase))) { if (lhs == null) { return($"{RenderWhereClause(type, rhs, args)} IS NOT NULL"); } else { return($"{RenderWhereClause(type, lhs, args)} IS NOT NULL"); } } return($"{RenderWhereClause(type, lhs, args)} {compare.Operator} {RenderWhereClause(type, rhs, args)}"); } else if (element is Value ve) { args.Add(ve.Item); return("?"); } else if (element is Arithmetic arithmetic) { return($"{RenderWhereClause(type, arithmetic.LeftHandSide, args)} {arithmetic.Operator} {RenderWhereClause(type, arithmetic.RightHandSide, args)}"); } else if (element is QueryElement) { throw new Exception($"Unexpected query element: {element.GetType()}"); } else { args.Add(element); return("?"); } }