MyDevTime.SqlDataProvider.AspDotNetCore.gRPC 2.0.1

MyDevTime.SqlDataProvider

A config-driven SQL data provider framework that exposes arbitrary database queries through multiple ASP.NET Core protocols. Define SQL queries in any IConfiguration source (appsettings.json, environment variables, Azure Key Vault, etc.) and the framework automatically exposes them via GraphQL, OData, SignalR, gRPC, and MCP (Model Context Protocol) — with schema auto-discovery, typed endpoints, filtering, sorting, and projection.

Supports .NET 8, .NET 9, and .NET 10.

Table of Contents

• Packages
• Features
• Quick Start
• Protocol Usage
  • GraphQL
  • OData
  • SignalR
  • gRPC
  • MCP (Model Context Protocol)
• Authorization
• Multi-Database Setup
• Custom Connection Factory
• Build & Test
• License

Packages

Package	Version	Description
MyDevTime.SqlDataProvider		Core library — connection factories, SQL dialects, query builder, schema discovery
MyDevTime.SqlDataProvider.AspDotNetCore.GraphQl		HotChocolate GraphQL integration
MyDevTime.SqlDataProvider.AspDotNetCore.OData		OData REST integration
MyDevTime.SqlDataProvider.AspDotNetCore.SignalR		SignalR real-time integration
MyDevTime.SqlDataProvider.AspDotNetCore.gRPC		gRPC integration
MyDevTime.SqlDataProvider.AspDotNetCore.Mcp		MCP integration for AI assistants

Features

• Config-driven — define endpoints via IConfiguration (appsettings.json, environment variables, or any configuration provider), no code changes needed
• Schema auto-discovery — column names, types, and nullability discovered at startup via SchemaOnly execution
• Multiple protocols — GraphQL (HotChocolate), OData, SignalR, gRPC, MCP
• Multi-database — pluggable IDbConnectionFactory with built-in SQL Server support; add any ADO.NET provider (SQLite, PostgreSQL, MySQL, etc.)
• SQL dialect abstraction — ISqlDialect handles paging syntax and column quoting per database (ANSI, SQL Server, PostgreSQL, MySQL built-in)
• SQL injection prevention — ValidColumns whitelist enforced on all filter/sort/projection inputs
• IQueryable provider — all protocols use SqlDataProviderQueryable<T> with runtime-generated CLR types, translating LINQ expression trees to parameterized SQL
• Filtering — per-endpoint opt-in with column-level control; recursive AND/OR filter trees across all protocols
• Sorting — single and multi-column, ASC/DESC
• Projection — auto-projection from GraphQL selection set; explicit column selection for other protocols
• Paging — offset-based with skip/take and totalCount across all protocols (default: 50 rows)
• Hot-reload — OData, SignalR, gRPC, and MCP reload endpoint config on IConfiguration changes without restart (via ChangeToken.OnChange)
• AI-ready — MCP protocol exposes endpoints as tools for Claude, VS Code, and other AI assistants

Quick Start

1. Install the packages

dotnet add package MyDevTime.SqlDataProvider
dotnet add package MyDevTime.SqlDataProvider.AspDotNetCore.GraphQl   # and/or other protocols

2. Register the provider

// GraphQL
builder.Services
    .AddGraphQLServer()
    .AddQueryType(d => d.Name("Query").Field("_empty").Resolve(""))
    .AddGraphQlSqlDataProvider();

// OData
builder.Services.AddControllers().AddOData(opt => opt.Filter().OrderBy().Count().SetMaxTop(1000));
builder.Services.AddODataSqlDataProvider();
// After builder.Build():
app.MapODataSqlDataProvider();  // enables $metadata and service document

// SignalR
builder.Services.AddSignalRSqlDataProvider();

// gRPC
builder.Services.AddGrpcSqlDataProvider();

// MCP (Model Context Protocol)
builder.Services.AddMcpServer()
    .AddMcpSqlDataProvider();

3. Register a database connection factory

using MyDevTime.SqlDataProvider.Connection;

// SQL Server (built-in)
builder.Services.AddDbConnectionFactory("mssql", new SqlClientConnectionFactory());

// Custom providers
builder.Services.AddDbConnectionFactory("sqlite", new SqliteConnectionFactory());
builder.Services.AddDbConnectionFactory("postgres", new NpgsqlConnectionFactory());

