public async Task LinqParameterisedTest1()
{
//Creating items for query.
IList <ToDoActivity> itemList = await ToDoActivity.CreateRandomItems(container : this.Container, pkCount : 10, perPKItemCount : 1, randomPartitionKey : true);
string queryText = "SELECT VALUE item0 FROM root JOIN item0 IN root[\"children\"] WHERE (((((root[\"CamelCase\"] = @param1)" +
" AND (root[\"description\"] = @param2)) AND (root[\"taskNum\"] < @param3))" +
" AND (root[\"valid\"] = @param4)) AND (item0 = @param5)) ";
ToDoActivity child1 = new ToDoActivity {
id = "child1", taskNum = 30
};
string description = "CreateRandomToDoActivity";
string camelCase = "camelCase";
int taskNum = 100;
bool valid = true;
// Passing incorrect boolean value, generating queryDefinition, updating parameter and verifying new result
valid = false;
IOrderedQueryable <ToDoActivity> linqQueryable = this.Container.GetItemLinqQueryable <ToDoActivity>(true);
IQueryable <ToDoActivity> queriable = linqQueryable.Where(item => item.CamelCase == camelCase)
.Where(item => item.description == description)
.Where(item => item.taskNum < taskNum)
.Where(item => item.valid == valid)
.SelectMany(item => item.children)
.Where(child => child == child1);
Dictionary <object, string> parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
parameters.Add(description, "@param2");
parameters.Add(taskNum, "@param3");
parameters.Add(valid, "@param4");
parameters.Add(child1, "@param5");
QueryDefinition queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(5, queryDefinition.ToSqlQuerySpec().Parameters.Count);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
string paramNameForUpdate = parameters[valid];
valid = true;
queryDefinition.WithParameter(paramNameForUpdate, valid);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
// Passing incorrect string value, generating queryDefinition, updating parameter and verifying new result
description = "wrongDescription";
linqQueryable = this.Container.GetItemLinqQueryable <ToDoActivity>(true);
queriable = linqQueryable.Where(item => item.CamelCase == "camelCase")
.Where(item => item.description == description)
.Where(item => item.taskNum < 100)
.Where(item => item.valid == true)
.SelectMany(item => item.children)
.Where(child => child == child1);
parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
parameters.Add(description, "@param2");
parameters.Add(taskNum, "@param3");
parameters.Add(valid, "@param4");
parameters.Add(child1, "@param5");
queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(5, queryDefinition.ToSqlQuerySpec().Parameters.Count);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
paramNameForUpdate = parameters[description];
description = "CreateRandomToDoActivity";
queryDefinition.WithParameter(paramNameForUpdate, description);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
// Passing incorrect number value, generating queryDefinition, updating parameter and verifying new result
taskNum = 10;
linqQueryable = this.Container.GetItemLinqQueryable <ToDoActivity>(true);
queriable = linqQueryable.Where(item => item.CamelCase == "camelCase")
.Where(item => item.description == "CreateRandomToDoActivity")
.Where(item => item.taskNum < taskNum)
.Where(item => item.valid == true)
.SelectMany(item => item.children)
.Where(child => child == child1);
parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
parameters.Add(description, "@param2");
parameters.Add(taskNum, "@param3");
parameters.Add(valid, "@param4");
parameters.Add(child1, "@param5");
queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(5, queryDefinition.ToSqlQuerySpec().Parameters.Count);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
paramNameForUpdate = parameters[taskNum];
taskNum = 100;
queryDefinition.WithParameter(paramNameForUpdate, taskNum);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
// Passing incorrect object value, generating queryDefinition, updating parameter and verifying new result
child1.taskNum = 40;
queriable = linqQueryable.Where(item => item.CamelCase == "camelCase")
.Where(item => item.description == "CreateRandomToDoActivity")
.Where(item => item.taskNum < taskNum)
.Where(item => item.valid == true)
.SelectMany(item => item.children)
.Where(child => child == child1);
parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
parameters.Add(description, "@param2");
parameters.Add(taskNum, "@param3");
parameters.Add(valid, "@param4");
parameters.Add(child1, "@param5");
queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(5, queryDefinition.ToSqlQuerySpec().Parameters.Count);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
paramNameForUpdate = parameters[child1];
child1.taskNum = 30;
queryDefinition.WithParameter(paramNameForUpdate, child1);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
}
public async Task LinqParameterisedTest2()
{
//Creating items for query.
IList <ToDoActivity> itemList = await ToDoActivity.CreateRandomItems(container : this.Container, pkCount : 10, perPKItemCount : 1, randomPartitionKey : true);
IOrderedQueryable <ToDoActivity> linqQueryable = this.Container.GetItemLinqQueryable <ToDoActivity>(true);
//Test same values in two where clause
string camelCase = "wrongValue";
IQueryable <ToDoActivity> queriable = linqQueryable
.Where(item => item.CamelCase == camelCase)
.Where(item => item.description != camelCase);
Dictionary <object, string> parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
QueryDefinition queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(1, queryDefinition.ToSqlQuerySpec().Parameters.Count);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
camelCase = "camelCase";
queryDefinition.WithParameter("@param1", camelCase);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
string queryText = "SELECT VALUE root FROM root WHERE (root[\"children\"] = [@param1, @param2]) ";
//Test array in query, array items will be parametrized
ToDoActivity child1 = new ToDoActivity {
id = "child1", taskNum = 30
};
ToDoActivity child2 = new ToDoActivity {
id = "child2", taskNum = 40
};
ToDoActivity[] children = new ToDoActivity[]
{ child1,
child2 };
queriable = linqQueryable
.Where(item => item.children == children);
parameters = new Dictionary <object, string>();
parameters.Add(child1, "@param1");
parameters.Add(child2, "@param2");
queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
//updating child to wrong value, result in query returning 0 results
child1.taskNum = 50;
queryDefinition.WithParameter("@param1", child1);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
//Test orderby, skip, take, distinct, these will not get parameterized.
queryText = "SELECT VALUE root FROM root WHERE (root[\"CamelCase\"] = @param1) ORDER BY" +
" root[\"taskNum\"] ASC OFFSET @param2 LIMIT @param3 ";
queriable = linqQueryable
.Where(item => item.CamelCase == camelCase)
.OrderBy(item => item.taskNum)
.Skip(5)
.Take(4);
parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
parameters.Add(5, "@param2");
parameters.Add(4, "@param3");
queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(4, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
queryDefinition.WithParameter("@param2", 10);
queryDefinition.WithParameter("@param3", 0);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
queryText = "SELECT VALUE root FROM root WHERE (root[\"CamelCase\"] != @param1) ";
camelCase = "\b\n";
queriable = linqQueryable
.Where(item => item.CamelCase != camelCase);
parameters = new Dictionary <object, string>();
parameters.Add(camelCase, "@param1");
queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual("\b\n", queryDefinition.ToSqlQuerySpec().Parameters[0].Value);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(10, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
}
public async Task LinqParameterisedTest3()
{
string queryText = "SELECT VALUE item0 FROM root JOIN item0 IN root[\"children\"]" +
" WHERE ((((((((((((((root[\"id\"] = @param1) AND (root[\"stringValue\"] = @Param2))" +
" AND (root[\"sbyteValue\"] = @param3))" +
" AND (root[\"byteValue\"] = @param4))" +
" AND (root[\"shortValue\"] = @param5))" +
" AND (root[\"uintValue\"] = @param6))" +
" AND (root[\"longValue\"] = @param7))" +
" AND (root[\"ulongValue\"] = @Param8))" +
" AND (root[\"floatValue\"] = @param9))" +
" AND (root[\"doubleValue\"] = @param10))" +
" AND (root[\"decimaleValue\"] = @param11))" +
" AND (root[\"ushortValue\"] = @param15))" +
" AND (root[\"booleanValue\"] = @param12))" +
" AND (item0 = @param13))";
string id = "testId";
string pk = "testPk";
string stringValue = "testStringValue";
sbyte sbyteValue = 5;
byte byteValue = 6;
short shortValue = 7;
int intValue = 8;
uint uintValue = 9;
long longValue = 10;
ulong ulongValue = 11;
float floatValue = 12;
double doubleValue = 13;
decimal decimaleValue = 14;
ushort ushortValue = 15;
bool booleanValue = true;
NumberLinqItem child = new NumberLinqItem {
id = "childId"
};
NumberLinqItem[] children = new NumberLinqItem[]
{
child
};
NumberLinqItem parametrizedLinqItem = new NumberLinqItem
{
id = id,
status = pk,
stringValue = stringValue,
sbyteValue = sbyteValue,
byteValue = byteValue,
shortValue = shortValue,
intValue = intValue,
uintValue = uintValue,
longValue = longValue,
ulongValue = ulongValue,
floatValue = floatValue,
doubleValue = doubleValue,
decimaleValue = decimaleValue,
ushortValue = ushortValue,
booleanValue = booleanValue,
children = children
};
await this.Container.CreateItemAsync(parametrizedLinqItem, new PartitionKey(pk));
IOrderedQueryable <NumberLinqItem> linqQueryable = this.Container.GetItemLinqQueryable <NumberLinqItem>(true);
IQueryable <NumberLinqItem> queriable = linqQueryable
.Where(item => item.id == id)
.Where(item => item.stringValue == stringValue)
.Where(item => item.sbyteValue == sbyteValue)
.Where(item => item.byteValue == byteValue)
.Where(item => item.shortValue == shortValue)
.Where(item => item.uintValue == uintValue)
.Where(item => item.longValue == longValue)
.Where(item => item.ulongValue == ulongValue)
.Where(item => item.floatValue == floatValue)
.Where(item => item.doubleValue == doubleValue)
.Where(item => item.decimaleValue == decimaleValue)
.Where(item => item.ushortValue == ushortValue)
.Where(item => item.booleanValue == booleanValue)
.SelectMany(item => item.children)
.Where(ch => ch == child);
Dictionary <object, string> parameters = new Dictionary <object, string>();
parameters.Add(id, "@param1");
parameters.Add(stringValue, "@Param2");
parameters.Add((int)sbyteValue, "@param3"); // Linq converts sbyte to int32, therefore adding int in cast
parameters.Add((int)byteValue, "@param4"); // Linq converts byte to int32, therefore adding int in cast
parameters.Add((int)shortValue, "@param5"); // Linq converts short to int32, therefore adding int in cast
parameters.Add(uintValue, "@param6");
parameters.Add(longValue, "@param7");
parameters.Add(ulongValue, "@Param8");
parameters.Add(floatValue, "@param9");
parameters.Add(doubleValue, "@param10");
parameters.Add(decimaleValue, "@param11");
parameters.Add(booleanValue, "@param12");
parameters.Add(child, "@param13");
parameters.Add((int)ushortValue, "@param15"); // Linq converts ushort to int32, therefore adding int in cast
QueryDefinition queryDefinition = queriable.ToQueryDefinition(parameters);
Assert.AreEqual(queryText, queryDefinition.ToSqlQuerySpec().QueryText);
Assert.AreEqual(1, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
queryDefinition.WithParameter("@param3", 6);
Assert.AreEqual(0, (await this.FetchResults <ToDoActivity>(queryDefinition)).Count);
}