Lambda.Azure.CosmosDb.OData.Sql 2.0.23

There is a newer version of this package available.
See the version list below for details.
dotnet add package Lambda.Azure.CosmosDb.OData.Sql --version 2.0.23
NuGet\Install-Package Lambda.Azure.CosmosDb.OData.Sql -Version 2.0.23
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Lambda.Azure.CosmosDb.OData.Sql" Version="2.0.23" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Lambda.Azure.CosmosDb.OData.Sql --version 2.0.23
#r "nuget: Lambda.Azure.CosmosDb.OData.Sql, 2.0.23"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Lambda.Azure.CosmosDb.OData.Sql as a Cake Addin
#addin nuget:?package=Lambda.Azure.CosmosDb.OData.Sql&version=2.0.23

// Install Lambda.Azure.CosmosDb.OData.Sql as a Cake Tool
#tool nuget:?package=Lambda.Azure.CosmosDb.OData.Sql&version=2.0.23

Microsoft.Azure.Documents.OData.Sql

Converts OData V4 queries to DocumentDB SQL queries.

Release Notes

  • 2.0.23 Added support for collection condition based on id
  • 2.0.21 Added support for collections at deeper levels
  • 2.0.19 Added support for SELECT VALUE c
  • 2.0.18 Added support for JOIN for entity with Id
  • 2.0.16 Added support for JOIN
  • 2.0.4 Added support for ARRAY_CONTAINS
  • 2.0.3 Added support for DateTimeOffset
  • 2.0.2 Added support for functions: length(), indexof(), substring(), trim(), concat()
  • 2.0.1 Added support for functions: contains(), startswith(), endswith(), toupper() and tolower()
  • 2.0.0 Breaking changes: Simplified usage with newly introuduced class ODataToSqlTranslator
  • 1.0.0 Initial release

Summary

This package supports most of the intersectional functionalities provided by OData V4 and DocumentDB SQL. For example, if you have a class looks like:

public class Company {
  public string englishName,
  public string countryCode,
  public int    revenue
}

To query all companies whose englishName contains "Limited", and sort them by countryCode in descending order, then select the revenue property from top 5 results, you can issue an OData query to your web service:

http://localhost/Company?$select=revenue&$filter=contains(englishName, 'Limited')&$orderby=countryCode desc&$top=5

The above query will then be translated to DocumentDB SQL:

SELECT TOP 5 c.revenue FROM c WHERE CONTAINS(c.englishName,'Limited') ORDER BY c.countryCode DESC 

Supported OData to DocumentDB SQL mappings:

System Query Options::

$select ⇒ SELECT

$filter ⇒ WHERE

$top ⇒ TOP

$orderby ⇒ ORDER BY

Built-in Query Functions

contains()(field, 'value') ⇒ CONTAINS(c.field, 'value')

startswith()(field, 'value') ⇒ STARTSWITH(c.field, 'value')

endswith()(field, 'value') ⇒ ENDSWITH(c.field, 'value')

toupper()(field, 'value') ⇒ UPPER(c.field, 'value')

tolower()(field, 'value') ⇒ LOWER(c.field, 'value')

length()(field) ⇒ LENGTH(c.field)

indexof(field,'value') ⇒ INDEX_OF(c.field,'value')

substring(field,idx1,idx2) ⇒ SUBSTRING(c.field,idx1,idx2)

trim(field) ⇒ LTRIM(RTRIM(c.englishName))

concat(field,'value') ⇒ CONCAT(c.englishName,'value')

array_field/any(f:f eq 'value') ⇒ ARRAY_CONTAINS(c.array_field,'value')

collection_field/any(f:f.property op 'value') ⇒ JOIN f in c.collection_field WHERE f.property op 'value'

Installing

The nuget package of this project is published on Nuget.org Download Page. To install in Visual Studio Package Manager Console, run command:

PM> Install-Package Lambda.Azure.CosmosDb.OData.Sql

Usage

After installation, you can include the binary in your *.cs file by

using Microsoft.Azure.Documents.OData.Sql;

-You can find a complete set of examles in ODataToSqlSamples.cs-

There are only two classes you need to work with in order to get the translation done: SQLQueryFormatter and ODataToSqlTranslatorhttps://github.com/aboo/azure-documentdb-odata-sql/blob/master/azure-documentdb-odata-sql/ODataToSqlTranslator/ODataToSqlTranslator.cs).

SQLQueryFormatter

SQLQueryFormatter is where we do the property and function name translation, such as translating 'propertyName' to 'c.propertyName', or 'contains()' to 'CONTAINS()'. This class inherits from QueryFormatterBase, which abstractly defines all required translations. One can derive from QueryFormatterBase to implement their own translation per need.

ODataToSqlTranslator

ODataToSqlTranslator does the overall translation, by taking in an implementation of QueryFormatterBase. Because the specific translation is defined in QueryFormatter, the translation performs differently according to the implementation in QueryFormatter. The default QueryFormatter is SQLQueryFormatter mentioned above:

var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());

Once we have an instance of ODataToSqlTranslator, we can call .Translate(ODataQueryOptions, TranslateOptions, string) method:

var translatedSQL = oDataToSqlTranslator.(oDataQueryOptions, TranslateOptions.ALL, additionalWhereClause: null);

In the above example, the ODataQueryOptions is a fundamental class provided by ASP.NET Web API 2, there you can find detailed instruction on ODataQueryOptions. Additionally, you can also find ODataQueryOptions usage in the ODataToSqlSamples file. The second parameter is TranslateOptions, which defines what queries to be translated. Define TranslateOptions are:

SELECT_CLAUSE, JOIN_CLAUSE, WHERE_CLAUSE, ORDERBY_CLAUSE, TOP_CLAUSE, ALL

The options can be combined with bit operators such as (TranslateOptions.SELECT_CLAUSE | TranslateOptions.WHERE_CLAUSE). One common usage is (TranslateOptions.ALL & ~TranslateOptions.TOP), this combination enables all translation but TOP. The reason to disable TOP is that when performing pagination, DocumentDB ignores continuation token in FeedOptions if TOP exists. Therefore, the best practice is to use FeedOptions to perform TOP operation in DocumentDB.

Authors

  • Aboo Azarnoush - Lambda Solutions
  • Ziyou Zheng - Microsoft Universal Store Team -
Product Compatible and additional computed target framework versions.
.NET Framework net462 is compatible.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
4.1.0 11,186 2/22/2022
3.0.87 1,208 11/10/2021
3.0.83 430 11/2/2021
3.0.80 5,502 9/21/2020
3.0.70 7,381 9/3/2019
2.0.40 3,164 6/26/2018
2.0.39 974 6/21/2018
2.0.37 1,067 6/18/2018
2.0.35 948 6/15/2018
2.0.33 2,452 6/6/2018
2.0.31 1,050 5/14/2018
2.0.29 1,115 3/18/2018
2.0.28 963 3/18/2018
2.0.24 968 3/17/2018
2.0.23 1,025 3/17/2018
2.0.21 1,219 2/26/2018
2.0.19 1,080 2/11/2018
2.0.18 1,000 2/11/2018
2.0.16 955 2/11/2018
2.0.4 1,020 2/6/2018
2.0.3 1,093 1/23/2018

Check project site for release notes.