ODataToSQLGenerator 1.0.22

ODataToSql

High-performance .NET library that converts OData V4 queries into optimized SQL statements for multiple database dialects including SQL Server, PostgreSQL, MySQL, Athena, Redshift, and Snowflake.

Installation

dotnet add package ODataToSql

Or via NuGet Package Manager:

Install-Package ODataToSql

Quick Start

EntityMetadata API

using ODataToSql;
using ODataToSql.Core;

// Define entity metadata directly
EntityMetadata entityMetadata = new("customer",
            [
                new("Id", typeof(int), false),
                new("Name", typeof(string), false),
                new("Age", typeof(int), false)
            ]);

// Convert using EntityMetadata
var result = ODataToSqlConverter.ConvertODataToSql(
    entityMetadata: entityMetadata,
    oDataQuery: "$select=Name,Age&$filter=Age gt 18&$orderby=Name asc",
    dialect: "SqlServer",
    useTableAliasName: true
);

SQL Generator Options

The library provides two SQL generation engines:

Default SQL Generator (Recommended)

var result = ODataToSqlConverter.ConvertODataToSql(
    metadata,
    "$select=Name,Age&$filter=Age gt 18",
    "SqlServer",
    useTableAliasName: true,
    sqlCompiler: "sql"  // Default - recommended for production
);

SqlKata Query Builder (Alternative)

Uses SqlKata for query building abstraction:

var result = ODataToSqlConverter.ConvertODataToSql(
    metadata,
    "$select=Name,Age&$filter=Age gt 18",
    "SqlServer",
    useTableAliasName: true,
    sqlCompiler: "sqlkata"  // Alternative implementation
);

When to use:

• Default SQL Generator: Production environments, complex queries, best performance (recommended)
• SqlKata Generator: Prototyping, simple queries, query builder abstraction preference

Features

• OData V4 Query Support: Full support for $select, $filter, $orderby, $top, $skip, $expand, $apply, and $compute
• Multiple SQL Dialects: SQL Server, PostgreSQL, MySQL, SQLite, ANSI SQL, Athena, Redshift, and Snowflake
• Parameterized Queries: Automatic SQL injection protection through parameterization with dialect-specific formats
• Multi-Level Expands: Handle complex nested entity relationships with unlimited depth
• Aggregate Transformations: Support for $apply with groupby, aggregate, filter, and compute operations
• Intelligent Filter Placement: Automatic WHERE vs HAVING clause detection with dialect-specific optimizations:
  • Athena: Expands aggregate and computed expressions inline in HAVING clause for full compatibility
  • Other Dialects: Uses cleaner column aliases in HAVING clause for better readability
• Computed Column References: Supports computed columns that reference other computed columns with recursive expansion
• Type Safety: Strong typing with compile-time validation
• High Performance: Optimized parsing and flat SQL generation with JOINs instead of subqueries
• Rich Function Library: Complete support for string, date/time, and math functions per OData V4 spec
• Date/Time Literals: ISO 8601 date, datetime, and datetimeoffset literals with timezone support
• Comprehensive Error Handling: Detailed validation errors with helpful messages

SQL Dialect Examples

The library supports 8 SQL dialects with dialect-specific parameter formatting and syntax:

SQL Server

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$select=Name,Age&$filter=Age gt 18&$top=10",
    "SqlServer",
    useTableAliasName: false
);

// Generated SQL:
// SELECT Name, Age FROM Customers 
// WHERE Age > @p0 
// ORDER BY Age 
// OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

// Parameters: { "@p0": 18 }

PostgreSQL

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$select=Name,Age&$filter=Age gt 18&$top=10",
    "PostgreSql",
    useTableAliasName: false
);

// Generated SQL:
// SELECT Name, Age FROM Customers 
// WHERE Age > $1 
// LIMIT 10

// Parameters: { "$1": 18 }

MySQL

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$select=Name,Age&$filter=Age gt 18&$top=10",
    "MySql",
    useTableAliasName: false
);

