In DocumentDB, we sincerely use SQL to query documents. If we are doing .NET improvement, there may be also a LINQ company that can be used and that can generate suitable SQL from a LINQ question.
Supported Data Types
In DocumentDB, all JSON primitive types are supported within the LINQ provider included with the DocumentDB .NET SDK that are as follows −
- Numeric
- Boolean
- String
- Null
Supported Expression
The following scalar expressions are supported within the LINQ company blanketed with the DocumentDB .NET SDK.
- Constant Values − Includes constant values of the primitive information types.
- Property/Array Index Expressions − Expressions talk over with the assets of an object or an array element.
- Arithmetic Expressions − Includes common mathematics expressions on numerical and Boolean values.
- String Comparison Expression − Includes evaluating a string price to a few steady string value.
- Object/Array Creation Expression − Returns an item of compound fee kind or anonymous kind or an array of such gadgets. These values can be nested.
Supported LINQ Operators
Here is a list of supported LINQ operators in the LINQ provider covered with the DocumentDB .NET SDK.
- Select − Projections translate to the SQL SELECT which includes item creation.
- Where − Filters translate to the SQL WHERE, and guide translation betweenfilter array factors.
- OrderBy and OrderByDescending − Translates to ORDER BY ascending/descending.
- CompareTo − Translates to range comparisons. Commonly used for strings given that they’re not similar 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 features.
- String Functions − Supports translation from .NET’s Concat, Contains, EndsWith, IndexOf, Count, ToLower, TrimStart, Replace, Reverse, TrimEnd, StartsWith, SubString, ToUpper to the equivalent SQL integrated features.
- Array Functions − Supports translation from .NET’s Concat, Contains, and Count to the equal SQL built-in capabilities.
- Geospatial Extension Functions − Supports translation from stub techniques Distance, Within, IsValid, and IsValidDetailed to the equal SQL built-in capabilities.
- User-Defined Extension Function − Supports translation from the stub technique UserDefinedFunctionProvider.Invoke to the corresponding person-defined characteristic.
- Miscellaneous − Supports translation of coalesce and conditional operators. Can translate Contains to String CONTAINS, ARRAY_CONTAINS or the SQL IN depending on context.
Let’s take a look at an instance where we will be the usage of the .Net SDK. Following are the three files which we will be don't forget for this case.
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"
},
}
Following is the code in which we query the usage of LINQ. We've described a LINQ query in q, but it may not execute until we run .ToList on it.
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();
}
The SDK will convert our LINQ query into SQL syntax for DocumentDB, producing a SELECT and WHERE clause based on our LINQ syntax.
Let’s call the above queries from the CreateDocumentClient mission.
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);
}
}
When the above code is done, it produces the subsequent output.
**** 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