ProviderName in the profile config selects which factory to use. Duplicate registrations throw at startup.

4. Configure endpoints via IConfiguration

The framework binds the SqlRetrievalProfiles section from IConfiguration. This can come from any configuration provider — appsettings.json, environment variables, Azure Key Vault, etc. The required configuration path is SqlRetrievalProfiles at the root level.

appsettings.json example:

{
  "SqlRetrievalProfiles": [
    {
      "ProfileName": "AdventureWorks",
      "ConnectionString": "Server=...;Database=AdventureWorks;...",
      "ProviderName": "mssql",
      "RetrievalEndpointProfiles": [
        {
          "EndPointName": "Customers",
          "SqlQuery": "SELECT CustomerID, FirstName, LastName, EmailAddress FROM Customer",
          "AllowFiltering": true,
          "AllowSorting": true,
          "AllowProjection": true
        },
        {
          "EndPointName": "Products",
          "SqlQuery": "SELECT ProductID, Name, ListPrice FROM Product WHERE ListPrice > 0",
          "AllowFiltering": true,
          "AllowSorting": true,
          "AllowProjection": false
        }
      ]
    }
  ]
}

Column lists for filtering/sorting/projection are auto-discovered from the query at startup. You can also pin them explicitly:

{
  "EndPointName": "Customers",
  "SqlQuery": "SELECT CustomerID, FirstName, LastName FROM Customer",
  "AllowFiltering": true,
  "FilterableColumns": ["LastName"],
  "AllowSorting": true,
  "AllowProjection": true
}

Explicit lists are never overwritten by discovery.

Protocol Usage

GraphQL

Endpoints are exposed as top-level query fields named {ProfileName}_{EndPointName}. Results are wrapped in a paged response with items and totalCount.

# Basic query — auto-projects from selection set
{
  AdventureWorks_Customers {
    items { customerID firstName lastName }
    totalCount
  }
}

# Filtering (HotChocolate-style where input)
{
  AdventureWorks_Customers(where: { lastName: { eq: "Miller" } }) {
    items { customerID firstName lastName }
    totalCount
  }
}

# Multiple conditions (AND)
{
  AdventureWorks_Customers(
    where: { lastName: { eq: "Miller" }, firstName: { startsWith: "J" } }
  ) {
    items { customerID firstName lastName }
  }
}

# OR conditions
{
  AdventureWorks_Customers(
    where: { or: [{ lastName: { eq: "Miller" } }, { lastName: { eq: "Smith" } }] }
  ) {
    items { customerID firstName lastName }
  }
}

# HC-style sorting
{
  AdventureWorks_Customers(order: [{ lastName: ASC }, { firstName: DESC }]) {
    items { lastName firstName }
  }
}

# Paging
{
  AdventureWorks_Customers(skip: 20, take: 10) {
    items { customerID firstName lastName }
    totalCount
  }
}

Filter field names are camelCased. Supported operators by type:

Note: GraphQL schema is built at startup. Adding or removing endpoints requires a restart.

OData

Uses Microsoft.AspNetCore.OData with [EnableQuery] — full OData query support via LINQ on our IQueryable provider.

GET /odata/$metadata                                          # EDM schema (CSDL XML)
GET /odata                                                    # Service document (entity set list)
GET /odata/AdventureWorks_Customers                           # All customers (default page size)
GET /odata/AdventureWorks_Customers?$filter=LastName eq 'Miller'
GET /odata/AdventureWorks_Customers?$orderby=LastName asc
GET /odata/AdventureWorks_Customers?$top=10&$skip=20
GET /odata/AdventureWorks_Customers?$count=true

OData query options ($filter, $orderby, $top, $skip, $count) are handled by Microsoft.AspNetCore.OData middleware — all standard OData filter operators are supported.

Logical combinators and / or and parenthesized grouping are supported (and binds tighter than or):

$filter=Color eq 'Red' and ListPrice gt 100
$filter=Color eq 'Red' or Color eq 'Blue'
$filter=(LastName eq 'Miller' or LastName eq 'Smith') and FirstName eq 'John'

SignalR

Hub URL: /sqlDataHub (configurable via MapSqlDataProviderHub("/custom-path"))

Connection

const connection = new signalR.HubConnectionBuilder()
    .withUrl("/sqlDataHub")
    .build();
await connection.start();

Hub Method: ExecuteQuery

