ODataToSql 1.0.9

ODataToSql

High-performance .NET library that converts OData V4 queries into optimized SQL statements for multiple database dialects.

Installation

dotnet add package ODataToSql

Or via NuGet Package Manager:

Install-Package ODataToSql

Quick Start

Basic Conversion (Direct API)

using ODataToSql;
using ODataToSql.Core;
using ODataToSql.Generation;

// Define your entity metadata
var metadata = new EntityMetadata(
    tableName: "Customers",
    columns: new[]
    {
        new ColumnMetadata("Id", typeof(int), isPrimaryKey: true),
        new ColumnMetadata("Name", typeof(string), isPrimaryKey: false),
        new ColumnMetadata("Email", typeof(string), isPrimaryKey: false),
        new ColumnMetadata("CreatedDate", typeof(DateTime), isPrimaryKey: false)
    }
);

// Create converter and convert OData query to SQL
var converter = new QueryConverter(SqlDialect.SqlServer);
var result = converter.Convert(
    odataQuery: "$filter=Name eq 'John' and CreatedDate gt 2024-01-01&$orderby=Name desc&$top=10",
    metadata: metadata
);

// Use the generated SQL
Console.WriteLine(result.SqlStatement);
// Output: SELECT TOP 10 * FROM Customers WHERE Name = @p0 AND CreatedDate > @p1 ORDER BY Name DESC

// Access parameters for safe execution
foreach (var param in result.Parameters)
{
    Console.WriteLine($"{param.Key} = {param.Value}");
}

Orchestrated Conversion (Recommended)

For production scenarios with validation, error handling, and workflow orchestration:

using ODataToSql;
using ODataToSql.Orchestration;

// Build conversion context
var context = new ConversionContext
{
    ODataQuery = "$filter=Name eq 'John'&$orderby=CreatedDate desc",
    EntityName = "Customers",
    Metadata = metadata,
    Dialect = "sqlserver"
};

// Execute orchestrated conversion
var result = await QueryConverterExtensions.ConvertAsync(context);

if (result.IsSuccess)
{
    var sqlResult = (SqlResult)result.Data;
    Console.WriteLine($"SQL: {sqlResult.SqlStatement}");
    
    // Execute against your database
    using var connection = new SqlConnection(connectionString);
    using var command = new SqlCommand(sqlResult.SqlStatement, connection);
    
    foreach (var param in sqlResult.Parameters)
    {
        command.Parameters.AddWithValue(param.Key, param.Value);
    }
    
    await connection.OpenAsync();
    using var reader = await command.ExecuteReaderAsync();
    // Process results...
}
else
{
    Console.WriteLine($"Conversion failed at {result.Error.Stage}: {result.Error.Message}");
}

Fluent Builder API

using ODataToSql;

var context = QueryConverterExtensions.CreateContextBuilder()
    .WithQuery("$filter=Price gt 100&$orderby=Name")
    .WithEntityName("Products")
    .WithMetadata(productMetadata)
    .WithDialect("postgresql")
    .WithPaging(top: 50, skip: 100)
    .Build();

var result = await QueryConverterExtensions.ConvertAsync(context);

Features

• OData V4 Query Support: Full support for $select, $filter, $orderby, $top, $skip, $expand, and $apply
• Multiple SQL Dialects: SQL Server, PostgreSQL, MySQL, SQLite, Oracle, and more
• Parameterized Queries: Automatic SQL injection protection through parameterization
• Multi-Level Expands: Handle complex nested entity relationships
• Aggregate Transformations: Support for $apply with groupby, aggregate, and filter operations
• Orchestrated Workflows: Built-in validation, error handling, and workflow coordination
• Type Safety: Strong typing with compile-time validation
• High Performance: Optimized parsing and SQL generation

Architecture

ODataToSql provides two usage patterns:

1. Direct API (Low-Level)

Use QueryConverter directly for simple, low-level conversions:

• Direct control over parsing and generation
• Minimal overhead
• Suitable for simple scenarios where you handle validation and errors yourself

2. Orchestration API (High-Level)

Use QueryConverterExtensions and orchestrators for production scenarios:

