DigitaleDelta.ODataTranslator 1.0.1

OData to SQL Translator

A .NET library that converts OData query expressions to SQL, allowing applications to use an OData-subset syntax as used by DD API V3, while leveraging native SQL performance. A mapping system maps properties and functions to SQL queries, making it database-agnostic.

The library has no dependency on the Microsoft OData library or on the Microsoft SQL Geography libraries.

The code is based on an Antlr OData language definition, tailored to the needs for DD API V3 (see grammar/OData.g4).

It is a subset of the OData V4 specification, specifically designed for the Digitale Delta API

Antlr can generate lexers, parsers, and visitors out of the box for the following languages:

• Java
• C#
• Python 2 and 3
• JavaScript
• TypeScript
• Go
• C++
• Swift
• PHP
• Dart

Besides these languages, there are projects that provide extra runtimes that can convert the Antlr-generated code to other languages:

• Rust
• Kotlin
• Objective-C
• R
• Haxe
• Erlang

The parsers, lexers, and visitors can be used to build OData filter parsers and SQL translators. The translation to SQL is fully database-engine or database-design agnostic: the WHERE clause is generated by using mappings to properties and functions in simple JSON configuration files.

Table of Contents

1. What is this?
2. Key Features
3. Getting Started
4. Usage
5. Porting
6. Installation
7. Antlr Usage
8. Example Outputs

Contribution Guidelines

What is this?

This library bridges the gap between OData's flexible query language and SQL databases. Instead of implementing complex OData servers, relying on the Microsoft libraries, and working around its many opinions, you can:

• Accept standard OData filter syntax in your APIs
• Convert these expressions to SQL WHERE clauses
• Execute efficient database queries while providing a modern API interface

Key Features

• ✅ Uses Antlr to generate visitors and parsers
• ✅ SQL database technology/design agnostic
• ✅ Translates OData filter expressions to SQL WHERE clauses
• ✅ Configuration-based mapping
• ✅ Supports common OData functions and operators
• ✅ Validates input expressions for security
• ✅ Handles data type conversions
• ✅ Provides detailed error messages for invalid expressions

Getting Started

The base .g4 (Antlr language definition) is included, which is the basis of the DD API V3-subset of OData.

The code is already pre-generated but can be regenerated using Antlr in the generated directory, for instance for creating code to use in other programming languages.

The provided CSDL file contains the current DD API V3 definition for Digitale Delta.

The CSDL is processed by the DigitaleDelta.CsdlParser and returns a structure readable by C#.

That CSDL-parsed model provides the semantic information needed to translate the OData expression to SQL.

That model is used by ODataFilterValidator to validate the OData expression and by ODataToSqlTranslator to translate the OData expression to SQL.

Porting

Porting this library will be of moderate complexity. The first step will be using Antlr to generate lexers, parsers (and optionally visitors) for the grammar file in the desired programming language. The second step is to use the generated code to wrap that into more developer-friendly functions, possibly with the provided functions as guidelines.

Usage

Validating an OData Filter Expression

1. Read the CSDL file.
2. Read property and function mappings from JSON files.
3. Create an instance of ODataFilterValidator with the CSDL and maps.
4. Call the Validate method with the OData filter expression.

var context          = ODataParserHelper.ParseFilterQuery(oDataFilter);
var validator        = new ODataFilterValidator(modelResponse.Model, functionMaps);
var success = validator.TryValidateFilter(context, "observations", out var validationResult);

if (!success)
{
    Console.WriteLine($"Validation Error: {validationResult.ErrorMessage}");
    Exit(1);
}

success = converter.TryConvertFilterToSql(context);

if (success)
{
    Console.WriteLine($"SQL WHERE Clause: {converterResult.WhereClause}");
}
else
{
    Console.WriteLine($"Error: {converterResult.ErrorMessage}");
}

<hr>

Installation

Install the library via NuGet:

  dotnet add package DigitaleDelta.ODataTranslator

<hr></hr>

Antlr Usage

To regenerate the parser, lexer, and visitor code using Antlr: Install the Antlr tool if not already installed.

If Antlr cannot be installed locally, you can use the Docker image: https://github.com/antlr/antlr4/tree/master/docker

Antlr is only required to regenerate the parser and visitor code from the grammar file, which is already pre-generated in the generated directory.

Advise: install Adoptium OpenJDK 17 and use the jar file antlr-4.13.2-complete.jar supplied in this project. Note: visitor is not used in this library and not generated by default.

Run the following command in the grammar directory:

java -jar antlr-4.13.2-complete.jar -Dlanguage=CSharp grammar/OData.g4 -o generated

Function mappings example

[
  {
    "ODataFunctionName": "startswith",
    "ExpectedArgumentTypes": [ "EdmType.String", "EdmType.String" ],
    "ReturnType": "EdmType.Boolean",
    "SqlFunctionFormat": "ILIKE({0}, {1})",
    "WildCardPosition": "right",
    "WildCard": "%"
  },
  {
    "ODataFunctionName": "now",
    "ExpectedArgumentTypes": [  ],
    "ReturnType": "EdmType.DateTimeOffset",
    "SqlFunctionFormat": "NOW()"
  }
]

A note on SqlFunctionFormat: @srid is a placeholder for the SRID of the geometry, which is not used in this library. The SqlFunctionFormat is used to format the SQL function call with the provided arguments.

Property mappings example

 [
    {
      "ODataPropertyName": "PhenomenonTime/BeginPosition",
      "Query": "phenomenon_time_start",
      "EdmType": "Edm.DateTimeOffset"
    },
    {
      "ODataPropertyName": "PhenomenonTime/EndPosition",
      "Query": "phenomenon_time_end",
      "EdmType": "Edm.DateTimeOffset"
    },
    {
      "ODataPropertyName": "Parameter/Source",
      "Query": "observation.source",
      "EdmType": "Edm.String"
    }
  ]

This will generate the necessary C# files in the generated directory.<hr></hr> Example Outputs Input OData Filter

OData example request

  $filter=startswith(parameter/source, 'Wmr') and ResultOf eq 'aggregation' and (PhenomenonTime/BeginPosition ge now() and PhenomenonTime/BeginPosition le now())

Output SQL WHERE Clause

SQL-WHERE clause

  ILIKE(source, 'Wmr%') AND result = 'aggregation' AND (phenomenon_time_start >= NOW() AND phenomenon_time_start <= NOW())

Contribution Guidelines

We welcome contributions! To contribute:

1. Fork the repository.
2. Create a new branch for your feature or bug fix.
3. Write clear and concise commit messages.
4. Submit a pull request with a detailed description of your changes.

Please ensure your code adheres to the existing style and includes tests where applicable.

<hr> Code Formatting All code snippets in this document are properly formatted for clarity. Ensure your contributions follow the same formatting style.

Testing

This project uses XUnit for unit tests. The test suite covers core functionality, including OData parsing, SQL translation, and validation logic.

Running Tests

  dotnet test