public async Task <List <string> > GetClientIdsAsync(ulong discordUserId)
{
await table.CreateIfNotExistsAsync();
var result = table.ExecuteQuery(
new TableQuery <ConnectionEntity>()
.Where(TableQuery.GenerateFilterConditionForLong(nameof(ConnectionEntity.UserId), QueryComparisons.Equal, (long)discordUserId)));
return(result.Select(o => o.RowKey).ToList());
}
/// <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(long value, string propertyName = "PartitionKey",
string operation = QueryComparisons.Equal, int take = 100)
{
// Create the query.
TableQuery <T> scanQuery = new TableQuery <T>().Where
(TableQuery.GenerateFilterConditionForLong(propertyName, operation, value));
// Return the array
return(await ScanAsyncEx(scanQuery, take));
}
public async Task <IEnumerable <SourcedEvent> > ReadEvents(string aggregateId, ulong fromVersion)
{
var tableQuery = new TableQuery <TableEventModel>().Where(
TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, aggregateId),
TableOperators.And,
TableQuery.GenerateFilterConditionForLong("EventVersion", QueryComparisons.GreaterThanOrEqual, (long)fromVersion)
));
return(await PullEvents(tableQuery));
}
async public Task <IList <DrawEntity> > GetPopularDraws(int minDrawCount = 1)
{
var drawTable = await GetDrawTable();
var tableContinutionToken = new TableContinuationToken();
var popularDrawsQuery = new TableQuery <DrawEntity>()
.Where(TableQuery.GenerateFilterConditionForLong("UseCount", QueryComparisons.GreaterThanOrEqual, minDrawCount));
var popularDraws = await drawTable.ExecuteQuerySegmentedAsync(popularDrawsQuery, tableContinutionToken);
var sortedPopularDraws = popularDraws.Results.OrderByDescending(d => d.UseCount).ToList();
return(sortedPopularDraws);
}
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);
}
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 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);
}
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 TableQuery <SnapshotEntry> BuildSnapshotTableQuery(string persistenceId, SnapshotSelectionCriteria criteria)
{
string comparsion = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, persistenceId),
TableOperators.And,
TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThanOrEqual, SnapshotEntry.ToRowKey(criteria.MaxSequenceNr)));
if (criteria.MaxTimeStamp != DateTime.MinValue && criteria.MaxTimeStamp != DateTime.MaxValue)
{
comparsion = TableQuery.CombineFilters(
comparsion,
TableOperators.And,
TableQuery.GenerateFilterConditionForLong("SnapshotTimestamp", QueryComparisons.LessThanOrEqual, criteria.MaxTimeStamp.Ticks));
}
return(new TableQuery <SnapshotEntry>().Where(comparsion));
}
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 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();
}
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));
}
public List <Incident> GetAllIncidentsDataByFilter(string startDate, string endDate)
{
long startTicks = Convert.ToInt64(startDate);
long endTicks = Convert.ToInt64(endDate);
string filterTimeStamp = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForLong("DateTime", QueryComparisons.GreaterThanOrEqual, startTicks),
TableOperators.And,
TableQuery.GenerateFilterConditionForLong("DateTime", QueryComparisons.LessThanOrEqual, endTicks)
);
TableQuery <Incident> UQuery = new TableQuery <Incident>().Where(filterTimeStamp);
base.LoadTable(Constants.IncidentsTableName);
List <Incident> qryReturn = base.EntityTable.ExecuteQuery(UQuery).ToList();
return(qryReturn != null && qryReturn.Count > 0 ? qryReturn : null);
}
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;
}
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 List <LocationHistory> GetLocationHistory(string profileID, DateTime startDate, DateTime endDate)
{
if (string.IsNullOrEmpty(profileID))
{
return(null);
}
long startTicks = startDate.Ticks;
long endTicks = endDate.Ticks;
string filterPartitionKey = TableQuery.GenerateFilterCondition(Constants.PartitionKey, QueryComparisons.Equal, profileID.ToString());
string filterTimeStamp = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForLong("ClientDateTime", QueryComparisons.GreaterThanOrEqual, startTicks),
TableOperators.And,
TableQuery.GenerateFilterConditionForLong("ClientDateTime", QueryComparisons.LessThanOrEqual, endTicks)
);
TableQuery <LocationHistory> UQuery = new TableQuery <LocationHistory>().Where(TableQuery.CombineFilters(filterPartitionKey, TableOperators.And, filterTimeStamp));
base.LoadTable(Constants.LocationHistoryTableName);
List <LocationHistory> qryReturn = base.EntityTable.ExecuteQuery(UQuery).ToList();
return(qryReturn);
}
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);
}
/// <summary>
/// 获取需要分配的资产、订单，排除资产
/// </summary>
/// <returns></returns>
private static async Task UserToAllocateAssetByNoIncludeAsync(string[] noIncludeAssetIds, long minAmount, long maxAmount, string assetAzureTableName,
string yemUserProductAzureTableName, string writeAssetAzureTable,
string writeYemUserProductAzureTable, string writeUserAssetRatioAzureTable, string writeAssetUserRatioAzureTable)
{
string yemUserPurchase = ConfigurationManager.AppSettings["PurchasePartitionKey"];
Console.WriteLine("创建CloudTable对象");
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings["BlobConnectionString"]);
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
tableClient.DefaultRequestOptions.RetryPolicy = new ExponentialRetry(2.Seconds(), 6);
tableClient.DefaultRequestOptions.MaximumExecutionTime = 2.Minutes();
tableClient.DefaultRequestOptions.ServerTimeout = 2.Minutes();
CloudTable cloudOnSellAssetTable = tableClient.GetTableReference(assetAzureTableName);
//查询所有资产信息
TableQuery <OnSellAssetDto> queryOnSellAsset = new TableQuery <OnSellAssetDto>()
.Where("RemainderTotal gt 0 and IsLock eq false");
List <OnSellAssetDto> onSellAssetDtos = noIncludeAssetIds.Any() ?
cloudOnSellAssetTable.ExecuteQuery(queryOnSellAsset).Where(p => !noIncludeAssetIds.Contains(p.OnSellAssetId)).ToList() : cloudOnSellAssetTable.ExecuteQuery(queryOnSellAsset).OrderBy(p => p.RemainderTotal).ToList();
//获取购买信息
CloudTable cloudPurchaseOrderTable = tableClient.GetTableReference(yemUserProductAzureTableName);
TableQuery <YemUserProductDto> queryPurchaseOrder;
// ReSharper disable once ConvertIfStatementToConditionalTernaryExpression
if (maxAmount > 0)
{
//queryPurchaseOrder = new TableQuery<YemUserProductDto>()
// .Where($"PartitionKey eq '{yemUserPurchase}' and RemainingAmount ge {minAmount} and RemainingAmount lt {maxAmount} and IsLock eq false");
string startsWithCondition = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, yemUserPurchase),
TableOperators.And,
TableQuery.GenerateFilterConditionForLong("RemainingAmount", QueryComparisons.GreaterThanOrEqual, minAmount));
string filterCondition = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForLong("RemainingAmount", QueryComparisons.LessThan, maxAmount),
TableOperators.And,
startsWithCondition
);
string endCondition = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForBool("IsLock", QueryComparisons.Equal, false),
TableOperators.And,
filterCondition
);
queryPurchaseOrder = new TableQuery <YemUserProductDto>().Where(endCondition);
}
else
{
queryPurchaseOrder = new TableQuery <YemUserProductDto>()
.Where($"PartitionKey eq '{yemUserPurchase}' and RemainingAmount gt 0 and IsLock eq false");
}
var yemUserProductDtos = cloudPurchaseOrderTable.ExecuteQuery(queryPurchaseOrder).OrderByDescending(p => p.RemainingAmount).ToList();
Console.WriteLine("要处理的购买订单条数=" + yemUserProductDtos.Count());
Console.WriteLine("待分配的资产数=" + onSellAssetDtos.Count());
List <OnSellAssetDto> oldOnSellAssetDtos = onSellAssetDtos;
if (!oldOnSellAssetDtos.Any())
{
Console.WriteLine("没有处理的资产");
return;
}
int index = 0;
foreach (var item in yemUserProductDtos)
{
index++;
var addUserAssetRatios = new List <UserAssetRatio>(); //记录每笔购买产生的比例 用户资产关系
var addAssetUserRatios = new List <UserAssetRatio>(); //记录每笔购买产生的比例 资产用户关系
List <OnSellAssetDto> modifyOnSellAssets = new List <OnSellAssetDto>(); //记录每笔购买使用的资产
YemUserProductDto newYemUserProductDto = null;
Console.WriteLine("总共处理的条数为：" + yemUserProductDtos.Count + ",当前处理的条数：" + index);
Console.WriteLine("还需要处理的资产数=" + oldOnSellAssetDtos.Count());
long sumAssetCount = oldOnSellAssetDtos.Sum(p => p.RemainderTotal);
long waitingDealAmount = item.RemainingAmount; //购买订单待处理的金额
for (int i = oldOnSellAssetDtos.Count - 1; i >= 0; i--)
{
var assetItem = oldOnSellAssetDtos[i];
long allocateAmount = AllocateAmountByAsset(waitingDealAmount, assetItem.RemainderTotal, sumAssetCount);
if (item.RemainingAmount < allocateAmount)
{
allocateAmount = item.RemainingAmount;
}
if (assetItem.RemainderTotal < allocateAmount)
{
allocateAmount = assetItem.RemainderTotal;
}
if (allocateAmount <= 0)
{
continue;
}
var userAssetRatioTuple = CreateUserAssetRatio(item, assetItem, allocateAmount); //第一个是用户资产关系 第二个是资产用户关系
addUserAssetRatios.Add(userAssetRatioTuple.Item1);
addAssetUserRatios.Add(userAssetRatioTuple.Item2);
var updateOnSellAsset = BuildOnSellAsset(assetItem, allocateAmount); //修改资产的信息
modifyOnSellAssets.Add(updateOnSellAsset);
newYemUserProductDto = BuildYemUserProduct(item, allocateAmount); //修改购买订单需要配置的参数
assetItem.RemainderTotal -= allocateAmount;
if (assetItem.RemainderTotal == 0)
{
oldOnSellAssetDtos.Remove(assetItem);
}
}
//保存数据库
await InsertOrReplaceAzureTable(tableClient, newYemUserProductDto, addUserAssetRatios, addAssetUserRatios, modifyOnSellAssets, writeAssetAzureTable, writeYemUserProductAzureTable, writeUserAssetRatioAzureTable, writeAssetUserRatioAzureTable);
if (!oldOnSellAssetDtos.Any())
{
Console.WriteLine("没有处理的资产");
break;
}
}
}
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);
}
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 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);
}
}
}
private static async Task DealUserToAsset(long minAmount, long maxAmount, string yemUserProductAzureTableName, string writeAssetAzureTable, string writeYemUserProductAzureTable,
CloudTableClient tableClient, string yemUserPurchase, List <OnSellAssetDto> onSellAssetDtos)
{
CloudTable cloudPurchaseOrderTable = tableClient.GetTableReference(yemUserProductAzureTableName);
TableQuery <YemUserProductDto> queryPurchaseOrder;
// ReSharper disable once ConvertIfStatementToConditionalTernaryExpression
if (maxAmount > 0)
{
//queryPurchaseOrder = new TableQuery<YemUserProductDto>()
// .Where($"PartitionKey eq '{yemUserPurchase}' and RemainingAmount ge {minAmount} and RemainingAmount lt {maxAmount} and IsLock eq false");
string startsWithCondition = TableQuery.CombineFilters(
TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, yemUserPurchase),
TableOperators.And,
TableQuery.GenerateFilterConditionForLong("RemainingAmount", QueryComparisons.GreaterThanOrEqual, minAmount));
string filterCondition = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForLong("RemainingAmount", QueryComparisons.LessThan, maxAmount),
TableOperators.And,
startsWithCondition
);
string endCondition = TableQuery.CombineFilters(
TableQuery.GenerateFilterConditionForBool("IsLock", QueryComparisons.Equal, false),
TableOperators.And,
filterCondition
);
queryPurchaseOrder = new TableQuery <YemUserProductDto>().Where(endCondition);
}
else
{
queryPurchaseOrder = new TableQuery <YemUserProductDto>()
.Where($"PartitionKey eq '{yemUserPurchase}' and RemainingAmount gt 0 and IsLock eq false");
}
var yemUserProductDtos = cloudPurchaseOrderTable.ExecuteQuery(queryPurchaseOrder).OrderByDescending(p => p.RemainingAmount).ToList();
yemUserProductDtos = yemUserProductDtos.Where(p => p.RemainingAmount > 0).ToList();
Console.WriteLine("要处理的购买订单条数=" + yemUserProductDtos.Count());
Console.WriteLine("待分配的资产数=" + onSellAssetDtos.Count());
List <OnSellAssetDto> oldOnSellAssetDtos = onSellAssetDtos;
if (!oldOnSellAssetDtos.Any())
{
Console.WriteLine("没有处理的资产");
return;
}
int index = 0;
//int syncIndex = 0; //备份索引
Stopwatch s = new Stopwatch();
s.Start();
List <OnSellAssetDto> lastOnSellAssetDtos = new List <OnSellAssetDto>(); //上一次的资产情况
foreach (var item in yemUserProductDtos)
{
if (!lastOnSellAssetDtos.Any()) //第一次
{
lastOnSellAssetDtos.AddRange(oldOnSellAssetDtos.MapToOnSellAssetList());
}
index++;
var addUserAssetRatios = new List <UserAssetRatio>(); //记录每笔购买产生的比例 用户资产关系
List <OnSellAssetDto> modifyOnSellAssets = new List <OnSellAssetDto>(); //记录每笔购买使用的资产
var addAssetUserRatios = new List <UserAssetRatio>(); //记录每笔购买产生的比例 资产用户关系
YemUserProductDto newYemUserProductDto = null;
Console.WriteLine("总共处理的条数为：" + yemUserProductDtos.Count + ",当前处理的条数：" + index);
Console.WriteLine("还需要处理的资产数=" + oldOnSellAssetDtos.Count());
long sumAssetCount = oldOnSellAssetDtos.Sum(p => p.RemainderTotal);
long waitingDealAmount = item.RemainingAmount; //购买订单待处理的金额
long currentDealPurchaseAmount = 0; //本次处理的金额
for (int i = oldOnSellAssetDtos.Count - 1; i >= 0; i--)
{
if (item.RemainingAmount == 0)
{
break;
}
var assetItem = oldOnSellAssetDtos[i];
long allocateAmount = AllocateAmountByAsset(waitingDealAmount, assetItem.RemainderTotal, sumAssetCount);
if (item.RemainingAmount < allocateAmount)
{
allocateAmount = item.RemainingAmount;
}
if (assetItem.RemainderTotal < allocateAmount)
{
allocateAmount = assetItem.RemainderTotal;
}
if (allocateAmount <= 0)
{
continue;
}
item.RemainingAmount -= allocateAmount;
currentDealPurchaseAmount += allocateAmount; //统计该订单分配的总金额
assetItem.RemainderTotal -= allocateAmount;
var userAssetRatioTuple = CreateUserAssetRatio(item, assetItem, allocateAmount); //第一个是用户资产关系 第二个是资产用户关系
addUserAssetRatios.Add(userAssetRatioTuple.Item1);
addAssetUserRatios.Add(userAssetRatioTuple.Item2);
var updateOnSellAsset = BuildOnSellAsset(assetItem, allocateAmount); //修改资产的信息
modifyOnSellAssets.Add(updateOnSellAsset);
if (assetItem.RemainderTotal == 0)
{
oldOnSellAssetDtos.Remove(assetItem);
}
}
newYemUserProductDto = BuildYemUserProduct(item, currentDealPurchaseAmount); //修改购买订单需要配置的参数
//保存数据库
await InsertOrReplaceAzureTable(tableClient, newYemUserProductDto, addUserAssetRatios, addAssetUserRatios,
modifyOnSellAssets, writeAssetAzureTable, writeYemUserProductAzureTable, currentDealPurchaseAmount, lastOnSellAssetDtos);
lastOnSellAssetDtos = new List <OnSellAssetDto>();
lastOnSellAssetDtos.AddRange(modifyOnSellAssets.MapToOnSellAssetList()); //记录上一次资产列表
if (!oldOnSellAssetDtos.Any())
{
Console.WriteLine("没有处理的资产");
break;
}
}
s.Stop();
Console.WriteLine("End:" + s.ElapsedMilliseconds);
}
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.");
}
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);
}