public static string Column(string columnName, double value, ColumnOperator op = ColumnOperator.Equal)
{
return(TableQuery.GenerateFilterConditionForDouble(
columnName,
ToQueryComparison(op),
value));
}
/// <summary>
/// Shortcut method that queries the table based on a given PartitionKey and given property with
/// the same property name. Handles the continuation token scenario as well. Overloaded to accept
/// all appropriate table entity types.
/// </summary>
/// <param name="partitionKey"></param>
/// <param name="propertyName"></param>
/// <param name="property"></param>
/// <returns></returns>
public async Task <List <TAzureTableEntity> > WherePropertyEqualsAsync(string partitionKey, string propertyName, double property)
{
var propertyFilter = TableQuery.GenerateFilterConditionForDouble(propertyName, QueryComparisons.Equal, property);
var query = PartitionKeyAndPropertyFilterQuery(partitionKey, propertyFilter);
return(await RunQuerySegmentAsync(query).ConfigureAwait(false));
}
public TableQuery GetSourceTableQuery()
{
TableQuery tq = new TableQuery();
tq.SelectColumns = new List <string>()
{
"PartitionKey", "RowKey", "KeyVersion"
};
string partitionFilter = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, Constants.RowKeyConstants.PreFixIdentityUserId),
TableOperators.And,
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, "V_"));
string rowFilterRole = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual, Constants.RowKeyConstants.PreFixIdentityUserRole),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, "S_"));
string rowFilterClaim = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual, Constants.RowKeyConstants.PreFixIdentityUserClaim),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, "D_"));
string keyVersionFilter = TableQuery.GenerateFilterConditionForDouble("KeyVersion", QueryComparisons.LessThan, 2.0);
string rowFilter = TableQuery.CombineFilters(rowFilterRole, TableOperators.Or, rowFilterClaim);
string keysFilter = TableQuery.CombineFilters(partitionFilter, TableOperators.And, rowFilter);
tq.FilterString = TableQuery.CombineFilters(keysFilter, TableOperators.And, keyVersionFilter);
return(tq);
}
public static string GetAreaFilter(string latitude, string longitude, double GeoLatDiff, double GeoLngDiff)
{
string latFilterFrom = TableQuery.GenerateFilterConditionForDouble(
InterventionFieldNames.GeoLat,
QueryComparisons.GreaterThanOrEqual,
ConvertToDouble(latitude) - GeoLatDiff
);
string latFilterTo = TableQuery.GenerateFilterConditionForDouble(
InterventionFieldNames.GeoLat,
QueryComparisons.LessThanOrEqual,
ConvertToDouble(latitude) + GeoLatDiff
);
string lngFilterFrom = TableQuery.GenerateFilterConditionForDouble(
InterventionFieldNames.GeoLng,
QueryComparisons.GreaterThanOrEqual,
ConvertToDouble(longitude) - GeoLngDiff
);
string lngFilterTo = TableQuery.GenerateFilterConditionForDouble(
InterventionFieldNames.GeoLng,
QueryComparisons.LessThanOrEqual,
ConvertToDouble(longitude) + GeoLngDiff
);
return(CombineFilters(
new List <string> {
latFilterFrom, latFilterTo, lngFilterFrom, lngFilterTo
}
));
}
/// <summary>
/// Get async.
/// </summary>
/// <typeparam name="ST">The skip type.</typeparam>
/// <param name="value">The value.</param>
/// <param name="propertyName">The property name.</param>
/// <param name="operation">The operation.</param>
/// <param name="take">The number of items to return.</param>
/// <param name="skip">The number of items to skip.</param>
/// <returns>The array or items.</returns>
public async Task <T[]> GetAsync <ST>(double value, string propertyName = "PartitionKey",
string operation = QueryComparisons.Equal, int take = 100, SkipCount <ST> skip = null)
{
TableQuery <T> scanQuery = null;
if (skip == null)
{
// Create the query.
scanQuery = new TableQuery <T>().Where
(TableQuery.GenerateFilterConditionForDouble(propertyName, operation, value));
}
else
{
// Create the query.
scanQuery = new TableQuery <T>().Where
(TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForDouble(propertyName, operation, value),
TableOperators.And,
skip.GetSkipQuery()
));
}
// Return the array
return(await ScanAsyncEx(scanQuery, take));
}
/// <summary>
/// Get the skip query.
/// </summary>
/// <returns>The skip query string.</returns>
public string GetSkipQuery()
{
// Select the skip type.
switch (typeof(T).Name.ToLower())
{
case "system.boolean":
case "boolean":
case "bool":
return(TableQuery.GenerateFilterConditionForBool(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(Boolean)Convert.ChangeType(_skipValue, typeof(Boolean))));
case "system.byte[]":
case "byte[]":
return(TableQuery.GenerateFilterConditionForBinary(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(Byte[])Convert.ChangeType(_skipValue, typeof(Byte[]))));
case "system.datetimeoffset":
case "datetimeoffset":
return(TableQuery.GenerateFilterConditionForDate(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(DateTimeOffset)Convert.ChangeType(_skipValue, typeof(DateTimeOffset))));
case "system.double":
case "double":
return(TableQuery.GenerateFilterConditionForDouble(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(Double)Convert.ChangeType(_skipValue, typeof(Double))));
case "system.guid":
case "guid":
return(TableQuery.GenerateFilterConditionForGuid(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(Guid)Convert.ChangeType(_skipValue, typeof(Guid))));
case "system.int32":
case "int32":
case "int":
return(TableQuery.GenerateFilterConditionForInt(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(Int32)Convert.ChangeType(_skipValue, typeof(Int32))));
case "system.int64":
case "int64":
case "long":
return(TableQuery.GenerateFilterConditionForLong(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(Int64)Convert.ChangeType(_skipValue, typeof(Int64))));
case "system.string":
case "string":
return(TableQuery.GenerateFilterCondition(
_skipPropertyName, QueryComparisons.GreaterThanOrEqual,
(String)Convert.ChangeType(_skipValue, typeof(String))));
default:
throw new Exception("The skip value type is not supported.");
}
}
/// <summary>
/// Shortcut method that queries the table based on a given PartitionKey and given property with
/// the same property name. Handles the continuation token scenario as well. Overloaded to accept
/// all appropriate table entity types.
/// </summary>
/// <param name="partitionKey"></param>
/// <param name="propertyName"></param>
/// <param name="property"></param>
/// <returns></returns>
public IEnumerable <TAzureTableEntity> WherePropertyEquals(string partitionKey, string propertyName, double property)
{
var propertyFilter = TableQuery.GenerateFilterConditionForDouble(propertyName, QueryComparisons.Equal,
property);
var query = PartitionKeyAndPropertyFilterQuery(partitionKey, propertyFilter);
return(RunQuerySegment(query));
}
public TableQuery GetSourceTableQuery()
{
//Get all User key records
TableQuery tq = new TableQuery();
string keyVersionFilter = TableQuery.GenerateFilterConditionForDouble("KeyVersion", QueryComparisons.LessThan, KeyHelper.KeyVersion);
tq.FilterString = keyVersionFilter;
return(tq);
}
public async Task UpdateLeaderboards(IBinder binder, ILogger log)
{
var table = await binder.BindAsync <CloudTable>(new TableAttribute(PLAYER_ENTITY_TABLE));
var collectionGlobal = new List <PlayerEntity>();
var regions = metaClient.GetRegions();
foreach (var region in regions)
{
var filters = new List <string>()
{
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, $"{region.Key}"),
TableQuery.GenerateFilterConditionForDouble("Ranking", QueryComparisons.GreaterThan, LEADERBOARD_RANK_CUTOFF),
TableQuery.GenerateFilterConditionForBool("Calibrated", QueryComparisons.Equal, true)
};
var filter = string.Empty;
foreach (var f in filters)
{
filter = string.IsNullOrEmpty(filter) ? f : TableQuery.CombineFilters(filter, TableOperators.And, f);
}
var query = new TableQuery <PlayerEntity>().Where(filter).Take(1000);
var collectionRegional = new List <PlayerEntity>();
TableContinuationToken token = null;
do
{
var segment = await table.ExecuteQuerySegmentedAsync(query, token);
token = segment.ContinuationToken;
collectionRegional = collectionRegional
.Union(segment.Results)
.OrderByDescending(_ => _.Ranking)
.Take(100)
.ToList();
collectionGlobal = collectionGlobal
.Union(segment.Results)
.OrderByDescending(_ => _.Ranking)
.Take(100)
.ToList();
}while (token != null);
await UpdateLeaderboard(region.Key, collectionRegional, binder, log);
}
var distinctCollectionGlobal = collectionGlobal
.GroupBy(p => p.RowKey)
.Select(g => g.OrderByDescending(_ => _.Ranking).First())
.ToList();
await UpdateLeaderboard(GLOBAL_LEADERBOARD_REGION, distinctCollectionGlobal, binder, log);
}
/// <summary>
/// Converts the filters to table query.
/// </summary>
/// <param name="filters">The filters.</param>
/// <returns>returns filter string.</returns>
public string ConvertFiltersToTableQuery(object filters)
{
var finalQuery = string.Empty;
if (filters == null)
{
return(finalQuery);
}
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(filters);
foreach (PropertyDescriptor prop in props)
{
var filterQuery = string.Empty;
if (prop.PropertyType == typeof(bool))
{
filterQuery = TableQuery.GenerateFilterConditionForBool(prop.Name, QueryComparisons.Equal, (bool)prop.GetValue(filters));
}
else if (prop.PropertyType == typeof(DateTime))
{
filterQuery = TableQuery.GenerateFilterConditionForDate(prop.Name, QueryComparisons.Equal, (DateTime)prop.GetValue(filters));
}
else if (prop.PropertyType == typeof(double))
{
filterQuery = TableQuery.GenerateFilterConditionForDouble(prop.Name, QueryComparisons.Equal, (double)prop.GetValue(filters));
}
else if (prop.PropertyType == typeof(int))
{
filterQuery = TableQuery.GenerateFilterConditionForInt(prop.Name, QueryComparisons.Equal, (int)prop.GetValue(filters));
}
else if (prop.PropertyType == typeof(long))
{
filterQuery = TableQuery.GenerateFilterConditionForLong(prop.Name, QueryComparisons.Equal, (long)prop.GetValue(filters));
}
else if (prop.PropertyType == typeof(Guid))
{
filterQuery = TableQuery.GenerateFilterConditionForGuid(prop.Name, QueryComparisons.Equal, (Guid)prop.GetValue(filters));
}
else
{
filterQuery = TableQuery.GenerateFilterCondition(prop.Name, QueryComparisons.Equal, (string)prop.GetValue(filters));
}
if (string.IsNullOrEmpty(finalQuery))
{
finalQuery = filterQuery;
}
else
{
finalQuery = TableQuery.CombineFilters(finalQuery, TableOperators.And, filterQuery);
}
}
return(finalQuery);
}
public List <TableDeviceReading> ReadItemCollection()
{
TableQuery <TableDeviceReading> rangeQuery = new TableQuery <TableDeviceReading>().Where(
TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("MetricType", QueryComparisons.Equal, "Temperature"),
TableOperators.And,
TableQuery.GenerateFilterConditionForDouble("MetricValue", QueryComparisons.GreaterThanOrEqual, 100.00)));
return(table.ExecuteQuery(rangeQuery).ToList <TableDeviceReading>());
}
/// <summary>
/// Get async.
/// </summary>
/// <param name="value">The value.</param>
/// <param name="propertyName">The property name.</param>
/// <param name="operation">The operation.</param>
/// <param name="take">The number of items to return.</param>
/// <returns>The array or items.</returns>
public async Task <T[]> GetAsync(double value, string propertyName = "PartitionKey",
string operation = QueryComparisons.Equal, int take = 100)
{
// Create the query.
TableQuery <T> scanQuery = new TableQuery <T>().Where
(TableQuery.GenerateFilterConditionForDouble(propertyName, operation, value));
// Return the array
return(await ScanAsyncEx(scanQuery, take));
}
DictionaryTableEntity GetDictionaryTableEntity(Type type, string property, object value)
{
var tableName = type.Name;
var table = client.GetTableReference(tableName);
TableQuery <DictionaryTableEntity> query;
var propertyInfo = type.GetProperty(property);
if (propertyInfo.PropertyType == typeof(byte[]))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBinary(property, QueryComparisons.Equal, (byte[])value));
}
else if (propertyInfo.PropertyType == typeof(bool))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBool(property, QueryComparisons.Equal, (bool)value));
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDate(property, QueryComparisons.Equal, (DateTime)value));
}
else if (propertyInfo.PropertyType == typeof(Guid))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForGuid(property, QueryComparisons.Equal, (Guid)value));
}
else if (propertyInfo.PropertyType == typeof(Int32))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForInt(property, QueryComparisons.Equal, (int)value));
}
else if (propertyInfo.PropertyType == typeof(Int64))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForLong(property, QueryComparisons.Equal, (long)value));
}
else if (propertyInfo.PropertyType == typeof(Double))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDouble(property, QueryComparisons.Equal, (double)value));
}
else if (propertyInfo.PropertyType == typeof(string))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterCondition(property, QueryComparisons.Equal, (string)value));
}
else
{
throw new NotSupportedException(
string.Format("The property type '{0}' is not supported in windows azure table storage",
propertyInfo.PropertyType.Name));
}
var tableEntity = table.ExecuteQuery(query).SafeFirstOrDefault();
return(tableEntity);
}
private string GenerateFilterCondition(string column, Filter.ECompare filterOperator, ETypeCode compareDataType, object value)
{
string filterString;
switch (compareDataType)
{
case ETypeCode.String:
case ETypeCode.Text:
case ETypeCode.Json:
case ETypeCode.Xml:
case ETypeCode.Guid:
case ETypeCode.Unknown:
filterString = TableQuery.GenerateFilterCondition(column, ConvertOperator(filterOperator), (string)value);
break;
case ETypeCode.Boolean:
filterString = TableQuery.GenerateFilterConditionForBool(column, ConvertOperator(filterOperator), (bool)value);
break;
case ETypeCode.Int16:
case ETypeCode.Int32:
case ETypeCode.UInt16:
case ETypeCode.UInt32:
filterString = TableQuery.GenerateFilterConditionForInt(column, ConvertOperator(filterOperator), (int)value);
break;
case ETypeCode.UInt64:
case ETypeCode.Int64:
filterString = TableQuery.GenerateFilterConditionForLong(column, ConvertOperator(filterOperator), (long)value);
break;
case ETypeCode.DateTime:
filterString = TableQuery.GenerateFilterConditionForDate(column, ConvertOperator(filterOperator), (DateTime)value);
break;
case ETypeCode.Time:
filterString = TableQuery.GenerateFilterCondition(column, ConvertOperator(filterOperator), value.ToString());
break;
case ETypeCode.Double:
case ETypeCode.Decimal:
filterString = TableQuery.GenerateFilterConditionForDouble(column, ConvertOperator(filterOperator), (double)value);
break;
default:
throw new Exception("The data type: " + compareDataType.ToString() + " is not supported by Azure table storage.");
}
return(filterString);
}
public static TableQuery <DictionaryTableEntity> BuildWherePropertyQuery(Type type, string property, object value)
{
TableQuery <DictionaryTableEntity> query;
var propertyInfo = type.GetProperty(property);
if (propertyInfo == null)
{
return(null);
}
if (propertyInfo.PropertyType == typeof(byte[]))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBinary(property, QueryComparisons.Equal, (byte[])value));
}
else if (propertyInfo.PropertyType == typeof(bool))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBool(property, QueryComparisons.Equal, (bool)value));
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDate(property, QueryComparisons.Equal, (DateTime)value));
}
else if (propertyInfo.PropertyType == typeof(Guid))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForGuid(property, QueryComparisons.Equal, (Guid)value));
}
else if (propertyInfo.PropertyType == typeof(int))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForInt(property, QueryComparisons.Equal, (int)value));
}
else if (propertyInfo.PropertyType == typeof(long))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForLong(property, QueryComparisons.Equal, (long)value));
}
else if (propertyInfo.PropertyType == typeof(double))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDouble(property, QueryComparisons.Equal, (double)value));
}
else if (propertyInfo.PropertyType == typeof(string))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterCondition(property, QueryComparisons.Equal, (string)value));
}
else
{
throw new NotSupportedException($"The property type '{propertyInfo.PropertyType.Name}' is not supported in Azure Table Storage");
}
return(query);
}
private void Map(IEnumerable <Criteria> criterias, List <string> filters)
{
foreach (var criteria in criterias.Safe())
{
if (criteria.Value is int i)
{
filters.Add(TableQuery.GenerateFilterConditionForInt(
criteria.Name,
criteria.Operator.ToAbbreviation(),
i));
}
else if (criteria.Value is double d)
{
filters.Add(TableQuery.GenerateFilterConditionForDouble(
criteria.Name,
criteria.Operator.ToAbbreviation(),
d));
}
else if (criteria.Value is long l)
{
filters.Add(TableQuery.GenerateFilterConditionForLong(
criteria.Name,
criteria.Operator.ToAbbreviation(),
l));
}
else if (criteria.Value is bool b)
{
filters.Add(TableQuery.GenerateFilterConditionForBool(
criteria.Name,
criteria.Operator.ToAbbreviation(),
b));
}
else if (criteria.Value is DateTime dt)
{
filters.Add(TableQuery.GenerateFilterConditionForDate(
criteria.Name,
criteria.Operator.ToAbbreviation(),
dt));
}
else
{
filters.Add(TableQuery.GenerateFilterCondition(
criteria.Name,
criteria.Operator.ToAbbreviation(),
WebUtility.UrlEncode(criteria.Value.As <string>())));
}
}
}
private static string GenerateFilter(string left, string op, object value)
{
return(value switch
{
null => throw new ArgumentNullException($"Null values comparison is not supported: {left}"),
string s => TableQuery.GenerateFilterCondition(left, op, s),
bool b => TableQuery.GenerateFilterConditionForBool(left, op, b),
DateTime d => TableQuery.GenerateFilterConditionForDate(left, op, d),
DateTimeOffset d => TableQuery.GenerateFilterConditionForDate(left, op, d),
double d => TableQuery.GenerateFilterConditionForDouble(left, op, d),
float f => TableQuery.GenerateFilterConditionForDouble(left, op, f),
byte i => TableQuery.GenerateFilterConditionForInt(left, op, i),
short i => TableQuery.GenerateFilterConditionForInt(left, op, i),
int i => TableQuery.GenerateFilterConditionForInt(left, op, i),
long l => TableQuery.GenerateFilterConditionForLong(left, op, l),
_ => throw new InvalidOperationException($"Type is not supported: {value.GetType()}")
});
private List <DynamicTableEntity> GetUserEntitiesBySourceId(string userPartitionKey, IdentityCloudContext sourcesContext)
{
List <DynamicTableEntity> results = new List <DynamicTableEntity>(1000);
TableQuery tq = new TableQuery();
string partitionKeyFilter = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, userPartitionKey);
string keyVersionFilter = TableQuery.GenerateFilterConditionForDouble("KeyVersion", QueryComparisons.LessThan, KeyHelper.KeyVersion);
tq.FilterString = TableQuery.CombineFilters(partitionKeyFilter, TableOperators.And, keyVersionFilter);
TableContinuationToken token = new TableContinuationToken();
while (token != null)
{
var r = sourcesContext.UserTable.ExecuteQuerySegmented(tq, token);
token = r.ContinuationToken;
results.AddRange(r.Results);
}
return(results);
}
protected override string ExpressionFilterConverter(IFilterExpression <T> expression)
{
if (expression.PropertyType == typeof(byte[]))
{
return(TableQuery.GenerateFilterConditionForBinary(expression.PropertyName, GetInstruction(expression.Comparator), (byte[])expression.PropertyValue));
}
if (expression.PropertyType == typeof(bool) || expression.PropertyType == typeof(bool?))
{
return(TableQuery.GenerateFilterConditionForBool(expression.PropertyName, GetInstruction(expression.Comparator), (bool)expression.PropertyValue));
}
if (expression.PropertyType == typeof(DateTime) || expression.PropertyType == typeof(DateTime?))
{
return(TableQuery.GenerateFilterConditionForDate(expression.PropertyName, GetInstruction(expression.Comparator), (DateTime)expression.PropertyValue));
}
if (expression.PropertyType == typeof(DateTimeOffset) || expression.PropertyType == typeof(DateTimeOffset?))
{
return(TableQuery.GenerateFilterConditionForDate(expression.PropertyName, GetInstruction(expression.Comparator), (DateTimeOffset)expression.PropertyValue));
}
if (expression.PropertyType == typeof(double) || expression.PropertyType == typeof(double?))
{
return(TableQuery.GenerateFilterConditionForDouble(expression.PropertyName, GetInstruction(expression.Comparator), (double)expression.PropertyValue));
}
if (expression.PropertyType == typeof(Guid) || expression.PropertyType == typeof(Guid?))
{
return(TableQuery.GenerateFilterConditionForGuid(expression.PropertyName, GetInstruction(expression.Comparator), (Guid)expression.PropertyValue));
}
if (expression.PropertyType == typeof(int) || expression.PropertyType == typeof(int?))
{
return(TableQuery.GenerateFilterConditionForInt(expression.PropertyName, GetInstruction(expression.Comparator), (int)expression.PropertyValue));
}
if (expression.PropertyType == typeof(long) || expression.PropertyType == typeof(long?))
{
return(TableQuery.GenerateFilterConditionForLong(expression.PropertyName, GetInstruction(expression.Comparator), (long)expression.PropertyValue));
}
return(TableQuery.GenerateFilterCondition(expression.PropertyName, GetInstruction(expression.Comparator), expression.PropertyValue?.ToString() ?? string.Empty));
}
private string GenerateFilterCondition(string key, object value)
{
if (value is string)
{
return(TableQuery.GenerateFilterCondition(key, QueryComparisons.Equal, (string)value));
}
if (value is Guid)
{
return(TableQuery.GenerateFilterConditionForGuid(key, QueryComparisons.Equal, (Guid)value));
}
if (value is byte || value is short || value is int)
{
return(TableQuery.GenerateFilterConditionForInt(key, QueryComparisons.Equal, (int)value));
}
if (value is long)
{
return(TableQuery.GenerateFilterConditionForLong(key, QueryComparisons.Equal, (long)value));
}
if (value is bool)
{
return(TableQuery.GenerateFilterConditionForBool(key, QueryComparisons.Equal, (bool)value));
}
if (value is DateTimeOffset)
{
return(TableQuery.GenerateFilterConditionForDate(key, QueryComparisons.Equal, (DateTimeOffset)value));
}
if (value is DateTime)
{
return(TableQuery.GenerateFilterConditionForDate(key, QueryComparisons.Equal, new DateTimeOffset((DateTime)value)));
}
if (value is float || value is double)
{
return(TableQuery.GenerateFilterConditionForDouble(key, QueryComparisons.Equal, (double)value));
}
throw new NotSupportedException();
}
public string GenerateFilter(string propertyName, object propertyValue, string operatorString)
{
var propertyType = propertyValue.GetType();
if (propertyType == typeof(string))
{
return(TableQuery.GenerateFilterCondition(propertyName, operatorString, propertyValue as string));
}
if (propertyType == typeof(DateTime))
{
return(TableQuery.GenerateFilterConditionForDate(propertyName, operatorString, (DateTime)propertyValue));
}
if (propertyType == typeof(bool))
{
return(TableQuery.GenerateFilterConditionForBool(propertyName, operatorString, (bool)propertyValue));
}
if (propertyType == typeof(double))
{
return(TableQuery.GenerateFilterConditionForDouble(propertyName, operatorString, (double)propertyValue));
}
if (propertyType == typeof(int))
{
return(TableQuery.GenerateFilterConditionForInt(propertyName, operatorString, (int)propertyValue));
}
if (propertyType == typeof(long))
{
return(TableQuery.GenerateFilterConditionForLong(propertyName, operatorString, (long)propertyValue));
}
if (propertyType == typeof(Guid))
{
return(TableQuery.GenerateFilterConditionForGuid(propertyName, operatorString, (Guid)propertyValue));
}
return(propertyType == typeof(byte[]) ?
TableQuery.GenerateFilterConditionForBinary(propertyName, operatorString, (byte[])propertyValue) :
TableQuery.GenerateFilterCondition(propertyName, operatorString, propertyValue.ToString()));
}
static AzureConditionBuilderExtention()
{
ToAzureType = new Dictionary <Type, Func <object, string> >();
ToAzureType[typeof(string)] = (x) => TableQuery.GenerateFilterCondition("", "", x.ToString());
ToAzureType[typeof(bool)] = (x) => TableQuery.GenerateFilterConditionForBool("", "", (bool)x);
ToAzureType[typeof(DateTime)] = (x) => TableQuery.GenerateFilterConditionForDate("", "", ((DateTime)x));
ToAzureType[typeof(double)] = (x) => TableQuery.GenerateFilterConditionForDouble("", "", (double)x);
ToAzureType[typeof(Guid)] = (x) => TableQuery.GenerateFilterConditionForGuid("", "", (Guid)x);
ToAzureType[typeof(int)] = (x) => TableQuery.GenerateFilterConditionForInt("", "", (int)x);
ToAzureType[typeof(long)] = (x) => TableQuery.GenerateFilterConditionForLong("", "", (long)x);
ToAzureType[typeof(byte[])] = (x) => TableQuery.GenerateFilterConditionForBinary("", "", (byte[])x);
ToAzureCondition = new Dictionary <string, string>();
ToAzureCondition["=="] = QueryComparisons.Equal;
ToAzureCondition[">"] = QueryComparisons.GreaterThan;
ToAzureCondition[">="] = QueryComparisons.GreaterThanOrEqual;
ToAzureCondition["<"] = QueryComparisons.LessThan;
ToAzureCondition["<="] = QueryComparisons.LessThanOrEqual;
ToAzureCondition["!="] = QueryComparisons.NotEqual;
ToAzureCondition["AndAlso"] = TableOperators.And;
ToAzureCondition["OrElse"] = TableOperators.Or;
ToAzureCondition["Not"] = TableOperators.Not;
}
public TableQuery GetSourceTableQuery()
{
//Get all User key records
TableQuery tq = new TableQuery();
tq.SelectColumns = new List <string>()
{
"PartitionKey", "RowKey", "KeyVersion"
};
string partitionFilter = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, _keyHelper.PreFixIdentityUserId),
TableOperators.And,
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, _keyHelper.PreFixIdentityUserIdUpperBound));
string rowFilter = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual, _keyHelper.PreFixIdentityUserId),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, _keyHelper.PreFixIdentityUserIdUpperBound));
string keyVersionFilter = TableQuery.GenerateFilterConditionForDouble("KeyVersion", QueryComparisons.LessThan, _keyHelper.KeyVersion);
string keysFilter = TableQuery.CombineFilters(partitionFilter, TableOperators.And, rowFilter);
tq.FilterString = TableQuery.CombineFilters(keysFilter, TableOperators.And, keyVersionFilter);
return(tq);
}
static string GenerateFilterCondition(BinaryExpression binary)
{
var left = binary.Left as MemberExpression;
if (left == null)
{
throw new InvalidOperationException();
}
var op = ToComparisonOperator(binary.NodeType);
var rightValue = binary.Right.Invoke();
return
(left.Type == typeof(byte[]) ? TableQuery.GenerateFilterConditionForBinary(left.Member.Name, op, (byte[])rightValue) :
left.Type == typeof(bool) ? TableQuery.GenerateFilterConditionForBool(left.Member.Name, op, (bool)rightValue) :
left.Type == typeof(DateTime) ? TableQuery.GenerateFilterConditionForDate(left.Member.Name, op, (DateTime)rightValue) :
left.Type == typeof(DateTimeOffset) ? TableQuery.GenerateFilterConditionForDate(left.Member.Name, op, (DateTimeOffset)rightValue) :
left.Type == typeof(double) ? TableQuery.GenerateFilterConditionForDouble(left.Member.Name, op, (double)rightValue) :
left.Type == typeof(Guid) ? TableQuery.GenerateFilterConditionForGuid(left.Member.Name, op, (Guid)rightValue) :
left.Type == typeof(int) ? TableQuery.GenerateFilterConditionForInt(left.Member.Name, op, (int)rightValue) :
left.Type == typeof(long) ? TableQuery.GenerateFilterConditionForLong(left.Member.Name, op, (long)rightValue) :
TableQuery.GenerateFilterCondition(left.Member.Name, op, rightValue.To <string>()));
}
public void TableGenericQueryOnSupportedTypes()
{
// 1. Filter on String
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterCondition("String", QueryComparisons.GreaterThanOrEqual, "0050"), Midpoint);
// 2. Filter on Guid
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForGuid("Guid", QueryComparisons.Equal, this.middleRef.Guid), 1);
// 3. Filter on Long
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForLong("Int64", QueryComparisons.GreaterThanOrEqual,
this.middleRef.LongPrimitive), Midpoint);
ExecuteQueryAndAssertResults(this.currentTable, TableQuery.GenerateFilterConditionForLong("LongPrimitive",
QueryComparisons.GreaterThanOrEqual, this.middleRef.LongPrimitive), Midpoint);
// 4. Filter on Double
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForDouble("Double", QueryComparisons.GreaterThanOrEqual,
this.middleRef.Double), Midpoint);
ExecuteQueryAndAssertResults(this.currentTable, TableQuery.GenerateFilterConditionForDouble("DoublePrimitive",
QueryComparisons.GreaterThanOrEqual, this.middleRef.DoublePrimitive), Midpoint);
// 5. Filter on Integer
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForInt("Int32", QueryComparisons.GreaterThanOrEqual,
this.middleRef.Int32), Midpoint);
ExecuteQueryAndAssertResults(this.currentTable, TableQuery.GenerateFilterConditionForInt("IntegerPrimitive",
QueryComparisons.GreaterThanOrEqual, middleRef.IntegerPrimitive), Midpoint);
// 6. Filter on Date
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForDate("DateTimeOffset", QueryComparisons.GreaterThanOrEqual,
this.middleRef.DateTimeOffset), Midpoint);
// 7. Filter on Boolean
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForBool("Bool", QueryComparisons.Equal, this.middleRef.Bool), Midpoint);
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForBool("BoolPrimitive", QueryComparisons.Equal, this.middleRef.BoolPrimitive),
Midpoint);
// 8. Filter on Binary
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForBinary("Binary", QueryComparisons.Equal, this.middleRef.Binary), 1);
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForBinary("BinaryPrimitive", QueryComparisons.Equal,
middleRef.BinaryPrimitive), 1);
// 9. Filter on Binary GTE
ExecuteQueryAndAssertResults(this.currentTable,
TableQuery.GenerateFilterConditionForBinary("Binary", QueryComparisons.GreaterThanOrEqual,
this.middleRef.Binary), Midpoint);
ExecuteQueryAndAssertResults(this.currentTable, TableQuery.GenerateFilterConditionForBinary("BinaryPrimitive",
QueryComparisons.GreaterThanOrEqual, this.middleRef.BinaryPrimitive), Midpoint);
// 10. Complex Filter on Binary GTE
ExecuteQueryAndAssertResults(this.currentTable, TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal,
this.middleRef.PartitionKey),
TableOperators.And,
TableQuery.GenerateFilterConditionForBinary("Binary", QueryComparisons.GreaterThanOrEqual,
this.middleRef.Binary)), Midpoint);
ExecuteQueryAndAssertResults(this.currentTable, TableQuery.GenerateFilterConditionForBinary("BinaryPrimitive",
QueryComparisons.GreaterThanOrEqual, this.middleRef.BinaryPrimitive), Midpoint);
}
T ISagaPersister.Get <T>(string property, object value)
{
var type = typeof(T);
var tableName = type.Name;
var table = client.GetTableReference(tableName);
TableQuery <DictionaryTableEntity> query;
var propertyInfo = type.GetProperty(property);
if (propertyInfo.PropertyType == typeof(byte[]))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBinary(property, QueryComparisons.Equal, (byte[])value));
}
else if (propertyInfo.PropertyType == typeof(bool))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForBool(property, QueryComparisons.Equal, (bool)value));
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDate(property, QueryComparisons.Equal, (DateTime)value));
}
else if (propertyInfo.PropertyType == typeof(Guid))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForGuid(property, QueryComparisons.Equal, (Guid)value));
}
else if (propertyInfo.PropertyType == typeof(Int32))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForInt(property, QueryComparisons.Equal, (int)value));
}
else if (propertyInfo.PropertyType == typeof(Int64))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForLong(property, QueryComparisons.Equal, (long)value));
}
else if (propertyInfo.PropertyType == typeof(Double))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterConditionForDouble(property, QueryComparisons.Equal, (double)value));
}
else if (propertyInfo.PropertyType == typeof(string))
{
query = new TableQuery <DictionaryTableEntity>().Where(TableQuery.GenerateFilterCondition(property, QueryComparisons.Equal, (string)value));
}
else
{
throw new NotSupportedException(
string.Format("The property type '{0}' is not supported in windows azure table storage",
propertyInfo.PropertyType.Name));
}
try
{
return(ToEntity <T>(table.ExecuteQuery(query).FirstOrDefault()));
}
catch (WebException ex)
{
// occurs when table has not yet been created, but already looking for absence of instance
if (ex.Status == WebExceptionStatus.ProtocolError && ex.Response != null)
{
var response = (HttpWebResponse)ex.Response;
if (response.StatusCode == HttpStatusCode.NotFound)
{
return(default(T));
}
}
throw;
}
}
private string BuildFilerQuery()
{
string queryFilters = null;
foreach (var filter in Filters)
{
var fieldName = _entityDefinition.Filters().FirstOrDefault(f => f.Accesor.MemberPath == filter.EntityMember.MemberPath)?.Name;
if (String.IsNullOrWhiteSpace(fieldName))
{
throw new StorageArgumentOutOfRangeException(nameof(filter), $"Not found filter with signature {filter.EntityMember.MemberPath}");
}
string queryFilter = null;
var toCompare = filter.ConstantMember.Value;
switch (Type.GetTypeCode(toCompare.GetType()))
{
case TypeCode.Boolean:
queryFilter = TableQuery.GenerateFilterConditionForBool(fieldName, GetOperator(filter.Operator), (bool)toCompare);
break;
case TypeCode.SByte:
case TypeCode.Byte:
case TypeCode.Int16:
case TypeCode.UInt16:
case TypeCode.Int32:
case TypeCode.UInt32:
queryFilter = TableQuery.GenerateFilterConditionForInt(fieldName, GetOperator(filter.Operator), (int)toCompare);
break;
case TypeCode.Int64:
case TypeCode.UInt64:
queryFilter = TableQuery.GenerateFilterConditionForLong(fieldName, GetOperator(filter.Operator), (long)toCompare);
break;
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
queryFilter = TableQuery.GenerateFilterConditionForDouble(fieldName, GetOperator(filter.Operator), (double)toCompare);
break;
case TypeCode.DateTime:
queryFilter = TableQuery.GenerateFilterConditionForDate(fieldName, GetOperator(filter.Operator), (DateTime)toCompare);
break;
case TypeCode.Char:
case TypeCode.String:
queryFilter = TableQuery.GenerateFilterCondition(fieldName, GetOperator(filter.Operator), toCompare.ToString());
break;
default:
throw new StorageArgumentOutOfRangeException(nameof(filter), $"Can not extract value for type '{toCompare.GetType().FullName}'");
}
if (String.IsNullOrWhiteSpace(queryFilters))
{
queryFilters = queryFilter;
}
else
{
queryFilters = TableQuery.CombineFilters(queryFilters, TableOperators.And, queryFilter);
}
}
return(queryFilters);
}
private string GenerateFilterCondition(DataTable schema, string columnName, string columnValue)
{
if (columnName.Equals(AZURE_TABLE_PARTITION_KEY_COL))
{
return(TableQuery.GenerateFilterCondition(columnName, QueryComparisons.GreaterThan, columnValue));
}
else if (columnName.Equals(AZURE_TABLE_ROW_KEY_COL))
{
return(TableQuery.GenerateFilterCondition(columnName, QueryComparisons.GreaterThan, columnValue));
}
else if (columnName.Equals(AZURE_TABLE_TIMESTAMP_COL))
{
return(TableQuery.GenerateFilterConditionForDate(columnName, QueryComparisons.GreaterThan, DateTimeOffset.Parse(columnValue)));
}
else if (!schema.Columns.Contains(columnName))
{
return(String.Empty);
}
else
{
Type type = schema.Columns[columnName].DataType;
if (type == typeof(Byte[]))
{
return(String.Empty);
}
else if (type == typeof(Boolean))
{
return(TableQuery.GenerateFilterConditionForBool(columnName, QueryComparisons.GreaterThan, Boolean.Parse(columnValue)));
}
else if (type == typeof(Int32))
{
return(TableQuery.GenerateFilterConditionForInt(columnName, QueryComparisons.GreaterThan, Int32.Parse(columnValue)));
}
else if (type == typeof(Int64))
{
return(TableQuery.GenerateFilterConditionForLong(columnName, QueryComparisons.GreaterThan, Int64.Parse(columnValue)));
}
else if (type == typeof(DateTime))
{
return(TableQuery.GenerateFilterConditionForDate(columnName, QueryComparisons.GreaterThan, DateTime.Parse(columnValue)));
}
else if (type == typeof(DateTimeOffset))
{
return(TableQuery.GenerateFilterConditionForDate(columnName, QueryComparisons.GreaterThan, DateTimeOffset.Parse(columnValue)));
}
else if (type == typeof(Double))
{
return(TableQuery.GenerateFilterConditionForDouble(columnName, QueryComparisons.GreaterThan, Double.Parse(columnValue)));
}
else if (type == typeof(Guid))
{
return(TableQuery.GenerateFilterConditionForGuid(columnName, QueryComparisons.GreaterThan, Guid.Parse(columnValue)));
}
else if (type == typeof(String))
{
return(TableQuery.GenerateFilterCondition(columnName, QueryComparisons.GreaterThan, columnValue));
}
else
{
return(String.Empty);
}
}
}
public static string FilterString(string propertyName, string queryComparison, object value)
{
var filterString = "";
var val = "";
if (value != null)
{
val = value.GetType().ToString().Replace("System.", "");
}
switch (val)
{
//case "":
// filterString = TableQuery.GenerateFilterCondition(property, queryComparison, "");
// break;
case "Byte[]":
filterString = TableQuery.GenerateFilterConditionForBinary(propertyName, queryComparison, (byte[])value);
break;
case "Boolean":
filterString = TableQuery.GenerateFilterConditionForBool(propertyName, queryComparison, (bool)value);
break;
case "DateTime":
filterString = TableQuery.GenerateFilterConditionForDate(propertyName, queryComparison, (DateTime)value);
break;
case "Double":
filterString = TableQuery.GenerateFilterConditionForDouble(propertyName, queryComparison, (double)value);
break;
case "Guid":
filterString = TableQuery.GenerateFilterConditionForGuid(propertyName, queryComparison, (Guid)value);
break;
case "Int32":
filterString = TableQuery.GenerateFilterConditionForInt(propertyName, queryComparison, (int)value);
break;
case "Int64":
filterString = TableQuery.GenerateFilterConditionForLong(propertyName, queryComparison, (long)value);
break;
default:
//if (Utils.IsRowKey(property))
// filterString = TableQuery.GenerateFilterCondition("RowKey", queryComparison, (String)value);
//else
//if (Utils.IsPartitionKey(property))
// filterString = TableQuery.GenerateFilterCondition("PartitionKey", queryComparison, (String)value);
//else
filterString = TableQuery.GenerateFilterCondition(propertyName, queryComparison, (String)value);
break;
}
//Event("Type Not Handled error on QueryOn");
return(filterString);
}
public override string ToString()
{
var filterOperation = QueryComparisons.Equal;
switch (Operator)
{
case QueryFilterOperator.Equal:
filterOperation = QueryComparisons.Equal;
break;
case QueryFilterOperator.NotEqual:
filterOperation = QueryComparisons.NotEqual;
break;
case QueryFilterOperator.Lower:
filterOperation = QueryComparisons.LessThan;
break;
case QueryFilterOperator.Greater:
filterOperation = QueryComparisons.GreaterThan;
break;
case QueryFilterOperator.LowerEqual:
filterOperation = QueryComparisons.LessThanOrEqual;
break;
case QueryFilterOperator.GreaterEqual:
filterOperation = QueryComparisons.GreaterThanOrEqual;
break;
}
if (Value is string)
{
return(TableQuery.GenerateFilterCondition(Property, filterOperation, (string)Value));
}
if (Value is bool)
{
return(TableQuery.GenerateFilterConditionForBool(Property, filterOperation, (bool)Value));
}
if (Value is byte[])
{
return(TableQuery.GenerateFilterConditionForBinary(Property, filterOperation, (byte[])Value));
}
if (Value is DateTimeOffset)
{
return(TableQuery.GenerateFilterConditionForDate(Property, filterOperation, (DateTimeOffset)Value));
}
if (Value is double)
{
return(TableQuery.GenerateFilterConditionForDouble(Property, filterOperation, (double)Value));
}
if (Value is Guid)
{
return(TableQuery.GenerateFilterConditionForGuid(Property, filterOperation, (Guid)Value));
}
if (Value is int)
{
return(TableQuery.GenerateFilterConditionForInt(Property, filterOperation, (int)Value));
}
if (Value is long)
{
return(TableQuery.GenerateFilterConditionForLong(Property, filterOperation, (long)Value));
}
throw new NotSupportedException($"QueryFilter of Type \"{Value?.GetType().FullName}\" is not supported.");
}
