LINQ 到 SQL 转换LINQ to SQL translation

适用于： SQL API

Azure Cosmos DB 查询提供程序执行从 LINQ 查询到 Cosmos DB SQL 查询的最有效映射。The Azure Cosmos DB query provider performs a best effort mapping from a LINQ query into a Cosmos DB SQL query. 如果要获取从 LINQ 转换的 SQL 查询，请对生成的 IQueryable 对象使用 ToString() 方法。If you want to get the SQL query that is translated from LINQ, use the ToString() method on the generated IQueryableobject. 以下内容假设你对 LINQ 有一个基本的了解。The following description assumes a basic familiarity with LINQ. 除 LINQ 外，Azure Cosmos DB 还支持适用于 SQL API 的 Entity Framework Core。In addition to LINQ, Azure Cosmos DB also supports Entity Framework Core which works with SQL API.

查询提供程序类型系统仅支持 JSON 基元类型：数字、布尔值、字符串和 null。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;
family.children[n].grade; //n is an int variable

  • 算术表达式，包括针对数值和布尔值运行的常见算术表达式。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 == "Wakefield";
child.givenName == s; //s is a string variable

  • 对象/数组创建表达式，返回复合值类型或匿名类型的对象，或此类对象组成的数组。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 };

使用 LINQUsing LINQ

可以使用 GetItemLinqQueryable 创建 LINQ 查询。You can create a LINQ query with GetItemLinqQueryable. 此示例演示如何使用 FeedIterator 生成 LINQ 查询并进行异步执行：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);
         }
       }
     }
 }

支持的 LINQ 运算符Supported LINQ operators

SQL .NET SDK 随附的 LINQ 提供程序支持以下运算符：The LINQ provider included with the SQL .NET SDK supports the following operators:

  • Select：投影转换为 SELECT（包括对象构造）。Select: Projections translate to SELECT, including object construction.
  • Where：筛选器转换为 WHERE，并且支持 &&、|| 和 ! 到 SQL 运算符的转换Where: Filters translate to WHERE, and support translation between &&, ||, and ! to the SQL operators
  • SelectMany：允许将数组展开到 JOIN 子句。SelectMany: Allows unwinding of arrays to the JOIN clause. 用于将表达式链接或嵌套到对数组元素应用的筛选器。Use to chain or nest expressions to filter on array elements.
  • OrderBy 和 OrderByDescending：使用 ASC 或 DESC 转换为 ORDER BY。OrderBy and OrderByDescending: Translate to ORDER BY with ASC or DESC.
  • CompareTo：转换为范围比较。CompareTo: Translates to range comparisons. 通常用于字符串，因为它们在 .NET 中不可进行比较。Commonly used for strings, since they're not comparable in .NET.
  • Skip 和 Take：转换为 OFFSET 和 LIMIT，用于限制查询的结果和进行分页。Skip and Take: Translates to OFFSET and LIMIT for limiting results from a query and doing pagination.
  • 数学函数：支持从 .NET Abs、Acos、Asin、Atan、Ceiling、Cos、Exp、Floor、Log、Log10、Pow、Round、Sign、Sin、Sqrt、Tan 和 Truncate 转换为等效的 内置数学函数。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.
  • 字符串函数：支持从 .NET Concat、Contains、Count、EndsWith、IndexOf、Replace、Reverse、StartsWith、SubString、ToLower、ToUpper、TrimEnd 和 TrimStart 转换为等效的 内置字符串函数。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.
  • 数组函数：支持从 .NET Concat、Contains 和 Count 转换为等效的 内置数组函数。Array functions: Supports translation from .NET Concat, Contains, and Count to the equivalent built-in array functions.
  • 地理空间扩展函数：支持从存根方法 Distance、IsValid、IsValidDetailed 和 Within 转换为等效的 内置地理空间函数。Geospatial Extension functions: Supports translation from stub methods Distance, IsValid, IsValidDetailed, and Within to the equivalent built-in geospatial functions.
  • 用户定义的函数扩展函数：支持从存根方法 UserDefinedFunctionProvider.Invoke 转换为相应的 用户定义的函数。User-Defined Function Extension function: Supports translation from the stub method UserDefinedFunctionProvider.Invoke to the corresponding user-defined function.
  • 其他：支持 Coalesce 和条件 运算符的转换。Miscellaneous: Supports translation of Coalesce and conditional operators. 可以根据上下文将 Contains 转换为字符串 CONTAINS、ARRAY_CONTAINS 或 IN。Can translate Contains to String CONTAINS, ARRAY_CONTAINS, or IN, depending on context.

示例Examples

以下示例演示了一些标准 LINQ 查询运算符如何转换为 Azure Cosmos DB 中的查询。The following examples illustrate how some of the standard LINQ query operators translate to queries in Azure Cosmos DB.

Select 运算符Select operator

语法为 input.Select(x => f(x))，其中 f 是一个标量表达式。The syntax is input.Select(x => f(x)), where f is a scalar expression. 这种情况下，input 将为 IQueryable 对象。The input, in this case, would be an IQueryable object.

