LINQ to NoSQL translation in Azure Cosmos DB for NoSQL
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:
- Select: Projections translate to SELECT, including object construction.
- Where: Filters translate to WHERE, and support translation between
&&
,||
, and!
to the NoSQL operators - SelectMany: Allows unwinding of arrays to the JOIN clause. Use to chain or nest expressions to filter on array elements.
- OrderBy and OrderByDescending: Translate to ORDER BY with ASC or DESC.
- Count, Sum, Min, Max, and Average operators for aggregation, and their async equivalents CountAsync, SumAsync, MinAsync, MaxAsync, and AverageAsync.
- CompareTo: Translates to range comparisons. This operator is commonly used for strings, since they're not comparable in .NET.
- Skip and Take: Translates to OFFSET and LIMIT for limiting results from a query and doing pagination.
- Math functions: Supports translation from .NET
Abs
,Acos
,Asin
,Atan
,Ceiling
,Cos
,Exp
,Floor
,Log
,Log10
,Pow
,Round
,Sign
,Sin
,Sqrt
,Tan
, andTruncate
to the equivalent built-in mathematical functions. - String functions: Supports translation from .NET
Concat
,Contains
,Count
,EndsWith
,IndexOf
,Replace
,Reverse
,StartsWith
,SubString
,ToLower
,ToUpper
,TrimEnd
, andTrimStart
to the equivalent built-in string functions. - Array functions: Supports translation from .NET
Concat
,Contains
, andCount
to the equivalent built-in array functions. - Geospatial Extension functions: Supports translation from stub methods
Distance
,IsValid
,IsValidDetailed
, andWithin
to the equivalent built-in geospatial functions. - User-Defined Function Extension function: Supports translation from the stub method CosmosLinq.InvokeUserDefinedFunction to the corresponding user-defined function.
- Miscellaneous: Supports translation of
Coalesce
and conditional operators. Can translateContains
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