public static string Format(this ComparisonOperator op)
{
switch (op)
{
case ComparisonOperator.Equal:
return("==");
case ComparisonOperator.NotEqual:
return("!=");
case ComparisonOperator.GreaterThan:
return(">");
case ComparisonOperator.GreaterThanOrEqual:
return(">=");
case ComparisonOperator.LessThan:
return("<");
case ComparisonOperator.LessThanOrEqual:
return("<=");
default:
throw new NotSupportedException(op.ToString());
}
}
public async Task Can_filter_comparison_on_DateTime_in_UTC_time_zone(string matchingDateTime, string nonMatchingDateTime, ComparisonOperator filterOperator,
string filterDateTime)
{
// Arrange
var resource = new FilterableResource
{
SomeDateTimeInUtcZone = DateTime.Parse(matchingDateTime, CultureInfo.InvariantCulture).AsUtc()
};
var otherResource = new FilterableResource
{
SomeDateTimeInUtcZone = DateTime.Parse(nonMatchingDateTime, CultureInfo.InvariantCulture).AsUtc()
};
await _testContext.RunOnDatabaseAsync(async dbContext =>
{
await dbContext.ClearTableAsync <FilterableResource>();
dbContext.FilterableResources.AddRange(resource, otherResource);
await dbContext.SaveChangesAsync();
});
string route = $"/filterableResources?filter={filterOperator.ToString().Camelize()}(someDateTimeInUtcZone,'{filterDateTime}')";
// Act
(HttpResponseMessage httpResponse, Document responseDocument) = await _testContext.ExecuteGetAsync <Document>(route);
// Assert
httpResponse.ShouldHaveStatusCode(HttpStatusCode.OK);
responseDocument.Data.ManyValue.ShouldHaveCount(1);
responseDocument.Data.ManyValue[0].Attributes.ShouldContainKey("someDateTimeInUtcZone")
.With(value => value.Should().Be(resource.SomeDateTimeInUtcZone));
}
public async Task Can_filter_comparison_on_whole_number(int matchingValue, int nonMatchingValue, ComparisonOperator filterOperator, double filterValue)
{
// Arrange
var resource = new FilterableResource
{
SomeInt32 = matchingValue
};
var otherResource = new FilterableResource
{
SomeInt32 = nonMatchingValue
};
await _testContext.RunOnDatabaseAsync(async db =>
{
await db.ClearCollectionAsync <FilterableResource>();
await db.GetCollection <FilterableResource>()
.InsertManyAsync(new[] { resource, otherResource });
});
var route = $"/filterableResources?filter={filterOperator.ToString().Camelize()}(someInt32,'{filterValue}')";
// Act
var(httpResponse, responseDocument) = await _testContext.ExecuteGetAsync <Document>(route);
// Assert
httpResponse.Should().HaveStatusCode(HttpStatusCode.OK);
responseDocument.ManyData.Should().HaveCount(1);
responseDocument.ManyData[0].Attributes["someInt32"].Should().Be(resource.SomeInt32);
}
public static bool Match(this ComparisonOperator op, int result)
{
switch (op)
{
case ComparisonOperator.Equal:
return(result == 0);
case ComparisonOperator.NotEqual:
return(result != 0);
case ComparisonOperator.GreaterThan:
return(result > 0);
case ComparisonOperator.GreaterThanOrEqual:
return(result >= 0);
case ComparisonOperator.LessThan:
return(result < 0);
case ComparisonOperator.LessThanOrEqual:
return(result <= 0);
default:
throw new NotSupportedException(op.ToString());
}
}
public async Task Can_filter_comparison_on_DateTime(string matchingDateTime, string nonMatchingDateTime, ComparisonOperator filterOperator, string filterDateTime)
{
// Arrange
var resource = new FilterableResource
{
SomeDateTime = DateTime.SpecifyKind(DateTime.ParseExact(matchingDateTime, "yyyy-MM-dd", null), DateTimeKind.Utc)
};
var otherResource = new FilterableResource
{
SomeDateTime = DateTime.SpecifyKind(DateTime.ParseExact(nonMatchingDateTime, "yyyy-MM-dd", null), DateTimeKind.Utc)
};
await _testContext.RunOnDatabaseAsync(async db =>
{
await db.ClearCollectionAsync <FilterableResource>();
await db.GetCollection <FilterableResource>()
.InsertManyAsync(new[] { resource, otherResource });
});
var route = $"/filterableResources?filter={filterOperator.ToString().Camelize()}(someDateTime,'{DateTime.ParseExact(filterDateTime, "yyyy-MM-dd", null)}')";
// Act
var(httpResponse, responseDocument) = await _testContext.ExecuteGetAsync <Document>(route);
// Assert
httpResponse.Should().HaveStatusCode(HttpStatusCode.OK);
responseDocument.ManyData.Should().HaveCount(1);
responseDocument.ManyData[0].Attributes["someDateTime"].Should().Be(resource.SomeDateTime);
}
public static void UpdateNode([NotNull] Node node, [Required] string name,
string schemaNs, string schema, ComparisonOperator op, string value)
{
ComboBox editor = new ComboBox
{
Name = String.Concat("_comboBox", name.Replace(" ", "")),
DropDownStyle = ComboBoxStyle.DropDownList
};
// ReSharper disable once CoVariantArrayConversion
editor.Items.AddRange(Enum.GetNames(typeof(ComparisonOperator)));
editor.SelectedItem = op.ToString();
editor.Tag = node;
node.Cells.Add(new Cell {
Name = Resources.LabelSchemaNamespace, Text = schemaNs
});
node.Cells.Add(new Cell {
Name = Resources.LabelSchemaName, Text = schema
});
node.Cells.Add(new Cell {
Name = Resources.LabelOperator, HostedControl = editor
});
node.Cells.Add(new Cell {
Name = Resources.LabelValue, Text = value
});
}
public async Task Can_filter_comparison_on_whole_number(int matchingValue, int nonMatchingValue, ComparisonOperator filterOperator, double filterValue)
{
// Arrange
var resource = new FilterableResource
{
SomeInt32 = matchingValue
};
var otherResource = new FilterableResource
{
SomeInt32 = nonMatchingValue
};
await _testContext.RunOnDatabaseAsync(async dbContext =>
{
await dbContext.ClearTableAsync <FilterableResource>();
dbContext.FilterableResources.AddRange(resource, otherResource);
await dbContext.SaveChangesAsync();
});
string route = $"/filterableResources?filter={filterOperator.ToString().Camelize()}(someInt32,'{filterValue}')";
// Act
(HttpResponseMessage httpResponse, Document responseDocument) = await _testContext.ExecuteGetAsync <Document>(route);
// Assert
httpResponse.ShouldHaveStatusCode(HttpStatusCode.OK);
responseDocument.Data.ManyValue.ShouldHaveCount(1);
responseDocument.Data.ManyValue[0].Attributes.ShouldContainKey("someInt32").With(value => value.Should().Be(resource.SomeInt32));
}
public async Task Can_filter_comparison_on_fractional_number(double matchingValue, double nonMatchingValue, ComparisonOperator filterOperator, double filterValue)
{
// Arrange
var resource = new FilterableResource
{
SomeDouble = matchingValue
};
var otherResource = new FilterableResource
{
SomeDouble = nonMatchingValue
};
await _testContext.RunOnDatabaseAsync(async dbContext =>
{
await dbContext.ClearTableAsync <FilterableResource>();
dbContext.FilterableResources.AddRange(resource, otherResource);
await dbContext.SaveChangesAsync();
});
var route = $"/filterableResources?filter={filterOperator.ToString().Camelize()}(someDouble,'{filterValue}')";
// Act
var(httpResponse, responseDocument) = await _testContext.ExecuteGetAsync <Document>(route);
// Assert
httpResponse.Should().HaveStatusCode(HttpStatusCode.OK);
responseDocument.ManyData.Should().HaveCount(1);
responseDocument.ManyData[0].Attributes["someDouble"].Should().Be(resource.SomeDouble);
}
public async Task Can_filter_comparison_on_DateTime(string matchingDateTime, string nonMatchingDateTime, ComparisonOperator filterOperator, string filterDateTime)
{
// Arrange
var resource = new FilterableResource
{
SomeDateTime = DateTime.ParseExact(matchingDateTime, "yyyy-MM-dd", null)
};
var otherResource = new FilterableResource
{
SomeDateTime = DateTime.ParseExact(nonMatchingDateTime, "yyyy-MM-dd", null)
};
await _testContext.RunOnDatabaseAsync(async dbContext =>
{
await dbContext.ClearTableAsync <FilterableResource>();
dbContext.FilterableResources.AddRange(resource, otherResource);
await dbContext.SaveChangesAsync();
});
var route = $"/filterableResources?filter={filterOperator.ToString().Camelize()}(someDateTime," +
$"'{DateTime.ParseExact(filterDateTime, "yyyy-MM-dd", null)}')";
// Act
var(httpResponse, responseDocument) = await _testContext.ExecuteGetAsync <Document>(route);
// Assert
httpResponse.Should().HaveStatusCode(HttpStatusCode.OK);
responseDocument.ManyData.Should().HaveCount(1);
responseDocument.ManyData[0].Attributes["someDateTime"].Should().BeCloseTo(resource.SomeDateTime);
}
public override string ToString()
{
if (IsContainerOnly)
{
return("Container");
}
return($"{LogicOperator} {Table}.{Column} {ComparisonOperator.ToString()} {Value} - Sub Clauses: {SubClauses.Count}");
}
protected virtual string CreateComparisonClause(string fieldName, ComparisonOperator comparisonOperator, object value)
{
string output = string.Empty;
if (value != null && value != System.DBNull.Value)
{
switch (comparisonOperator)
{
case ComparisonOperator.EqualTo: output = fieldName + " = " + FormatSQLValue(value); break;
case ComparisonOperator.NotEqualTo: output = fieldName + " <> " + FormatSQLValue(value); break;
case ComparisonOperator.GreaterThan: output = fieldName + " > " + FormatSQLValue(value); break;
case ComparisonOperator.GreaterThanOrEqualTo: output = fieldName + " >= " + FormatSQLValue(value); break;
case ComparisonOperator.LessThan: output = fieldName + " < " + FormatSQLValue(value); break;
case ComparisonOperator.LessThanOrEqualTo: output = fieldName + " <= " + FormatSQLValue(value); break;
case ComparisonOperator.Like: output = fieldName + " LIKE " + FormatSQLValue(value); break;
case ComparisonOperator.NotLike: output = "NOT " + fieldName + " LIKE " + FormatSQLValue(value); break;
case ComparisonOperator.In: output = fieldName + " IN (" + FormatSQLValue(value) + ")"; break;
case ComparisonOperator.Contains: output = string.Format("{0} LIKE ({1})", fieldName, FormatSQLValue("%" + value + "%")); break;
case ComparisonOperator.NotContains: output = string.Format("NOT {0} LIKE {1}", fieldName, FormatSQLValue("%" + value + "%")); break;
case ComparisonOperator.StartsWith: output = string.Format("{0} LIKE ({1})", fieldName, FormatSQLValue(value + "%")); break;
case ComparisonOperator.EndsWith: output = string.Format("{0} LIKE ({1})", fieldName, FormatSQLValue("%" + value)); break;
}
}
else // value==null || value==DBNull.Value
{
if ((comparisonOperator != ComparisonOperator.EqualTo) && (comparisonOperator != ComparisonOperator.NotEqualTo))
{
throw new Exception("Cannot use comparison operator " + comparisonOperator.ToString() + " for NULL values.");
}
else
{
switch (comparisonOperator)
{
case ComparisonOperator.EqualTo: output = fieldName + " IS NULL"; break;
case ComparisonOperator.NotEqualTo: output = "NOT " + fieldName + " IS NULL"; break;
}
}
}
return(output);
}
public override string ToString()
{
if (!string.IsNullOrEmpty(_customClause))
{
return(_customClause);
}
StringBuilder result = new StringBuilder();
result.AppendFormat(
"{0}_{1}_{2}",
ColumnName,
ComparisonOperator.ToString(),
ColumnValue);
if (LogicalOperatorAgainstNextColumn != null)
{
result.AppendFormat("_{0}", LogicalOperatorAgainstNextColumn.Value);
}
return(result.ToString());
}
protected string SerializeCondition(ComparisonOperator eCompare, object objValue)
{
EnsureCompareValuePairValid(eCompare, objValue);
string strSQL = string.Empty;
//Return 'IS NULL' rather than '= NULL'
if (ValueIsNull(objValue))
{
if (eCompare == ComparisonOperator.EqualTo)
strSQL += "IS " + SerializeValue(objValue);
else if (eCompare == ComparisonOperator.NotEqualTo)
strSQL += "IS NOT " + SerializeValue(objValue);
else
throw new Exceptions.DatabaseObjectsException("DBNull or Nothing/null was specified as a value using the " + eCompare.ToString() + " operator");
}
else
strSQL += SerializeComparisonOperator(eCompare) + " " + SerializeValue(objValue);
return strSQL;
}
public bool Validate()
{
if (value1 == null || value2 == null)
{
Debug.LogError("Custom value is null!");
return(false);
}
if (value1.type != value2.type)
{
Debug.LogError("Custom value type mismatch! Used " + value1.type.ToString() + " and " + value1.type.ToString());
return(false);
}
if (value1.type == CustomValueData.ValueType.Bool)
{
if (comparisonOperator != ComparisonOperator.EqualTo && comparisonOperator != ComparisonOperator.NotEqualTo)
{
Debug.LogError("Invalid conditional used for Bool type! Used: " + comparisonOperator.ToString());
return(false);
}
}
else if (value1.type == CustomValueData.ValueType.String)
{
if (comparisonOperator != ComparisonOperator.EqualTo && comparisonOperator != ComparisonOperator.NotEqualTo)
{
Debug.LogError("Invalid conditional used for String type! Used: " + comparisonOperator.ToString());
return(false);
}
}
return(true);
}
public ValueOutOfRangeException(string i_NameOfArgument, int i_ActualValue, int i_Threshold, eComparisonOperator i_ComparisonOperator)
: base(i_NameOfArgument, i_ActualValue, string.Format("{0} {1} {2}", i_NameOfArgument, ComparisonOperator.ToString(i_ComparisonOperator), i_Threshold))
{
}
public WhereClauseComparisonOperatorWindows(ComparisonOperator comparisonOperator)
{
_value = GetComparisonOperators()[comparisonOperator.ToString()];
}
private XElement Where(ComparisonOperator comparisonOperator, [NotNull] XElement xFieldRef,
[CanBeNull] XElement xValue)
{
return(new XElement(comparisonOperator.ToString(), xFieldRef, xValue));
}
internal static QueryFilter HasLocalCopyFilterBuilder(SinglePropertyFilter filter)
{
LegacyDatabase.InternalAssertComparisonFilter(filter, LegacyDatabaseSchema.HasLocalCopy);
ComparisonFilter comparisonFilter = filter as ComparisonFilter;
ComparisonOperator comparisonOperator = comparisonFilter.ComparisonOperator;
if (comparisonOperator != ComparisonOperator.Equal && ComparisonOperator.NotEqual != comparisonOperator)
{
throw new ADFilterException(DirectoryStrings.ExceptionInvalidBitwiseComparison(LegacyDatabaseSchema.HasLocalCopy.Name, comparisonOperator.ToString()));
}
bool flag = (bool)comparisonFilter.PropertyValue;
bool flag2 = ComparisonOperator.Equal == comparisonOperator;
QueryFilter queryFilter = new BitMaskAndFilter(LegacyDatabaseSchema.HasLocalCopyValue, 1UL);
if (flag ^ flag2)
{
queryFilter = new NotFilter(queryFilter);
}
return(queryFilter);
}
protected virtual string CreateComparisonClause(string tableName, string columnName, ComparisonOperator comparisonOperator, object value)
{
string fieldName = CreateFieldName(tableName, columnName);
string output = string.Empty;
if (value != null && value != DBNull.Value)
{
switch (comparisonOperator)
{
case ComparisonOperator.EqualTo: output = $"{fieldName} = {FormatSQLValue(value)}"; break;
case ComparisonOperator.NotEqualTo: output = $"{fieldName} <> {FormatSQLValue(value)}"; break;
case ComparisonOperator.GreaterThan: output = $"{fieldName} > {FormatSQLValue(value)}"; break;
case ComparisonOperator.GreaterThanOrEqualTo: output = $"{fieldName} >= {FormatSQLValue(value)}"; break;
case ComparisonOperator.LessThan: output = $"{fieldName} < {FormatSQLValue(value)}"; break;
case ComparisonOperator.LessThanOrEqualTo: output = $"{fieldName} <= {FormatSQLValue(value)}"; break;
case ComparisonOperator.Like: output = $"{fieldName} LIKE {FormatSQLValue(value)}"; break;
case ComparisonOperator.NotLike: output = $"NOT {fieldName} LIKE {FormatSQLValue(value)}"; break;
case ComparisonOperator.In:
{
var type = value.GetType();
if (type.IsCollection())
{
var collection = (value as IEnumerable);
var collectionType = GetCollectionType(collection, type);
if (collectionType == typeof(DateTime))
{
// IN() won't work for dates. Need to substitue multiple date ranges instead..
// ..for the start and end of each day in `value`
var values = collection.OfType <DateTime>().ToArray();
var clause = GetDateRangesForInOperator(tableName, columnName, values);
var sb = new StringBuilder();
CreateWhereClause(clause, sb, true);
return(sb.ToString());
}
}
output = $"{fieldName} IN ({FormatSQLValue(value)})";
}
break;
case ComparisonOperator.Contains: output = $"{fieldName} LIKE {FormatSQLValue("%" + value + "%")}"; break;
case ComparisonOperator.NotContains: output = $"NOT {fieldName} LIKE {FormatSQLValue("%" + value + "%")}"; break;
case ComparisonOperator.StartsWith: output = $"{fieldName} LIKE {FormatSQLValue(value + "%")}"; break;
case ComparisonOperator.EndsWith: output = $"{fieldName} LIKE {FormatSQLValue("%" + value)}"; break;
case ComparisonOperator.HasFlag: output = $"{fieldName} & {value} <> 0"; break;
}
}
else // value==null || value==DBNull.Value
{
if ((comparisonOperator != ComparisonOperator.EqualTo) && (comparisonOperator != ComparisonOperator.NotEqualTo))
{
throw new Exception("Cannot use comparison operator " + comparisonOperator.ToString() + " for NULL values.");
}
else
{
switch (comparisonOperator)
{
case ComparisonOperator.EqualTo: output = $"{fieldName} IS NULL"; break;
case ComparisonOperator.NotEqualTo: output = $"{fieldName} IS NOT NULL"; break;
}
}
}
return(output);
}
/// <summary>
/// Filters the data in the <see cref="T:System.Linq.IQueryable"/> object based on the parameters specifed and appends those parameters to the object prior to SQL execution.
/// </summary>
/// <param name="dataset">A value of type <see cref="T:System.Linq.IQueryable"/> containing the data to be filtered.</param>
/// <param name="filterProperty">A <see cref="T:System.String"/> value indicating the name of a public, non-static property member of type 'T' to compare.</param>
/// <param name="filterValue">A <see cref="T:System.Object"/> value indicating the value(s) to compare against.</param>
/// <param name="op">A value from the <see cref="T:RainstormStudios.Data.Linq.ComparisonOperator"/> enumeration indicating how the data will be compared.</param>
/// <param name="matchCase">A value of type <see cref="T:System.Boolean"/> indicating true if the comparison should be case sensitive. Otherwise, false.</param>
/// <returns>A value of type <see cref="T:System.Linq.IQueryable"/> containing the data matching the filter requirements.</returns>
public static IQueryable FilterResult(IQueryable dataset, string filterProperty, object filterValue, ComparisonOperator op, bool matchCase = false)
{
if (op == ComparisonOperator.Contains && (!(filterValue is string) && !(filterValue is Array)))
{
throw new ArgumentException(string.Format("Contains operation is not valid on member {1}. Invalid data type: {0}", filterValue.GetType().Name, filterProperty), "filterValue");
}
if (op != ComparisonOperator.Contains && op != ComparisonOperator.Equals)
{
// This would require a numerical, DateTime, byte or boolean data type. This is mostly as a failsafe, to get a "cleaner" error message.
int iVal; DateTime dtVal; byte btVal; bool bVal;
if (filterValue is string)
{
string fv = (string)filterValue;
if (!int.TryParse(fv, out iVal) && !DateTime.TryParse(fv, out dtVal) && !byte.TryParse(fv, out btVal) && !bool.TryParse(fv, out bVal))
{
throw new ArgumentException(string.Format("Unable to convert value to valid data type for requested operation: {0} ({1})", op.ToString(), filterValue.GetType().Name));
}
}
}
StringBuilder sbPredicate = new StringBuilder();
if (filterValue is Array && op == ComparisonOperator.Contains)
{
sbPredicate.Append("@0.Contains(");
sbPredicate.Append(filterProperty);
if (!matchCase)
{
sbPredicate.Append(".ToLower()");
}
sbPredicate.Append(")");
}
else
{
sbPredicate.Append(filterProperty);
if (filterValue is string && !matchCase)
{
sbPredicate.Append(".ToLower()");
}
sbPredicate.Append(compOpStr[(int)op]);
sbPredicate.Append("@0");
if (op == ComparisonOperator.Contains)
{
sbPredicate.Append(")");
}
}
string pred = sbPredicate.ToString();
if ((filterValue is Array) && !matchCase)
{
List <object> lst = new List <object>();
foreach (object obj in ((Array)filterValue))
{
if (obj is string)
{
lst.Add(obj.ToString().ToLower());
}
else
{
lst.Add(obj);
}
}
return(dataset.Where(pred, filterValue));
}
else if (filterValue is string && !matchCase)
{
return(dataset.Where(pred, filterValue.ToString()));
}
else
{
return(dataset.Where(pred, filterValue));
}
}