internal async Task <List <T> > GetAllItems(FilterValues filter = null)
{
var sql = "SELECT TOP 300 * FROM c ";
//bool andRequired = false;
//bool whereRequired = true;
if (filter == null)
{
filter = new FilterValues();
}
if (filter.MinDateTime != null && filter.MaxDateTime != null)
{
sql += "where c.EventEnqueuedUtcTime between @mindatetime and @maxdatetime";
//andRequired = true;
//whereRequired = false;
}
sql += " order by c.EventEnqueuedUtcTime desc";
QueryDefinition query = new QueryDefinition(sql);
if (filter.MinDateTime != null && filter.MaxDateTime != null)
{
query.WithParameter("@mindatetime", filter.MinDateTime);
query.WithParameter("@maxdatetime", filter.MaxDateTime);
}
var result = await GetAllItems(query);
return(result);
}
// https://github.com/Azure/azure-cosmos-dotnet-v3/blob/master/Microsoft.Azure.Cosmos.Samples/Usage/Queries/Program.cs
public async Task <AccountData> FetchByIdAsync(
Guid id)
{
var queryDefinition =
new QueryDefinition(
"SELECT TOP 1 * FROM a WHERE a.object = @object and a.id = @id");
queryDefinition.WithParameter("@object", "Account");
queryDefinition.WithParameter("@id", id.ToString());
var feedIterator =
_cosmosContainer.GetItemQueryIterator <AccountData>(
queryDefinition);
var accountDataList =
new List <AccountData>();
while (feedIterator.HasMoreResults)
{
accountDataList.AddRange(
await feedIterator.ReadNextAsync());
}
;
if (accountDataList.Count == 0)
{
return(null);
}
return
(accountDataList.First());
}
public IFetchedJob Dequeue(string[] queues, CancellationToken cancellationToken)
{
lock (syncLock)
{
IEnumerable <string> queueParams = Enumerable.Range(0, queues.Length).Select((q, i) => $"@queue_{i}");
string query = $"SELECT TOP 1 * FROM doc WHERE doc.type = @type AND doc.name IN ({string.Join(", ", queueParams)}) " +
"AND (NOT IS_DEFINED(doc.fetched_at) OR doc.fetched_at < @timeout) ORDER BY doc.created_on";
QueryDefinition sql = new QueryDefinition(query)
.WithParameter("@type", (int)DocumentTypes.Queue);
for (int index = 0; index < queues.Length; index++)
{
string queue = queues[index];
sql.WithParameter($"@queue_{index}", queue);
}
while (true)
{
cancellationToken.ThrowIfCancellationRequested();
logger.Trace("Looking for any jobs from the queue");
using (new CosmosDbDistributedLock(DISTRIBUTED_LOCK_KEY, defaultLockTimeout, storage))
{
int invisibilityTimeoutEpoch = DateTime.UtcNow.Add(invisibilityTimeout.Negate()).ToEpoch();
sql.WithParameter("@timeout", invisibilityTimeoutEpoch);
Documents.Queue data = storage.Container.GetItemQueryIterator <Documents.Queue>(sql, requestOptions: new QueryRequestOptions {
PartitionKey = partitionKey
})
.ToQueryResult()
.FirstOrDefault();
if (data != null)
{
// mark the document
data.FetchedAt = DateTime.UtcNow;
Task <ItemResponse <Documents.Queue> > task = storage.Container.ReplaceItemWithRetriesAsync(data, data.Id, partitionKey, cancellationToken: cancellationToken);
task.Wait(cancellationToken);
logger.Trace($"Found job {data.JobId} from the queue : {data.Name}");
return(new FetchedJob(storage, data));
}
}
logger.Trace($"Unable to find any jobs in the queue. Will check the queue for jobs in {storage.StorageOptions.QueuePollInterval.TotalSeconds} seconds");
cancellationToken.WaitHandle.WaitOne(storage.StorageOptions.QueuePollInterval);
}
}
}
private async Task <List <Tenant> > FetchTenants(string sqlQueryText, Dictionary <string, string> parameters = null)
{
logger.LogInformation($"Executing query: {sqlQueryText}");
try
{
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
if (parameters != null)
{
foreach (var parameter in parameters)
{
queryDefinition = queryDefinition.WithParameter(parameter.Key, parameter.Value);
}
}
FeedIterator <Tenant> queryResultSetIterator = container.GetItemQueryIterator <Tenant>(queryDefinition);
List <Tenant> tenants = new List <Tenant>();
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <Tenant> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (Tenant tenant in currentResultSet)
{
tenants.Add(tenant);
}
}
logger.LogInformation($"Fetched tenants : {tenants.Count}");
return(tenants);
}
catch (Exception ex)
{
logger.LogError(ex, "Exception occured");
}
return(null);
}
public async Task UpdateUsernameInPostsContainer(string userId, string newUsername)
{
//get all items matching the userId (which can be posts, comments, or likes) from the posts container where we need to update the username
var queryString = $"SELECT DISTINCT VALUE p.postId FROM p WHERE p.userId = @UserId";
var queryDef = new QueryDefinition(queryString);
queryDef.WithParameter("@UserId", userId);
var query = this._postsContainer.GetItemQueryIterator <string>(queryDef);
var postIds = new List <string>();
while (query.HasMoreResults)
{
var response = await query.ReadNextAsync();
var ru = response.RequestCharge;
postIds.AddRange(response.ToList());
}
//NOTE: This operation is costly because it requires this stored procedure to be executed on every partition of the posts container.
// We assume that most users choose a suitable username during sign-up and won't ever change it, so this update will run very rarely.
foreach (var postId in postIds)
{
var obj = new dynamic[] { userId, newUsername };
var result = await _postsContainer.Scripts.ExecuteStoredProcedureAsync <string>("updateUsernames", new Microsoft.Azure.Cosmos.PartitionKey(postId), obj);
}
}
public void AddToQuery(QueryDefinition query)
{
foreach ((string Key, object Value)item in ToSqlParameterCollection())
{
query.WithParameter(item.Key, item.Value);
}
}
public async Task <AccountData> FetchByAccountNumberAsync(
string accountNumber)
{
var queryDefinition =
new QueryDefinition(
"SELECT TOP 1 * FROM a WHERE a.object = @object");
queryDefinition.WithParameter("@object", "Account");
var feedIterator =
_cosmosContainer.GetItemQueryIterator <AccountData>(
queryDefinition,
requestOptions: new QueryRequestOptions {
PartitionKey = new PartitionKey(accountNumber)
});
var accountDataList =
new List <AccountData>();
while (feedIterator.HasMoreResults)
{
accountDataList.AddRange(
await feedIterator.ReadNextAsync());
}
;
if (accountDataList.Count == 0)
{
return(null);
}
return
(accountDataList.First());
}
public async Task <List <Image> > GetGalleryImagesAsync(string galleryId)
{
const string query = "SELECT * FROM c WHERE c.GalleryId = @galleryId";
var queryDefinition = new QueryDefinition(query);
queryDefinition.WithParameter("@galleryId", galleryId);
var container = Server.Instance.Database.GetContainer(Constants.ImagesContainerName);
var queryResult = container.GetItemQueryIterator <Image>(queryDefinition);
double charge = 0;
TimeSpan elapsedTime = default;
var images = new List <Image>();
while (queryResult.HasMoreResults)
{
var results = await queryResult.ReadNextAsync();
images.AddRange(results);
elapsedTime += results.Diagnostics.GetClientElapsedTime();
charge += results.RequestCharge;
}
Log.Debug($"ImageServer.GetGalleryImagesAsync: Found {images.Count} gallery images");
Log.Debug($"ImageServer.GetGalleryImagesAsync: Total request charge: {charge}. Total elapsed time: {elapsedTime.TotalMilliseconds} ms");
return(images);
}
public async Task <Album> GetBy(int id)
{
Album result = null;
var sqlQueryText = "SELECT * FROM album a WHERE a.Id = @id";
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
queryDefinition.WithParameter("@id", id);
FeedIterator <CosmosAlbum> queryResultSetIterator = _container.GetItemQueryIterator <CosmosAlbum>(queryDefinition);
List <CosmosAlbum> albums = new List <CosmosAlbum>();
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <CosmosAlbum> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (CosmosAlbum ca in currentResultSet)
{
result = new Album()
{
Id = ca.Id,
Artist = ca.Artist,
Title = ca.Title,
Year = ca.Year,
Format = ca.Format,
Location = ca.Location,
Symbol = ca.Symbol,
Store = ca.Store,
Price = ca.Price
};
break;
}
}
return(result);
}
/// <summary>
/// List of element of current container specified by a queryText. It use the sql api
/// </summary>
/// <param name="queryText"></param>
/// <param name="parameters"></param>
/// <exception cref="ArgumentNullException">Throws when queryText is null</exception>
/// <returns></returns>
public List <T> QuerySync(string queryText, Dictionary <string, object> parameters = null)
{
if (string.IsNullOrEmpty(queryText))
{
throw new ArgumentNullException($"{nameof(queryText)} is empty or null");
}
List <T> result = new List <T>();
QueryDefinition queryDefinition = new QueryDefinition(queryText);
if (parameters != null)
{
foreach (KeyValuePair <string, object> parameter in parameters)
{
queryDefinition.WithParameter(parameter.Key, parameter.Value);
}
}
var queryResult = _container.GetItemQueryIterator <T>(queryDefinition);
while (queryResult.HasMoreResults)
{
FeedResponse <T> resultSet = queryResult.ReadNextAsync().Result;
foreach (T element in resultSet)
{
result.Add(element);
}
}
return(result);
}
public void ValidateCreateQueryDefinition()
{
string query = "select * from s where s.Account = @account";
string paramName = "@account";
string paramValue = "12345";
QueryDefinition sqlQueryDefinition = new QueryDefinition(query)
.WithParameter(paramName, paramValue);
SqlQuerySpec sqlQuerySpec = sqlQueryDefinition.ToSqlQuerySpec();
Assert.AreEqual(query, sqlQuerySpec.QueryText);
Assert.AreEqual(1, sqlQuerySpec.Parameters.Count);
SqlParameter sqlParameter = sqlQuerySpec.Parameters.First();
Assert.AreEqual(paramName, sqlParameter.Name);
Assert.AreEqual(paramValue, sqlParameter.Value);
string newParamValue = "9001";
sqlQueryDefinition.WithParameter(paramName, newParamValue);
sqlQuerySpec = sqlQueryDefinition.ToSqlQuerySpec();
Assert.AreEqual(query, sqlQuerySpec.QueryText);
Assert.AreEqual(1, sqlQuerySpec.Parameters.Count);
sqlParameter = sqlQuerySpec.Parameters.First();
Assert.AreEqual(paramName, sqlParameter.Name);
Assert.AreEqual(newParamValue, sqlParameter.Value);
}
public async Task <IEnumerable <T> > GetAsync <T>(string containerId, string partitionKey, string query, Dictionary <string, object> parameters)
{
ThrowIfNotInitialized();
var container = Client.GetContainer(DatabaseId, containerId);
QueryDefinition q = new QueryDefinition(query);
if (parameters != null)
{
for (int i = 0; i < parameters.Count; i++)
{
q = q.WithParameter(parameters.Keys.ElementAt(i), parameters.Values.ElementAt(i));
}
}
var r = container.GetItemQueryIterator <T>(q,
requestOptions: new QueryRequestOptions()
{
PartitionKey = new PartitionKey(partitionKey)
});
var items = new List <T>();
while (r.HasMoreResults)
{
var itemr = await r.ReadNextAsync(CancellationToken);
items.AddRange(itemr.Resource);
}
return(items);
}
private QueryDefinition CreateDefinition(string queryText)
{
QueryDefinition queryDefinition = new QueryDefinition(queryText);
foreach (List <string> paths in this.pathsToEncrypt.Keys)
{
if (SqlQuery.TryParse(queryText, out SqlQuery sqlQuery) &&
(sqlQuery.WhereClause != null) &&
(sqlQuery.WhereClause.FilterExpression != null) &&
(sqlQuery.WhereClause.FilterExpression is SqlBinaryScalarExpression expression) &&
(expression.OperatorKind == SqlBinaryScalarOperatorKind.Equal))
{
foreach (string path in paths)
{
string rightExpression = expression.RightExpression.ToString();
string leftExpression = expression.LeftExpression.ToString();
if (leftExpression.Contains(path.Substring(1)))
{
string newExpression = queryText.Replace(rightExpression, "@" + path.Substring(1));
queryDefinition = new QueryDefinition(newExpression);
queryDefinition.WithParameter("@" + path.Substring(1), rightExpression);
}
}
}
}
return(queryDefinition);
}
public async Task <IEnumerable <PartnerOrganization> > Get(string sql, IDictionary <string, object> parameters = null, string partitionKey = null)
{
QueryDefinition query = new QueryDefinition(sql);
if (parameters != null)
{
foreach (var parameter in parameters)
{
query.WithParameter(parameter.Key, parameter.Value);
}
}
QueryRequestOptions options = new QueryRequestOptions()
{
MaxBufferedItemCount = 100, MaxConcurrency = 10
};
if (partitionKey != null)
{
options.PartitionKey = new PartitionKey(partitionKey);
}
List <PartnerOrganization> results = new List <PartnerOrganization>();
FeedIterator <PartnerOrganization> iterator = this.cosmosContainer.GetItemQueryIterator <PartnerOrganization>(query, requestOptions: options);
while (iterator.HasMoreResults)
{
FeedResponse <PartnerOrganization> response = await iterator.ReadNextAsync();
results.AddRange(response);
}
return(results);
}
public async Task <IEnumerable <T> > ConvertAsync(CosmosAttribute attribute, CancellationToken cancellationToken)
{
CosmosContext context = configProvider.CreateContext(attribute);
List <T> finalResults = new List <T>();
string continuation = null;
QueryDefinition query = new QueryDefinition(context.ResolvedAttribute.SqlQuery);
if ((context.ResolvedAttribute.SqlQueryParameters?.Count ?? 0) > 0)
{
foreach (var parameter in context.ResolvedAttribute.SqlQueryParameters)
{
query = query.WithParameter(parameter.Key, parameter.Value);
}
}
var response = context.Service.GetItemQueryIterator <T>(context.ResolvedAttribute.DatabaseName, context.ResolvedAttribute.ContainerName, query);
do
{
await foreach (var page in response.AsPages(continuation))
{
finalResults.AddRange(page.Values);
continuation = page.ContinuationToken;
}
}while (!string.IsNullOrEmpty(continuation));
return(finalResults);
}
public void ValidateHashWithSameParameter()
{
QueryDefinition sqlQueryDefinition = new QueryDefinition("select * from s where s.Account = 1234");
sqlQueryDefinition.WithParameter("@account", "12345");
sqlQueryDefinition.WithParameter("@name", "ABC");
int hashCode = QueryDefinitionEqualityComparer.Instance.GetHashCode(sqlQueryDefinition);
// Reverse the order
sqlQueryDefinition = new QueryDefinition("select * from s where s.Account = 1234");
sqlQueryDefinition.WithParameter("@name", "ABC");
sqlQueryDefinition.WithParameter("@account", "12345");
Assert.AreEqual(hashCode, QueryDefinitionEqualityComparer.Instance.GetHashCode(sqlQueryDefinition));
}
private void EnsureParameters(QueryDefinition sql)
{
foreach (var condition in _conditions)
{
sql.WithParameter(condition.Property, condition.Data);
}
}
/// <summary>
/// Get a list of Actors by search string
///
/// The search is a "contains" search on actor name
/// If q is empty, all actors are returned
/// </summary>
/// <param name="actorQueryParameters">search parameters</param>
/// <returns>List of Actors or an empty list</returns>
public async Task <IEnumerable <Actor> > GetActorsAsync(ActorQueryParameters actorQueryParameters)
{
_ = actorQueryParameters ?? throw new ArgumentNullException(nameof(actorQueryParameters));
string sql = ActorSelect;
int offset = actorQueryParameters.GetOffset();
int limit = actorQueryParameters.PageSize;
string offsetLimit = string.Format(CultureInfo.InvariantCulture, ActorOffset, offset, limit);
if (!string.IsNullOrEmpty(actorQueryParameters.Q))
{
// convert to lower and escape embedded '
actorQueryParameters.Q = actorQueryParameters.Q.Trim().ToLowerInvariant().Replace("'", "''", System.StringComparison.OrdinalIgnoreCase);
if (!string.IsNullOrEmpty(actorQueryParameters.Q))
{
// get actors by a "like" search on name
sql += string.Format(CultureInfo.InvariantCulture, $" and contains(m.textSearch, @q) ");
}
}
sql += ActorOrderBy + offsetLimit;
QueryDefinition queryDefinition = new QueryDefinition(sql);
if (!string.IsNullOrEmpty(actorQueryParameters.Q))
{
queryDefinition.WithParameter("@q", actorQueryParameters.Q);
}
return(await InternalCosmosDBSqlQuery <Actor>(queryDefinition).ConfigureAwait(false));
}
public async Task <Gallery> GetGalleryByLegacyGuidIdAsync(Guid galleryLegacyId)
{
const string query = "SELECT * FROM c WHERE c.LegacyGuidId = @galleryLegacyId";
var queryDefinition = new QueryDefinition(query);
queryDefinition.WithParameter("@galleryLegacyId", galleryLegacyId);
return(await GetGalleryByQueryAsync(queryDefinition));
}
public void ValidateEqualsWithParameters()
{
QueryDefinition sqlQueryDefinition = new QueryDefinition("select * from s where s.Account = 1234");
sqlQueryDefinition.WithParameter("@account", "12345");
sqlQueryDefinition.WithParameter("@name", "ABC");
// Reverse the order
QueryDefinition sqlQueryDefinition2 = new QueryDefinition("select * from s where s.Account = 1234");
Assert.IsFalse(QueryDefinitionEqualityComparer.Instance.Equals(sqlQueryDefinition, sqlQueryDefinition2));
sqlQueryDefinition2.WithParameter("@name", "ABC");
sqlQueryDefinition2.WithParameter("@account", "12345");
Assert.IsTrue(QueryDefinitionEqualityComparer.Instance.Equals(sqlQueryDefinition, sqlQueryDefinition2));
}
public async Task <Image> GetImageByLegacyIdAsync(long legacyId)
{
const string query = "SELECT * FROM c WHERE c.LegacyNumId = @legacyId";
var queryDefinition = new QueryDefinition(query);
queryDefinition.WithParameter("@legacyId", legacyId);
return(await GetImageByQueryAsync(queryDefinition));
}
public async Task <CosmosDbResponse <IEnumerable <T> > > GetByQuery <T>(string partitionKey, string query, IReadOnlyDictionary <string, object> parameters)
{
var queryDef = new QueryDefinition(query);
if (parameters != null)
{
foreach (KeyValuePair <string, object> kvp in parameters)
{
queryDef.WithParameter(kvp.Key, kvp.Value);
}
}
var options = new QueryRequestOptions();
if (!string.IsNullOrWhiteSpace(partitionKey))
{
options.PartitionKey = new PartitionKey(partitionKey);
}
var response = new CosmosDbResponse <IEnumerable <T> >();
var results = new List <T>();
Stopwatch watch = Stopwatch.StartNew();
try
{
FeedIterator <T> resultSetIterator = _container.GetItemQueryIterator <T>(queryDef, requestOptions: options);
while (resultSetIterator.HasMoreResults)
{
FeedResponse <T> feedResponse = await resultSetIterator.ReadNextAsync();
results.AddRange(feedResponse);
response.RequestCharge += feedResponse.RequestCharge;
response.ActivityId = feedResponse.ActivityId;
response.ETag = feedResponse.ETag;
response.Diagnostics = feedResponse.Diagnostics.ToString();
}
watch.Stop();
response.StatusCode = HttpStatusCode.OK;
}
catch (CosmosException cex)
{
watch.Stop();
response.Error = cex;
response.ActivityId = cex.ActivityId;
response.StatusCode = cex.StatusCode;
response.RequestCharge += cex.RequestCharge;
response.Diagnostics = cex.Diagnostics.ToString();
}
finally
{
response.ExecutionTime = watch.Elapsed;
response.Result = results;
}
return(response);
}
private async Task <IActionResult> QueryMatchContact(HttpRequest req, IUser user)
{
try
{
// User
var queryUser = new QueryDefinition("SELECT * FROM Beacon b WHERE b.UserMajor = @Major and b.UserMinor = @Minor");
queryUser.WithParameter("Major", user.Major);
queryUser.WithParameter("Minor", user.Minor);
var rUser = await Cosmos.Beacon.GetItemQueryIterator <BeaconModel>(queryUser).ReadNextAsync();
// Other
var queryOther = new QueryDefinition("SELECT * FROM Beacon b WHERE b.Major = @Major and b.Minor = @Minor");
queryOther.WithParameter("Major", user.Major);
queryOther.WithParameter("Minor", user.Minor);
var rOther = await Cosmos.Beacon.GetItemQueryIterator <BeaconModel>(queryOther).ReadNextAsync();
// validation
foreach (var u in rUser.Resource)
{
foreach (var o in rOther.Resource)
{
if (u.Major == o.UserMajor && u.Minor == o.UserMinor)
{
// target UserUuid
var id = o.GetId();
await updateUserStatus(id, Common.UserStatus.Contacted);
}
}
}
await updateUserStatus(user.GetId(), Common.UserStatus.Infection);
return(new OkResult());
}
catch (CosmosException ex)
{
// 429-TooManyRequests
if (ex.StatusCode == System.Net.HttpStatusCode.TooManyRequests)
{
return(new StatusCodeResult(503));
}
}
AddBadRequest(req);
return(new BadRequestObjectResult(""));
}
public async Task <BlogPostLike> GetBlogPostLikeForUserIdAsync(string postId, string userId)
{
var queryString = $"SELECT TOP 1 * FROM p WHERE p.type='like' AND p.postId = @PostId AND p.userId = @UserId ORDER BY p.dateCreated DESC";
var queryDef = new QueryDefinition(queryString);
queryDef.WithParameter("@PostId", postId);
queryDef.WithParameter("@UserId", userId);
var query = this._postsContainer.GetItemQueryIterator <BlogPostLike>(queryDef);
BlogPostLike like = null;
if (query.HasMoreResults)
{
var response = await query.ReadNextAsync();
var ru = response.RequestCharge;
like = response.FirstOrDefault();
}
return(like);
}
public async Task <PagedList <DocumentDetails> > GetPagedCorrespondenceByInvestorId(int investorId, int pageNumber = 1, int pageSize = 10)
{
var cacheKey = string.Format($"customer_corro_{investorId}");
if (_cache.Get(cacheKey) != null)
{
var list = _cache.Get(cacheKey) as List <DocumentDetails>;
if (list != null)
{
return(new PagedList <DocumentDetails>(list.AsQueryable(), pageNumber, pageSize));
}
}
var result = new List <DocumentDetails>();
using (var client = new CosmosClient(_functionSettings.CosmosDBEndpoint, _functionSettings.CosmosDBMasterKey))
{
var container = client.GetContainer(_functionSettings.CosmosDBName,
SQLStatements.InvestorDocuments.ContainerName);
var sql = SQLStatements.InvestorDocuments.ListInvestorDocs;
var query = new QueryDefinition(sql);
query.WithParameter("@InvestorId", investorId);
var iterator = container
.GetItemQueryIterator <InvestorDocument>(query,
requestOptions: new QueryRequestOptions()
{
MaxConcurrency = 1
});
while (iterator.HasMoreResults)
{
var task = await iterator.ReadNextAsync();
FeedResponse <InvestorDocument> response = task;
if (response.Any())
{
foreach (var item in response)
{
result.AddRange(item.Corro);
}
var expireTime = DateTimeOffset.Now.AddMinutes(_functionSettings.CacheInMinutes);
_cache.Set(cacheKey, result, expireTime);
}
}
}
return(new PagedList <DocumentDetails>(result.AsQueryable(), pageNumber, pageSize));
}
private static QueryDefinition CreateQueryDefinition(Query query)
{
var definition = new QueryDefinition(query.Sql);
if (query.Parameters != null)
{
foreach (var(key, value) in query.Parameters)
{
definition = definition.WithParameter(key, value);
}
}
return(definition);
}
private async Task <FeedIterator> InitializeInternalFeedIteratorAsync(QueryDefinition queryDefinition)
{
QueryDefinition newqueryDefinition = queryDefinition;
if (queryDefinition.Parameters.Count == 0)
{
newqueryDefinition = this.CreateDefinition(this.queryDefinition.QueryText);
}
QueryDefinition queryWithEncryptedParameters = new QueryDefinition(newqueryDefinition.QueryText);
string modifiedText = newqueryDefinition.QueryText;
foreach (KeyValuePair <string, Query.Core.SqlParameter> parameters in newqueryDefinition.Parameters)
{
modifiedText = modifiedText.Replace((string)parameters.Value.Name, (string)parameters.Value.Value);
}
foreach (KeyValuePair <string, Query.Core.SqlParameter> parameters in newqueryDefinition.Parameters)
{
foreach (List <string> paths in this.pathsToEncrypt.Keys)
{
if (SqlQuery.TryParse(modifiedText, out SqlQuery sqlQuery) &&
(sqlQuery.WhereClause != null) &&
(sqlQuery.WhereClause.FilterExpression != null) &&
(sqlQuery.WhereClause.FilterExpression is SqlBinaryScalarExpression expression) &&
(expression.OperatorKind == SqlBinaryScalarOperatorKind.Equal))
{
foreach (string path in paths)
{
string leftExpression = expression.LeftExpression.ToString();
if (leftExpression.Contains(path.Substring(1)))
{
byte[] plaintext = System.Text.Encoding.UTF8.GetBytes((string)parameters.Value.Value);
byte[] ciphertext = await this.encryptor.EncryptAsync(
plaintext,
this.pathsToEncrypt[paths],
CosmosEncryptionAlgorithm.AEAD_AES_256_CBC_HMAC_SHA256);
queryWithEncryptedParameters.WithParameter(parameters.Key, ciphertext);
}
}
}
}
}
return(this.container.GetItemQueryStreamIterator(
queryWithEncryptedParameters,
this.continuationToken,
this.requestOptions));
}
/// <summary>
/// Get a list of Actors by search string
///
/// The search is a "contains" search on actor name
/// If q is empty, all actors are returned
/// </summary>
/// <param name="actorQueryParameters">search parameters</param>
/// <returns>List of Actors or an empty list</returns>
public async Task <IEnumerable <Actor> > GetActorsAsync(ActorQueryParameters actorQueryParameters)
{
if (actorQueryParameters == null)
{
throw new ArgumentNullException(nameof(actorQueryParameters));
}
string key = actorQueryParameters.GetKey();
if (App.UseCache && cache.Contains(key) && cache.Get(key) is List <Actor> ac)
{
return(ac);
}
string sql = ActorSelect;
int offset = actorQueryParameters.GetOffset();
int limit = actorQueryParameters.PageSize;
string offsetLimit = string.Format(CultureInfo.InvariantCulture, ActorOffset, offset, limit);
if (!string.IsNullOrEmpty(actorQueryParameters.Q))
{
// convert to lower and escape embedded '
actorQueryParameters.Q = actorQueryParameters.Q.Trim().ToLowerInvariant().Replace("'", "''", System.StringComparison.OrdinalIgnoreCase);
if (!string.IsNullOrEmpty(actorQueryParameters.Q))
{
// get actors by a "like" search on name
sql += string.Format(CultureInfo.InvariantCulture, $" and contains(m.textSearch, @q) ");
}
}
sql += ActorOrderBy + offsetLimit;
QueryDefinition queryDefinition = new QueryDefinition(sql);
if (!string.IsNullOrEmpty(actorQueryParameters.Q))
{
queryDefinition.WithParameter("@q", actorQueryParameters.Q);
}
List <Actor> res = (List <Actor>) await InternalCosmosDBSqlQuery <Actor>(queryDefinition).ConfigureAwait(false);
cache.Add(new System.Runtime.Caching.CacheItem(key, res), cachePolicy);
return(res);
}
public void GetQueryParametersReflectsParametersAddedLater()
{
string query = "select * from s where s.Account = @account and s.Balance > @balance";
QueryDefinition queryDefinition = new QueryDefinition(query)
.WithParameter("@account", "12345");
IReadOnlyList <(string Name, object Value)> parameters = queryDefinition.GetQueryParameters();
queryDefinition.WithParameter("@balance", 42);
Assert.AreEqual(2, parameters.Count);
Assert.AreEqual("@account", parameters[0].Name);
Assert.AreEqual("12345", parameters[0].Value);
Assert.AreEqual("@balance", parameters[1].Name);
Assert.AreEqual(42, parameters[1].Value);
}
private async Task <NotificationMessageModel[]> QueryNotificationMessages(DateTime lastScanTime)
{
var q = new QueryDefinition("SELECT * FROM c WHERE c.Created >= @Created ORDER BY c.Created DESC");
q.WithParameter("@Created", lastScanTime);
var opt = new QueryRequestOptions();
var iterator = Cosmos.Notification.GetItemQueryIterator <NotificationMessageModel>(q, null, opt);
var resultCollection = new List <NotificationMessageModel>();
while (iterator.HasMoreResults)
{
var result = await iterator.ReadNextAsync();
resultCollection.AddRange(result.Resource);
}
return(resultCollection.ToArray());
}
