/// <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("?");
}
}
