/// <summary> /// Returns a <see cref="FilterNegation"/> containing of the inner expression /// </summary> public static FilterNegation Make(FilterExpression inner) { return(new FilterNegation { Inner = inner }); }
/// <summary> /// Turns a filter expression into an SQL WHERE clause (without the WHERE keyword), adds all required parameters into the <see cref="SqlStatementParameters"/> /// </summary> public static string FilterToSql(FilterExpression e, Func <Type, string> sources, SqlStatementParameters ps, JoinTree joinTree, int userId, DateTime?userToday) { if (e == null) { return(null); } // This inner function just relieves us of having to pass all the above parameters each time, they just become part of its closure string FilterToSqlInner(FilterExpression exp) { if (exp is FilterConjunction conExp) { return($"({FilterToSqlInner(conExp.Left)}) AND ({FilterToSqlInner(conExp.Right)})"); } if (exp is FilterDisjunction disExp) { return($"({FilterToSqlInner(disExp.Left)}) OR ({FilterToSqlInner(disExp.Right)})"); } if (exp is FilterNegation notExp) { return($"NOT ({FilterToSqlInner(notExp.Inner)})"); } if (exp is FilterAtom atom) { // (A) Prepare the symbol corresponding to the path, e.g. P1 var join = joinTree[atom.Path]; if (join == null) { // Developer mistake throw new InvalidOperationException($"The path '{string.Join('/', atom.Path)}' was not found in the joinTree"); } var symbol = join.Symbol; // (B) Determine the type of the property and its value var propName = atom.Property; var prop = join.Type.GetProperty(propName); if (prop == null) { // Developer mistake throw new InvalidOperationException($"Could not find property {propName} on type {join.Type}"); } // The type of the first operand var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType; if (!string.IsNullOrWhiteSpace(atom.Modifier)) { // So far all modifiers are only applicable for date properties if (propType != typeof(DateTime) && propType != typeof(DateTimeOffset)) { // Developer mistake throw new InvalidOperationException($"The modifier {atom.Modifier} is not valid for property {propName} since it is not of type DateTime or DateTimeOffset"); } // So far all modifiers are date modifiers that return INT propType = typeof(int); } // The expected type of the second operand (different in the case of hierarchyId) var expectedValueType = propType; if (expectedValueType == typeof(HierarchyId)) { var idType = join.Type.GetProperty("Id")?.PropertyType; if (idType == null) { // Programmer mistake throw new InvalidOperationException($"Type {join.Type} is a tree structure but has no Id property"); } expectedValueType = Nullable.GetUnderlyingType(idType) ?? idType; } // (C) Prepare the value (e.g. "'Huntington Rd.'") var valueString = atom.Value; object value; bool isNull = false; switch (valueString?.ToLower()) { // This checks all built-in values case "null": value = null; isNull = true; break; case "me": value = userId; break; // Relative DateTime values case "startofyear": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = StartOfYear(userToday); break; case "endofyear": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = StartOfYear(userToday).AddYears(1); break; case "startofquarter": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = StartOfQuarter(userToday); break; case "endofquarter": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = StartOfQuarter(userToday).AddMonths(3); break; case "startofmonth": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = StartOfMonth(userToday); break; case "endofmonth": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = StartOfMonth(userToday).AddMonths(1); break; case "today": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = Today(userToday); break; case "endofday": EnsureNullFunction(atom); EnsureTypeDateTime(atom, propName, propType); value = Today(userToday).AddDays(1); break; case "now": EnsureNullFunction(atom); EnsureTypeDateTimeOffset(atom, propName, propType); var now = DateTimeOffset.Now; value = now; break; default: if (expectedValueType == typeof(string) || expectedValueType == typeof(char)) { if (!valueString.StartsWith("'") || !valueString.EndsWith("'")) { // Developer mistake throw new InvalidOperationException($"Property {propName} is of type String, therefore the value it is compared to must be enclosed in single quotation marks"); } valueString = valueString[1..^ 1]; }