Events
Mar 17, 9 PM - Mar 21, 10 AM
Join the meetup series to build scalable AI solutions based on real-world use cases with fellow developers and experts.
Register nowThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
APPLIES TO:
NoSQL
The Azure Cosmos DB query provider performs a best effort mapping from a LINQ query into an Azure Cosmos DB for NoSQL query. If you want to get the NoSQL query that is translated from LINQ, use the ToString()
method on the generated IQueryable
object. The following description assumes a basic familiarity with LINQ. In addition to LINQ, Azure Cosmos DB also supports Entity Framework Core, which works with API for NoSQL.
Note
We recommend using the latest .NET SDK (Microsoft.Azure.Cosmos
) version
The query provider type system supports only the JSON primitive types: numeric
, Boolean
, string
, and null
.
The query provider supports the following scalar expressions:
Constant values, including constant values of the primitive data types at query evaluation time.
Property/array index expressions that refer to the property of an object or an array element. For example:
family.Id;
family.children[0].familyName;
family.children[0].grade;
int n = 1;
family.children[n].grade;
Arithmetic expressions, including common arithmetic expressions on numerical and Boolean values.
2 * family.children[0].grade;
x + y;
String comparison expressions, which include comparing a string value to some constant string value.
mother.familyName.StringEquals("Wakefield");
string s = "Rob";
string e = "in";
string c = "obi";
child.givenName.StartsWith(s);
child.givenName.EndsWith(e);
child.givenName.Contains(c);
Object/array creation expressions, which return an object of compound value type or anonymous type, or an array of such objects. You can nest these values.
new Parent { familyName = "Wakefield", givenName = "Robin" };
new { first = 1, second = 2 }; //an anonymous type with two fields
new int[] { 3, child.grade, 5 };
You can create a LINQ query with GetItemLinqQueryable
. This example shows LINQ query generation and asynchronous execution with a FeedIterator
:
using FeedIterator<Book> setIterator = container.GetItemLinqQueryable<Book>()
.Where(b => b.Title == "War and Peace")
.ToFeedIterator<Book>());
//Asynchronous query execution
while (setIterator.HasMoreResults)
{
foreach(var item in await setIterator.ReadNextAsync()){
{
Console.WriteLine(item.cost);
}
}
The LINQ provider included with the NoSQL .NET SDK supports the following operators:
&&
, ||
, and !
to the NoSQL operatorsAbs
, Acos
, Asin
, Atan
, Ceiling
, Cos
, Exp
, Floor
, Log
, Log10
, Pow
, Round
, Sign
, Sin
, Sqrt
, Tan
, and Truncate
to the equivalent built-in mathematical functions.Concat
, Contains
, Count
, EndsWith
,IndexOf
, Replace
, Reverse
, StartsWith
, SubString
, ToLower
, ToUpper
, TrimEnd
, and TrimStart
to the equivalent built-in string functions.Concat
, Contains
, and Count
to the equivalent built-in array functions.Distance
, IsValid
, IsValidDetailed
, and Within
to the equivalent built-in geospatial functions.Coalesce
and conditional operators. Can translate Contains
to String CONTAINS, ARRAY_CONTAINS, or IN, depending on context.The following examples illustrate how some of the standard LINQ query operators translate to queries in Azure Cosmos DB.
The syntax is input.Select(x => f(x))
, where f
is a scalar expression. The input
, in this case, would be an IQueryable
object.
Select operator, example 1:
LINQ lambda expression
input.Select(family => family.parents[0].familyName);
NoSQL
SELECT VALUE f.parents[0].familyName
FROM Families f
Select operator, example 2:
LINQ lambda expression
input.Select(family => family.children[0].grade + c); // c is an int variable
NoSQL
SELECT VALUE f.children[0].grade + c
FROM Families f
Select operator, example 3:
LINQ lambda expression
input.Select(family => new
{
name = family.children[0].familyName,
grade = family.children[0].grade + 3
});
NoSQL
SELECT VALUE {
"name":f.children[0].familyName,
"grade": f.children[0].grade + 3
}
FROM Families f
The syntax is input.SelectMany(x => f(x))
, where f
is a scalar expression that returns a container type.
LINQ lambda expression
input.SelectMany(family => family.children);
NoSQL
SELECT VALUE child
FROM child IN Families.children
The syntax is input.Where(x => f(x))
, where f
is a scalar expression, which returns a Boolean value.
Where operator, example 1:
LINQ lambda expression
input.Where(family=> family.parents[0].familyName == "Wakefield");
NoSQL
SELECT *
FROM Families f
WHERE f.parents[0].familyName = "Wakefield"
Where operator, example 2:
LINQ lambda expression
input.Where(
family => family.parents[0].familyName == "Wakefield" &&
family.children[0].grade < 3);
NoSQL
SELECT *
FROM Families f
WHERE f.parents[0].familyName = "Wakefield"
AND f.children[0].grade < 3
You can compose the preceding operators to form more powerful queries. Since Azure Cosmos DB supports nested containers, you can concatenate or nest the composition.
The syntax is input(.|.SelectMany())(.Select()|.Where())*
. A concatenated query can start with an optional SelectMany
query, followed by multiple Select
or Where
operators.
Concatenation, example 1:
LINQ lambda expression
input.Select(family => family.parents[0])
.Where(parent => parent.familyName == "Wakefield");
NoSQL
SELECT *
FROM Families f
WHERE f.parents[0].familyName = "Wakefield"
Concatenation, example 2:
LINQ lambda expression
input.Where(family => family.children[0].grade > 3)
.Select(family => family.parents[0].familyName);
NoSQL
SELECT VALUE f.parents[0].familyName
FROM Families f
WHERE f.children[0].grade > 3
Concatenation, example 3:
LINQ lambda expression
input.Select(family => new { grade=family.children[0].grade}).
Where(anon=> anon.grade < 3);
NoSQL
SELECT *
FROM Families f
WHERE ({grade: f.children[0].grade}.grade > 3)
Concatenation, example 4:
LINQ lambda expression
input.SelectMany(family => family.parents)
.Where(parent => parents.familyName == "Wakefield");
NoSQL
SELECT *
FROM p IN Families.parents
WHERE p.familyName = "Wakefield"
The syntax is input.SelectMany(x=>x.Q())
where Q
is a Select
, SelectMany
, or Where
operator.
A nested query applies the inner query to each element of the outer container. One important feature is that the inner query can refer to the fields of the elements in the outer container, like a self-join.
Nesting, example 1:
LINQ lambda expression
input.SelectMany(family=>
family.parents.Select(p => p.familyName));
NoSQL
SELECT VALUE p.familyName
FROM Families f
JOIN p IN f.parents
Nesting, example 2:
LINQ lambda expression
input.SelectMany(family =>
family.children.Where(child => child.familyName == "Jeff"));
NoSQL
SELECT *
FROM Families f
JOIN c IN f.children
WHERE c.familyName = "Jeff"
Nesting, example 3:
LINQ lambda expression
input.SelectMany(family => family.children.Where(
child => child.familyName == family.parents[0].familyName));
NoSQL
SELECT *
FROM Families f
JOIN c IN f.children
WHERE c.familyName = f.parents[0].familyName
Events
Mar 17, 9 PM - Mar 21, 10 AM
Join the meetup series to build scalable AI solutions based on real-world use cases with fellow developers and experts.
Register now