public async Task <Learner> FetchByUserNameAsync(
string userName)
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT * FROM l where l.userName = @userName";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter(
"@userName", userName);
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
var learner =
learnerList.FirstOrDefault();
return(learner);
}
public async Task <ContestLearner> FetchByUserNameAsync(
Guid contestId,
string userName)
{
var contestLearnerContainer =
_cosmosDatabase.Containers["contestlearners"];
var query =
$"SELECT * FROM cl WHERE cl.userName = @userName";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter("@userName", userName.ToLower());
var contestLearners =
contestLearnerContainer.Items.CreateItemQuery <ContestLearner>(
queryDefinition,
contestId.ToString());
var contestLearnerList = new List <ContestLearner>();
while (contestLearners.HasMoreResults)
{
contestLearnerList.AddRange(
await contestLearners.FetchNextSetAsync());
}
;
var contestLearner =
contestLearnerList.FirstOrDefault();
return(contestLearner);
}
public async Task <IEnumerable <HeroDocument> > ListAsync()
{
var heroContainer =
_cosmosDatabase.Containers[_containerId];
var sqlQuery =
"SELECT * FROM t1 WHERE t1.object = @object";
var sqlQueryDefinition =
new CosmosSqlQueryDefinition(sqlQuery);
sqlQueryDefinition.UseParameter(
"@object", "Hero");
var heroDocumentIterator =
heroContainer.Items.CreateItemQuery <HeroDocument>(
sqlQueryDefinition,
maxConcurrency: MAXIMUM_CONCURRENCY);
var heroDocumentList = new List <HeroDocument>();
while (heroDocumentIterator.HasMoreResults)
{
heroDocumentList.AddRange(
await heroDocumentIterator.FetchNextSetAsync());
}
;
return(heroDocumentList);
}
public async Task <IEnumerable <Learner> > FetchListAsync(
string[] names)
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT * FROM l";
query += " WHERE ARRAY_CONTAINS(@names, l.userName)";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter("@names", names);
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
return(learnerList);
}
public async Task <IEnumerable <Course> > FetchListAsync(
Guid[] ids)
{
var courseContainer =
_cosmosDatabase.Containers["courses"];
var query =
$"SELECT * FROM l";
query += " WHERE ARRAY_CONTAINS(@ids, l.id)";
var queryDefinition =
new CosmosSqlQueryDefinition(query);
queryDefinition.UseParameter("@ids", ids);
var courses =
courseContainer.Items.CreateItemQuery <Course>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var courseList = new List <Course>();
while (courses.HasMoreResults)
{
courseList.AddRange(
await courses.FetchNextSetAsync());
}
;
return(courseList);
}
public void ValidateCreateQueryDefinition()
{
string query = "select * from s where s.Account = @account";
string paramName = "@account";
string paramValue = "12345";
CosmosSqlQueryDefinition sqlQueryDefinition = new CosmosSqlQueryDefinition(query)
.UseParameter(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.UseParameter(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 <Learner> > FetchListAsync(
string status = null,
int?maximumRecords = null,
string orderBy = "l.displayName")
{
var learnerContainer =
_cosmosDatabase.Containers["learners"];
var query =
$"SELECT";
if (maximumRecords.HasValue)
{
query += $" TOP {maximumRecords.Value}";
}
query += " * FROM l";
if (!string.IsNullOrWhiteSpace(status))
{
query += " WHERE l.status = @status";
}
if (!string.IsNullOrWhiteSpace(orderBy))
{
query += $" ORDER BY {orderBy}";
}
var queryDefinition =
new CosmosSqlQueryDefinition(query);
if (!string.IsNullOrWhiteSpace(status))
{
queryDefinition.UseParameter("@status", status);
}
var learners =
learnerContainer.Items.CreateItemQuery <Learner>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var learnerList = new List <Learner>();
while (learners.HasMoreResults)
{
learnerList.AddRange(
await learners.FetchNextSetAsync());
}
;
return(learnerList);
}
public async Task <IEnumerable <Contest> > FetchListAsync(
string status = null)
{
var contestContainer =
_cosmosDatabase.Containers["contests"];
var query =
$"SELECT * FROM c";
if (!string.IsNullOrWhiteSpace(status))
{
query += " WHERE c.status = @status";
}
var queryDefinition =
new CosmosSqlQueryDefinition(query);
if (!string.IsNullOrEmpty(status))
{
queryDefinition.UseParameter("@status", status);
}
var contests =
contestContainer.Items.CreateItemQuery <Contest>(
queryDefinition,
_azureCosmosDbOptions.Value.MaxConcurrency);
var contestList = new List <Contest>();
while (contests.HasMoreResults)
{
contestList.AddRange(
await contests.FetchNextSetAsync());
}
;
return(contestList);
}
public void ThrowOnNullConnectionString()
{
CosmosSqlQueryDefinition sqlQueryDefinition = new CosmosSqlQueryDefinition("select * from s where s.Account = 1234");
sqlQueryDefinition.UseParameter(null, null);
}