DocumentDB SQL Linq to SQL Translation - DocumentDB SQL

What is the Link to SQL Translation in DocumentDB SQL?

In DocumentDB , SQL is used to query the documents. In .NET development ,there is LINQ provider which can generate appropriate SQL from LINQ query.

Supported Data Types

In DocumentDB, the LINQ provider included with the DocumentDB .NET SDK supports all the JSON primitive types which are as follows :

• Numeric
• Boolean
• String
• Null

Supported Expression

The scalar expressions supported in the LINQ provider included with the DocumentDB .NET SDK are as follows:

• Constant Values : Includes constant values of the primitive data types.
• Property/Array Index Expressions : Expressions refer to the property of an object or an array element.
• Arithmetic Expressions : Includes common arithmetic expressions on numerical and Boolean values.
• String Comparison Expression : Includes comparing a string value to some constant string value.
• Object/Array Creation Expression : Returns an object of compound value type or anonymous type or an array of such objects. These values can be nested.

Supported LINQ Operators

The list of LINQ operators supported in the LINQ provider included with the DocumentDB .NET SDK are:

• Select : Projections translate to the SQL SELECT including object construction.
• Where : Filters translate to the SQL WHERE, and support translation between && , || and ! to the SQL operators.
• SelectMany : Allows unwinding of arrays to the SQL JOIN clause. Can be used to chain/nest expressions to filter array elements.
• OrderBy and OrderByDescending : Translates to ORDER BY ascending/descending.
• CompareTo : Translates to range comparisons. Commonly used for strings since they’re not comparable in .NET.
• Take : Translates to the SQL TOP for limiting results from a query.
• Math Functions : Supports translation from .NET’s Abs, Acos, Asin, Atan, Ceiling, Cos, Exp, Floor, Log, Log10, Pow, Round, Sign, Sin, Sqrt, Tan, Truncate to the equivalent SQL built-in functions.
• String Functions : Supports translation from .NET’s Concat, Contains, EndsWith, IndexOf, Count, ToLower, TrimStart, Replace, Reverse, TrimEnd, StartsWith, SubString, ToUpper to the equivalent SQL built-in functions.
• Array Functions : Supports translation from .NET’s Concat, Contains, and Count to the equivalent SQL built-in functions.
• Geospatial Extension Functions : Supports translation from stub methods Distance, Within, IsValid, and IsValidDetailed to the equivalent SQL built-in functions.
• User-Defined Extension Function : Supports translation from the stub method UserDefinedFunctionProvider.Invoke to the corresponding user-defined function.
• Miscellaneous : Supports translation of coalesce and conditional operators. Can translate Contains to String CONTAINS, ARRAY_CONTAINS or the SQL IN depending on context.

The usage the .Net SDK is illustrated in the following example. Following are the three documents which we will be consider for this example.

New Customer 1

{ "name": "New Customer 1", "address": { "addressType": "Main Office", "addressLine1": "123 Main Street", "location": { "city": "Brooklyn", "stateProvinceName": "New York" }, "postalCode": "11229", "countryRegionName": "United States" }, }

New Customer 2

{ "name": "New Customer 2", "address": { "addressType": "Main Office", "addressLine1": "678 Main Street", "location": { "city": "London", "stateProvinceName": " London " }, "postalCode": "11229", "countryRegionName": "United Kingdom" }, }

New Customer 3

{ "name": "New Customer 3", "address": { "addressType": "Main Office", "addressLine1": "12 Main Street", "location": { "city": "Brooklyn", "stateProvinceName": "New York" }, "postalCode": "11229", "countryRegionName": "United States" }, }

The query using LINQ is explained in the following code. We've defined a LINQ query in q, but it won't execute until we run to list.

private static void QueryDocumentsWithLinq(DocumentClient client) { Console.WriteLine(); Console.WriteLine("**** Query Documents (LINQ) ****"); Console.WriteLine(); Console.WriteLine("Quering for US customers (LINQ)"); var q = from d in client.CreateDocumentQuery<Customer>(collection.DocumentsLink) where d.Address.CountryRegionName == "United States" select new { Id = d.Id, Name = d.Name, City = d.Address.Location.City }; var documents = q.ToList(); Console.WriteLine("Found {0} US customers", documents.Count); foreach (var document in documents) { var d = document as dynamic; Console.WriteLine(" Id: {0}; Name: {1}; City: {2}", d.Id, d.Name, d.City); } Console.WriteLine(); }

For DocumentDB ,the SDK will convert our LINQ query into SQL syntax, generating a SELECT and WHERE clause based on our LINQ syntax.
The above queries are called from the CreateDocumentClient task.

private static async Task CreateDocumentClient() { // Create a new instance of the DocumentClient using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) { database = client.CreateDatabaseQuery("SELECT * FROM c WHERE c.id = 'myfirstdb'").AsEnumerable().First(); collection = client.CreateDocumentCollectionQuery(database.CollectionsLink, "SELECT * FROM c WHERE c.id = 'MyCollection'").AsEnumerable().First(); QueryDocumentsWithLinq(client); } }

the above code is executed and the following output is produced.

**** Query Documents (LINQ) **** Quering for US customers (LINQ) Found 2 US customers Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1; City: Brooklyn Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1; City: Brooklyn