Ticomix.AI.ODataQuery

AI-powered natural language to OData query translation library for .NET applications.

Overview

Ticomix.AI.ODataQuery converts conversational natural language queries into secure, validated OData queries using Azure OpenAI. Built with expression tree validation and a fluent configuration API, it provides an intuitive query interface for business applications while maintaining security through configuration-based allowlists.

Example Query Flow:

User: "Show me active customers in Texas"
  ↓
AI Translation: Azure OpenAI structured outputs
  ↓
DSL Validation: Security allowlist checking
  ↓
Expression Trees: Type-safe LINQ compilation
  ↓
OData Query: $filter=IsActive eq true and State eq 'TX'
  ↓
Results: Customer[]

Key Features

• AI-Powered Translation: Azure OpenAI with structured JSON outputs (GPT-4o)
• Expression Tree Security: Zero SQL injection risk - queries built using LINQ Expression Trees
• Configuration-Based Allowlists: Fluent API for entity/field security configuration
• Multi-Turn Conversations: Context-aware query refinement with conversation history
• Azure Managed Identity: Secure authentication without API keys
• Comprehensive Testing: 98 unit tests with 100% pass rate, 35%+ coverage
• Production-Ready: Phases 1 & 2 complete (read queries, chat interface)

Installation

dotnet add package Ticomix.AI.ODataQuery --version 1.0.0-beta.1

Requirements:

• .NET 8.0+
• Azure OpenAI Service account
• Microsoft.AspNetCore.OData 8.2.5+
• Microsoft.EntityFrameworkCore 8.0.11+

Quick Start

1. Configure Azure OpenAI

Add to appsettings.json:

{
  "AzureOpenAI": {
    "Endpoint": "https://your-resource.openai.azure.com",
    "DeploymentName": "gpt-4",
    "UseManagedIdentity": true
  }
}

2. Register Services

In Startup.cs or Program.cs:

using Ticomix.AI.ODataQuery.Extensions;
using Ticomix.AI.ODataQuery.Models;

public void ConfigureServices(IServiceCollection services)
{
    // Configure Azure OpenAI
    services.Configure<AzureOpenAIOptions>(Configuration.GetSection("AzureOpenAI"));

    // Configure security allowlists
    services.Configure<QueryAllowlistOptions>(options =>
    {
        options.MaxTop = 500;
        options.MaxExpandDepth = 2;

        // Define allowed entities and fields
        options.AllowedEntities = new Dictionary<string, EntityAllowlist>
        {
            ["Customer"] = new EntityAllowlist
            {
                EntityName = "Customer",
                AllowedFields = new List<string>
                {
                    "Id", "Name", "Email", "Phone", "IsActive"
                },
                AllowedNavigations = new List<NavigationAllowlist>
                {
                    new NavigationAllowlist
                    {
                        NavigationName = "Orders",
                        TargetEntityName = "Order",
                        AllowedFields = new List<string> { "Id", "OrderDate", "Total" }
                    }
                }
            }
        };
    });

    // Register AI Query Builder services
    services.AddAIQueryBuilder();
}

3. Add Controller Endpoint

using Microsoft.AspNetCore.Mvc;
using Ticomix.AI.ODataQuery.Models;
using Ticomix.AI.ODataQuery.Services;

[ApiController]
[Route("api/[controller]")]
public class QueryController : ControllerBase
{
    private readonly ODataQueryOrchestrator _orchestrator;
    private readonly YourDbContext _context;

    public QueryController(
        ODataQueryOrchestrator orchestrator,
        YourDbContext context)
    {
        _orchestrator = orchestrator;
        _context = context;
    }

    [HttpPost("chat")]
    public async Task<ActionResult<ODataQueryResponse>> Chat(
        [FromBody] ODataQueryRequest request)
    {
        var response = await _orchestrator.ExecuteQueryAsync(
            request.UserQuery,
            request.ConversationId,
            request.ConversationHistory,
            _context
        );

        return Ok(response);
    }
}

4. Test Your Integration

curl -X POST https://localhost:5001/api/query/chat \
  -H "Content-Type: application/json" \
  -d '{
    "userQuery": "Show me all active customers",
    "conversationId": "00000000-0000-0000-0000-000000000001",
    "conversationHistory": []
  }'

Expected Response:

{
  "success": true,
  "results": [
    { "id": 1, "name": "Acme Corp", "email": "contact@acme.com", "isActive": true }
  ],
  "query": "$filter=IsActive eq true",
  "message": "Found 10 customers"
}

Security Model

Three-Layer Security Architecture

1. Row-Level Security: Business unit filtering via EF Core Global Query Filters
2. Column-Level Security: Field access based on security levels (Public, Standard, Restricted)
3. Query Validation: Pattern detection for malicious queries, complexity limits

Expression Tree Safety

// NO SQL INJECTION RISK - Uses strongly-typed Expression Trees
var predicate = Expression.Lambda<Func<Customer, bool>>(
    Expression.Equal(
        Expression.Property(customerParam, "IsActive"),
        Expression.Constant(true)
    ),
    customerParam
);