• Built-in request validation
• Comprehensive error handling
• Workflow coordination
• Logging support
• Suitable for production applications and complex scenarios

Usage Patterns

Pattern 1: Simple Conversion

For straightforward OData to SQL conversion:

var converter = new QueryConverter(SqlDialect.PostgreSql);
var result = converter.Convert("$filter=Active eq true", metadata);

Pattern 2: Orchestrated Conversion with Validation

For production scenarios with full validation and error handling:

var context = new ConversionContext
{
    ODataQuery = "$filter=Active eq true",
    EntityName = "Users",
    Metadata = userMetadata,
    Dialect = "postgresql"
};

var result = await QueryConverterExtensions.ConvertAsync(
    context,
    logger: msg => _logger.LogInformation(msg)
);

if (!result.IsSuccess)
{
    // Handle validation or conversion errors
    Console.WriteLine($"Error: {result.Error.Message}");
    if (result.Error.ValidationErrors != null)
    {
        foreach (var error in result.Error.ValidationErrors)
        {
            Console.WriteLine($"  - {error.Location}: {error.Message}");
        }
    }
}

Pattern 3: Dynamic Metadata with V2 Orchestration

For scenarios where metadata is fetched dynamically:

var context = new ConversionContext
{
    ODataQuery = "$filter=Status eq 'Active'",
    EntityName = "Orders"
};

var result = await QueryConverterExtensions.ConvertV2SqlAsync(
    context,
    metadataProvider: async entityName => 
    {
        // Fetch metadata from your repository
        return await _metadataRepository.GetAsync(entityName);
    },
    logger: msg => _logger.LogDebug(msg)
);

Pattern 4: Conversion with Data Execution

For end-to-end conversion and query execution:

var context = new ConversionContext
{
    ODataQuery = "$top=10&$skip=20",
    EntityName = "Products",
    Top = 10,
    Skip = 20
};

var result = await QueryConverterExtensions.ConvertV2Async(
    context,
    metadataProvider: async entityName => await _metadataRepo.GetAsync(entityName),
    dataExecutor: async sqlResult =>
    {
        // Execute SQL and return results
        using var connection = new NpgsqlConnection(_connectionString);
        using var command = new NpgsqlCommand(sqlResult.SqlStatement, connection);
        
        foreach (var param in sqlResult.Parameters)
            command.Parameters.AddWithValue(param.Key, param.Value);
        
        await connection.OpenAsync();
        using var reader = await command.ExecuteReaderAsync();
        
        var results = new List<Dictionary<string, object>>();
        while (await reader.ReadAsync())
        {
            var row = new Dictionary<string, object>();
            for (int i = 0; i < reader.FieldCount; i++)
                row[reader.GetName(i)] = reader.GetValue(i);
            results.Add(row);
        }
        return results;
    }
);

Supported OData Operations

Supported SQL Dialects

• SQL Server (T-SQL)
• PostgreSQL
• MySQL / MariaDB
• SQLite
• Oracle
• And more...

Migration Guide

Upgrading from Previous Versions

If you're using the direct QueryConverter API, no changes are required. The library maintains full backward compatibility.

To take advantage of new orchestration features:

Before (still works):

var converter = new QueryConverter(SqlDialect.PostgreSql);
var result = converter.Convert(odataQuery, metadata);

After (recommended for production):

var context = new ConversionContext
{
    ODataQuery = odataQuery,
    EntityName = "MyEntity",
    Metadata = metadata,
    Dialect = "postgresql"
};

var result = await QueryConverterExtensions.ConvertAsync(context);
if (result.IsSuccess)
{
    var sqlResult = (SqlResult)result.Data;
    // Use sqlResult.SqlStatement and sqlResult.Parameters
}

Benefits of Orchestration API

• Error Handling: Errors are returned as structured data instead of exceptions
• Validation: Built-in request validation before conversion
• Logging: Optional logging callbacks for diagnostics
• Flexibility: Support for dynamic metadata fetching and data execution
• Consistency: Uniform error format across all failure scenarios

Requirements

• .NET 10.0 or higher

Documentation

• Full Documentation: GitHub Repository
• API Reference: GitHub Wiki
• Examples: Sample Projects

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.