ExecuteQuery(profileName, endpointName, filter?, sorting?, projection?, skip?, take?)
  -> { data, totalCount, success } | { error, success }

Filter format: A FilterNode is either a leaf or a combinator:

• Leaf: { column: "Name", operator: "=", value: "Miller" }
• AND: { and: [{ column: "A", ... }, { column: "B", ... }] }
• OR: { or: [{ column: "A", ... }, { column: "B", ... }] }
• Nested: { and: [{ column: "A", ... }, { or: [{ column: "B", ... }, { column: "C", ... }] }] }

Supported filter operators: =, !=, <>, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN

Examples

// Simple query (returns first 50 rows by default)
const result = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Customers",
    null, null, null, null, null
);

// Single filter
const filtered = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Customers",
    { column: "LastName", operator: "=", value: "Miller" },
    null, null, null, null
);

// OR filter
const redOrBlue = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Products",
    { or: [
        { column: "Color", operator: "=", value: "Red" },
        { column: "Color", operator: "=", value: "Blue" }
    ]},
    null, null, null, null
);

// With sorting and paging
const page2 = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Customers",
    null,
    [{ column: "LastName", ascending: true }],
    null,
    20,   // skip
    20    // take
);
// page2.totalCount = 847, page2.data contains rows 21-40

gRPC

Service Definition

service SqlDataProviderGrpc {
  rpc ExecuteQuery (QueryRequest) returns (QueryResponse);
  rpc GetCatalog (CatalogRequest) returns (CatalogResponse);
}

GetCatalog — Discover Available Endpoints

Returns all profiles, their endpoints, discovered column schemas, and allowed column lists.

var catalog = await client.GetCatalogAsync(new CatalogRequest());
foreach (var profile in catalog.Profiles)
{
    Console.WriteLine($"Profile: {profile.Name}");
    foreach (var endpoint in profile.Endpoints)
    {
        Console.WriteLine($"  Endpoint: {endpoint.Name}");
        foreach (var col in endpoint.Columns)
            Console.WriteLine($"    Column: {col.Name} ({col.ClrType}, nullable={col.Nullable})");
    }
}

ExecuteQuery — Run a Query

FilterNode is recursive — each node is either a leaf (column/operator/value) or a combinator (repeated FilterNode and / repeated FilterNode or).

Response contains success, error, json_data (JSON-serialized row array), and total_count.

Supported filter operators: =, !=, <>, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN

Examples

// Simple query
var response = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Customers"
});

// Single filter
var filtered = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Customers",
    Filter = new FilterNode { Column = "LastName", Operator = "=", Value = "Miller" }
});

// OR filter
var redOrBlue = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Products",
    Filter = new FilterNode { Or = {
        new FilterNode { Column = "Color", Operator = "=", Value = "Red" },
        new FilterNode { Column = "Color", Operator = "=", Value = "Blue" }
    }}
});

// With sorting and paging
var page2 = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Customers",
    Sorting = { new SortDescriptor { Column = "CustomerID", Ascending = true } },
    Skip = 20,
    Take = 20
});

MCP (Model Context Protocol)

Exposes SQL endpoints as tools for AI assistants (Claude Desktop, VS Code, etc.).

Setup

// In Program.cs
builder.Services.AddMcpServer()
    .AddMcpSqlDataProvider();
builder.Services.AddDbConnectionFactory("sqlite", new SqliteConnectionFactory());

var app = builder.Build();
app.MapMcpSqlDataProvider();  // HTTP transport
app.Run();

Configuration for AI Tools

Claude Desktop / Claude Code (HTTP):

{
  "mcpServers": {
    "sql-data-provider": {
      "type": "http",
      "url": "http://localhost:5000/mcp"
    }
  }
}

Claude Desktop (stdio, auto-starts):

{
  "mcpServers": {
    "sql-data-provider": {
      "command": "dotnet",
      "args": ["run", "--project", "/path/to/your/mcp-host-project"]
    }
  }
}

Available Tools

ListEndpoints — Discover all profiles, endpoints, and column schemas. Call this first.

DescribeEndpoint — Get detailed column info for a specific endpoint.

• profileName — Profile name (e.g. "AdventureWorks")
• endpointName — Endpoint name (e.g. "Customers")

ExecuteQuery — Run a query with optional filtering, sorting, projection, and paging.