// Compiled to safe parameterized SQL by Entity Framework
queryable.Where(predicate);

Configuration-Based Allowlists

// Entity configuration with field-level security
options.AllowedEntities["Customer"] = new EntityAllowlist
{
    EntityName = "Customer",
    AllowedFields = new List<string>
    {
        "Id",           // Public
        "Name",         // Public
        "Email",        // Standard
        "Phone"         // Standard
        // SSN NOT in allowlist = BLOCKED
    },
    AllowedNavigations = new List<NavigationAllowlist>
    {
        new NavigationAllowlist
        {
            NavigationName = "Orders",
            TargetEntityName = "Order",
            AllowedFields = new List<string> { "Id", "Total" }
        }
    }
};

Supported Query Types

OData Operators Supported:

• Equals, NotEquals, Contains, StartsWith, EndsWith
• GreaterThan, LessThan, GreaterThanOrEqual, LessThanOrEqual
• And, Or, Not
• In (collection matching)
• IsNull

Advanced Configuration

Fluent Builder API

services.AddAIQueryBuilder(options =>
{
    options.Entity<Customer>("Customers")
        .AllowField(c => c.Id)
        .AllowField(c => c.Name)
        .AllowField(c => c.Email)
        .AllowNavigation(c => c.Orders, nav =>
        {
            nav.AllowField(o => o.Id)
               .AllowField(o => o.Total);
        });

    options.Entity<Order>("Orders")
        .AllowField(o => o.Id)
        .AllowField(o => o.OrderDate)
        .AllowField(o => o.Total);
});

Conversation Support

// Multi-turn conversation with context
var request = new ODataQueryRequest
{
    UserQuery = "Show me the same but only in Texas",  // References previous query
    ConversationId = existingConversationId,
    ConversationHistory = new List<ConversationMessage>
    {
        new ConversationMessage
        {
            Role = "user",
            Content = "Show me all customers"
        },
        new ConversationMessage
        {
            Role = "assistant",
            Content = "Here are all customers...",
            Query = "$filter=true"
        }
    }
};

Error Handling

try
{
    var response = await _orchestrator.ExecuteQueryAsync(
        userQuery, conversationId, history, _context
    );

    if (!response.Success)
    {
        // Handle validation or execution errors
        Console.WriteLine($"Query failed: {response.Error}");
    }
}
catch (SecurityException ex)
{
    // Unauthorized entity or field access attempt
    Console.WriteLine($"Security violation: {ex.Message}");
}
catch (InvalidOperationException ex)
{
    // Invalid query structure or malformed DSL
    Console.WriteLine($"Invalid query: {ex.Message}");
}

Performance

• Query Compilation: < 100ms typical
• AI Translation: 1-3 seconds (Azure OpenAI API call)
• Token Usage: ~500-1500 tokens per query (GPT-4o)
• Cost: Estimated $0.02-0.05 per query
• Unit Tests: All 98 tests complete in < 150ms

Audit Logging

All queries are automatically logged with:

• User ID, roles, timestamp
• Natural language prompt
• Generated DSL and OData query
• Token usage (input/output tokens)
• Execution time, result count
• Errors/warnings

Documentation

• Quick Start Guide: QUICKSTART.md
• Architecture Analysis: ANALYSIS_AI_QueryBuilder_Requirements_and_Architecture.md
• Test Coverage: TEST-COVERAGE.md
• Repository: Azure DevOps

Troubleshooting

Azure OpenAI Authentication Errors

Error: 401 Unauthorized or Azure OpenAI authentication failed

Solution:

1. Verify endpoint URL format (no trailing slash)
2. Check deployment name matches exactly (case-sensitive)
3. For Managed Identity: Ensure Azure CLI is logged in (az login)
4. For API Key: Verify key is correct in appsettings.json

Entity Not Found Errors

Error: Entity 'Customer' is not configured in allowlist

Solution: Add the entity to your QueryAllowlistOptions configuration.

CORS Issues

Solution: Configure CORS in Startup.cs:

services.AddCors(options =>
{
    options.AddPolicy("AllowAngularDev",
        builder => builder
            .WithOrigins("http://localhost:4200")
            .AllowAnyMethod()
            .AllowAnyHeader());
});

Version Information

• Package Version: 1.0.0-beta.1 (Pre-release)
• Status: Production-ready for Phase 1 & 2
• .NET Version: 8.0+
• Dependencies:
  • Microsoft.AspNetCore.OData 8.2.5+
  • Azure.AI.OpenAI 2.5.0-beta.1+
  • Microsoft.EntityFrameworkCore 8.0.11+

License

MIT License - Copyright (c) 2025 Ticomix. All rights reserved.

Support

• Issues: Azure DevOps Repository
• Documentation: See ai-docs/ folder in repository
• Version: 1.0.0-beta.1 (Pre-release)

Built for FoxPro-to-web modernization projects requiring intuitive database query interfaces.