Select 运算符，示例 1：Select operator, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Select(family => family.parents[0].familyName);

  • SQLSQL

      SELECT VALUE f.parents[0].familyName
  FROM Families f

Select 运算符，示例 2：Select operator, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Select(family => family.children[0].grade + c); // c is an int variable

  • SQLSQL

      SELECT VALUE f.children[0].grade + c
  FROM Families f

Select 运算符，示例 3：Select operator, example 3:

  • LINQ Lambda 表达式LINQ lambda expression

    input.Select(family => new
{
    name = family.children[0].familyName,
    grade = family.children[0].grade + 3
});

  • SQLSQL

      SELECT VALUE {"name":f.children[0].familyName,
                "grade": f.children[0].grade + 3 }
  FROM Families f

SelectMany 运算符SelectMany operator

语法为 input.SelectMany(x => f(x))，其中 f 是返回容器类型的标量表达式。The syntax is input.SelectMany(x => f(x)), where f is a scalar expression that returns a container type.

  • LINQ Lambda 表达式LINQ lambda expression

      input.SelectMany(family => family.children);

  • SQLSQL

      SELECT VALUE child
  FROM child IN Families.children

Where 运算符Where operator

语法为 input.Where(x => f(x))，其中 f 是返回布尔值的标量表达式。The syntax is input.Where(x => f(x)), where f is a scalar expression, which returns a Boolean value.

Where 运算符，示例 1：Where operator, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Where(family=> family.parents[0].familyName == "Wakefield");

  • SQLSQL

      SELECT *
  FROM Families f
  WHERE f.parents[0].familyName = "Wakefield"

Where 运算符，示例 2：Where operator, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Where(
      family => family.parents[0].familyName == "Wakefield" &&
      family.children[0].grade < 3);

  • SQLSQL

      SELECT *
  FROM Families f
  WHERE f.parents[0].familyName = "Wakefield"
  AND f.children[0].grade < 3

复合 SQL 查询Composite SQL queries

将上述运算符组合到一起可以构成更强大的查询。You can compose the preceding operators to form more powerful queries. 由于 Cosmos DB 支持嵌套的容器，因此你可以连接或嵌套这种组合。Since Cosmos DB supports nested containers, you can concatenate or nest the composition.

串联Concatenation

语法为 input(.|.SelectMany())(.Select()|.Where())*。The syntax is input(.|.SelectMany())(.Select()|.Where())*. 连接的查询可以使用可选的 SelectMany 查询开头，后接多个 Select 或 Where 运算符。A concatenated query can start with an optional SelectMany query, followed by multiple Select or Where operators.

连接，示例 1：Concatenation, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Select(family=>family.parents[0])
      .Where(parent => parent.familyName == "Wakefield");

  • SQLSQL

      SELECT *
  FROM Families f
  WHERE f.parents[0].familyName = "Wakefield"

连接，示例 2：Concatenation, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Where(family => family.children[0].grade > 3)
      .Select(family => family.parents[0].familyName);

  • SQLSQL

      SELECT VALUE f.parents[0].familyName
  FROM Families f
  WHERE f.children[0].grade > 3

连接，示例 3：Concatenation, example 3:

  • LINQ Lambda 表达式LINQ lambda expression

      input.Select(family => new { grade=family.children[0].grade}).
      Where(anon=> anon.grade < 3);

  • SQLSQL

      SELECT *
  FROM Families f
  WHERE ({grade: f.children[0].grade}.grade > 3)

连接，示例 4：Concatenation, example 4:

  • LINQ Lambda 表达式LINQ lambda expression

      input.SelectMany(family => family.parents)
      .Where(parent => parents.familyName == "Wakefield");

  • SQLSQL

      SELECT *
  FROM p IN Families.parents
  WHERE p.familyName = "Wakefield"

嵌套Nesting

语法为 input.SelectMany(x=>x.Q())，其中 Q 是 Select、SelectMany 或 Where 运算符。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.

嵌套，示例 1：Nesting, example 1:

  • LINQ Lambda 表达式LINQ lambda expression

      input.SelectMany(family=>
      family.parents.Select(p => p.familyName));

  • SQLSQL

      SELECT VALUE p.familyName
  FROM Families f
  JOIN p IN f.parents

嵌套，示例 2：Nesting, example 2:

  • LINQ Lambda 表达式LINQ lambda expression

      input.SelectMany(family =>
      family.children.Where(child => child.familyName == "Jeff"));

  • SQLSQL

      SELECT *
  FROM Families f
  JOIN c IN f.children
  WHERE c.familyName = "Jeff"

嵌套，示例 3：Nesting, example 3:

  • LINQ Lambda 表达式LINQ lambda expression

      input.SelectMany(family => family.children.Where(
      child => child.familyName == family.parents[0].familyName));

  • SQLSQL

      SELECT *
  FROM Families f
  JOIN c IN f.children
  WHERE c.familyName = f.parents[0].familyName

后续步骤Next steps