• profileName — Profile name
• endpointName — Endpoint name
• filter — Recursive JSON filter object (see below)
• orderBy — JSON array: [{"column":"LastName","ascending":true}]
• select — JSON array: ["CustomerID","FirstName","LastName"]
• skip — Rows to skip (default: 0)
• take — Max rows (default: 50)

Filter format: A filter is either a leaf or a combinator, nestable:

• Leaf: {"column":"LastName","operator":"=","value":"Miller"}
• AND: {"and":[{"column":"A","operator":"=","value":"1"},{"column":"B","operator":">","value":100}]}
• OR: {"or":[{"column":"Color","operator":"=","value":"Red"},{"column":"Color","operator":"=","value":"Blue"}]}
• Nested: {"and":[{"column":"ListPrice","operator":">","value":100},{"or":[{"column":"Color","operator":"=","value":"Red"},{"column":"Color","operator":"=","value":"Blue"}]}]}

All errors include detailed messages with expected format and examples to help AI self-correct.

Authorization

Endpoints support authorization using ASP.NET Core's standard policy-based authorization. Each endpoint can optionally reference a named policy. The framework calls IAuthorizationService.AuthorizeAsync with the policy name and passes any configured parameters as the resource — consumers own the policy definitions and handlers entirely.

Configuration

{
  "EndPointName": "Customers",
  "SqlQuery": "SELECT CustomerID, FirstName, LastName FROM Customer",
  "AuthorizationPolicy": {
    "PolicyName": "HasPermission",
    "Parameters": { "Permission": "customers.read" }
  }
}

Simple example (built-in policy)

builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("RequireAdmin", policy => policy.RequireRole("Admin"));
});

No custom handler needed — just reference "RequireAdmin" as the PolicyName in config.

Advanced example (with parameters)

// Requirement (marker class)
public class PermissionRequirement : IAuthorizationRequirement { }

// Register policy
builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("HasPermission", policy =>
        policy.Requirements.Add(new PermissionRequirement()));
});
builder.Services.AddSingleton<IAuthorizationHandler, PermissionHandler>();

// Handler receives config parameters as the resource
public class PermissionHandler : AuthorizationHandler<PermissionRequirement, IConfigurationSection>
{
    protected override Task HandleRequirementAsync(
        AuthorizationHandlerContext context,
        PermissionRequirement requirement,
        IConfigurationSection parameters)
    {
        var permission = parameters["Permission"];
        if (context.User.HasClaim("permission", permission))
            context.Succeed(requirement);
        return Task.CompletedTask;
    }
}

Authorization is enforced across all 5 protocols (GraphQL, OData, SignalR, gRPC, MCP). Endpoints without AuthorizationPolicy are accessible without authorization.

Multi-Database Setup

Each profile specifies a ProviderName that maps to a registered IDbConnectionFactory. The factory's Dialect property controls SQL generation (paging syntax, column quoting):

{
  "SqlRetrievalProfiles": [
    {
      "ProfileName": "Sales",
      "ConnectionString": "Server=sales-server;...",
      "ProviderName": "mssql",
      "RetrievalEndpointProfiles": [...]
    },
    {
      "ProfileName": "Archive",
      "ConnectionString": "Host=archive-server;Database=archive;...",
      "ProviderName": "postgres",
      "RetrievalEndpointProfiles": [...]
    }
  ]
}

builder.Services.AddDbConnectionFactory("mssql", new SqlClientConnectionFactory());   // SqlServerDialect
builder.Services.AddDbConnectionFactory("postgres", new NpgsqlConnectionFactory());   // custom dialect

Custom Connection Factory

Implement IDbConnectionFactory for any ADO.NET provider:

using MyDevTime.SqlDataProvider.Connection;
using MyDevTime.SqlDataProvider.Dialect;

public class NpgsqlConnectionFactory : IDbConnectionFactory
{
    public ISqlDialect Dialect => PostgreSqlDialect.Instance;

    public DbConnection CreateConnection(string connectionString)
        => new NpgsqlConnection(connectionString);
}

Built-in dialects: AnsiSqlDialect (default), SqlServerDialect, PostgreSqlDialect, MySqlDialect.

Build & Test

dotnet build            # Build entire solution
dotnet test             # Run all 439 tests across 6 projects
dotnet pack             # Package all libraries as NuGet

Target frameworks: net8.0, net9.0, net10.0. Solution uses .slnx format.

License

Apache 2.0 — see LICENSE.