// Generated SQL:
// SELECT Name, Age FROM Customers 
// WHERE Age > ? 
// LIMIT 10

// Parameters: { "p0": 18 }

Amazon Athena

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$select=Name,Age&$filter=Age gt 18&$top=10&$skip=5",
    "Athena",
    useTableAliasName: false
);

// Generated SQL:
// SELECT Name, Age FROM Customers 
// WHERE Age > :p0 
// OFFSET 5 LIMIT 10

// Parameters: { ":p0": 18 }
// Note: Athena uses OFFSET...LIMIT order (opposite of standard SQL)
// Note: Athena expands aggregate expressions inline in HAVING clauses for compatibility

Amazon Redshift

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$select=Name,Age&$filter=Age gt 18&$top=10",
    "Redshift",
    useTableAliasName: false
);

// Generated SQL:
// SELECT Name, Age FROM Customers 
// WHERE Age > :p0 
// LIMIT 10

// Parameters: { ":p0": 18 }

Snowflake

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$select=Name,Age&$filter=Age gt 18&$top=10",
    "Snowflake",
    useTableAliasName: false
);

// Generated SQL:
// SELECT Name, Age FROM Customers 
// WHERE Age > :p0 
// LIMIT 10

// Parameters: { ":p0": 18 }

Parameter Format by Dialect

Notes:

• MySQL, SQLite, and ANSI SQL use ? placeholders with positional binding (dictionary keys: p0, p1, etc.)
• Athena, Redshift, and Snowflake use :p0, :p1 named parameters
• SQL Server uses @p0, @p1 named parameters
• PostgreSQL uses $1, $2 positional parameters (1-indexed)

Advanced Features

WHERE vs HAVING Clause (Intelligent Filter Placement)

The library automatically determines whether filters should go in the WHERE clause (for regular columns) or HAVING clause (for aggregate/computed columns), with dialect-specific optimizations:

Basic Aggregate Filter

OData Query:

$apply=groupby((Country), aggregate(People with sum as Total))&$filter=Total gt 100

Athena (expands expressions inline):

SELECT "Country", SUM("People") AS "Total"
FROM "Employees"
GROUP BY "Country"
HAVING SUM("People") > :p0

Redshift/Snowflake (uses column aliases):

SELECT "Country", SUM("People") AS "Total"
FROM "Employees"
GROUP BY "Country"
HAVING "Total" > :p0

SQL Server (uses column aliases):

SELECT [Country], SUM([People]) AS [Total]
FROM [Employees]
GROUP BY [Country]
HAVING [Total] > @p0

Computed Column Filter

OData Query:

$apply=groupby((Country), aggregate(People with sum as Total))/compute(Total add 1 as Total1)&$filter=Total1 gt 100

Athena:

SELECT "Country", SUM("People") AS "Total", (SUM("People") + :p0) AS "Total1"
FROM "Employees"
GROUP BY "Country"
HAVING ((SUM("People") + :p1) > :p2)

SQL Server:

SELECT [Country], SUM([People]) AS [Total], ([Total] + @p0) AS [Total1]
FROM [Employees]
GROUP BY [Country]
HAVING [Total1] > @p1

Mixed Filters (WHERE + HAVING)

OData Query:

$apply=groupby((Country, City), aggregate(Salary with sum as TotalSalary))&$filter=Country eq 'USA' and TotalSalary gt 100000

SQL Server:

SELECT [Country], [City], SUM([Salary]) AS [TotalSalary]
FROM [Employees]
WHERE [Country] = @p0
GROUP BY [Country], [City]
HAVING [TotalSalary] > @p1

Athena:

SELECT "Country", "City", SUM("Salary") AS "TotalSalary"
FROM "Employees"
WHERE "Country" = :p0
GROUP BY "Country", "City"
HAVING SUM("Salary") > :p1

Date/Time Functions

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$filter=year(BirthDate) eq 2000 and month(BirthDate) eq 1",
    "SqlServer",
    useTableAliasName: false
);

