LINQ to NoSQL translation in Azure Cosmos DB for NoSQL

  • Article

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 };

Using LINQ

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);
    }
}

Supported LINQ operators

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, and Truncate 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, and TrimStart to the equivalent built-in string functions.
  • Array functions: Supports translation from .NET Concat, Contains, and Count to the equivalent built-in array functions.
  • Geospatial Extension functions: Supports translation from stub methods Distance, IsValid, IsValidDetailed, and Within 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 translate Contains to String CONTAINS, ARRAY_CONTAINS, or IN, depending on context.

Examples

The following examples illustrate how some of the standard LINQ query operators translate to queries in Azure Cosmos DB.

Select operator

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

SelectMany operator

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

Where operator

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

Composite NoSQL queries

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.

Concatenation

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"

Nesting

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

Related content