// Generated SQL:
// SELECT * FROM Customers 
// WHERE (YEAR(BirthDate) = @p0) AND (MONTH(BirthDate) = @p1)

// Parameters: { "@p0": 2000, "@p1": 1 }

String Functions

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$filter=contains(Name, 'John') and length(Email) gt 10",
    "PostgreSql",
    useTableAliasName: false
);

// Generated SQL:
// SELECT * FROM Customers 
// WHERE (Name LIKE '%' || $1 || '%') AND (LENGTH(Email) > $2)

// Parameters: { "$1": "John", "$2": 10 }

Math Functions

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$filter=round(Price) gt 100 and ceiling(Discount) lt 20",
    "MySql",
    useTableAliasName: false
);

// Generated SQL:
// SELECT * FROM Products 
// WHERE (ROUND(Price, 0) > ?) AND (CEILING(Discount) < ?)

// Parameters: { "p0": 100, "p1": 20 }

Computed Columns with References

The library supports computed columns that reference other computed columns:

var result = ODataToSqlConverter.ConvertODataToSql(
    datasets,
    "$compute=site add 1 as c1, c1 sub 20 as c2&$select=c1,c2",
    "Athena",
    useTableAliasName: true
);

// Generated SQL (Athena - expands inline):
// SELECT (t0."site" + :p0) AS "c1", 
//        ((t0."site" + :p0) - :p1) AS "c2"
// FROM "CAMPUS" AS t0

// Parameters: { ":p0": 1, ":p1": 20 }

**Key Features:**
- Computed columns can reference other computed columns
- Expressions are recursively expanded inline
- Consistent behavior across all SQL dialects

Error Handling

The library provides detailed error information for debugging:

try
{
    var result = ODataToSqlConverter.ConvertODataToSql(
        datasets,
        "$filter=NonExistentColumn eq 'test'",
        "SqlServer",
        useTableAliasName: false
    );
}
catch (ConversionFailedException ex)
{
    Console.WriteLine($"Conversion failed at stage: {ex.Stage}");
    Console.WriteLine($"Original query: {ex.ODataQuery}");
    
    if (ex.ValidationErrors != null && ex.ValidationErrors.Any())
    {
        Console.WriteLine("Validation errors:");
        foreach (var error in ex.ValidationErrors)
        {
            Console.WriteLine($"  - {error.Message} (Location: {error.Location})");
        }
    }
    
    if (ex.ErrorPosition.HasValue)
    {
        Console.WriteLine($"Error at position: {ex.ErrorPosition.Value}");
    }
}

// Output:
// Conversion failed at stage: Validation
// Original query: $filter=NonExistentColumn eq 'test'
// Validation errors:
//   - Column 'NonExistentColumn' does not exist in entity 'Products' (Location: $filter)

Supported OData Operations

Supported OData V4 Functions

String Functions

• contains, startswith, endswith, length, indexof, substring, tolower, toupper, trim, concat

Date/Time Functions

• year, month, day, hour, minute, second, date, time, now, mindatetime, maxdatetime, totaloffsetminutes, fractionalseconds

Math Functions

• round, floor, ceiling

Aggregate Functions

• sum, average, min, max, count

Type Functions

• cast

Supported SQL Dialects

• SQL Server - Full T-SQL support with OFFSET/FETCH pagination
• PostgreSQL - Complete PostgreSQL syntax with $1, $2 parameters
• MySQL - MySQL/MariaDB with ? placeholders
• SQLite - SQLite syntax with ? placeholders
• Amazon Athena - Presto/Trino-based with OFFSET...LIMIT order
• Amazon Redshift - Redshift-specific optimizations
• Snowflake - Snowflake syntax and functions
• ANSI SQL - Standard SQL for maximum compatibility

Requirements

• .NET 10.0 or higher

Documentation

• Full Documentation: GitHub Repository
• API Reference: GitHub Wiki

Support

• Issues: Report bugs or request features
• Discussions: Ask questions

License

This project is licensed under the MIT License.

Contributing

Contributions are welcome! Please see our Contributing Guide for details.