TwoFiveThree.Repository.SqlBase 2.0.3

TwoFiveThree.Repository.SqlBase

Shared SQL infrastructure for the TwoFiveThree repository library. Provides RepositoryBase, SqlBuilder, and ExpressionTranslator — the building blocks for SQL-based providers such as TwoFiveThree.Repository.Psql and TwoFiveThree.Repository.MySql.

This package is intended for provider authors, not application consumers. If you are building an application, install a provider package directly instead:

dotnet add package TwoFiveThree.Repository.Psql
# or
dotnet add package TwoFiveThree.Repository.MySql

Packages

Requirements

• .NET Standard 2.0+ (.NET Framework 4.6.1, .NET Core, .NET 5+)
• TwoFiveThree.Repository
• Dapper 2.x

Installation

dotnet add package TwoFiveThree.Repository.SqlBase

Writing a SQL Provider

Reference this package and extend RepositoryBase. The only method you must implement is CreateConnection() — return the DbConnection for your database driver:

internal sealed class MyDatabaseRepository : RepositoryBase
{
    private readonly string _connectionString;

    public MyDatabaseRepository(string connectionString) =>
        _connectionString = connectionString;

    protected override DbConnection CreateConnection() =>
        new MyDatabaseConnection(_connectionString);
}

All query execution, SQL building, expression translation, audit methods, and navigation property mapping are inherited automatically.

Register via RepositoryOptions.UseProvider:

public static class MyDatabaseRepositoryExtensions
{
    public static RepositoryOptions UseMyDatabase(
        this RepositoryOptions options,
        string connectionString) =>
        options.UseProvider(_ => new MyDatabaseRepository(connectionString));
}

Key Components

RepositoryBase

Abstract base class implementing IRepository. Inherit this to build a SQL provider. Provides:

• DeleteAsync<T> — DELETE by primary key`
• ExecuteAsync — Execute arbitrary SQL with parameters`
• ExecuteScalarAsync<T> — Execute arbitrary SQL with parameters and return a single value`
• InsertAsync<T> — INSERT
• InsertWithAuditAsync<T, TAudit> — INSERT with audit record
• QueryAsync<T> — Execute arbitrary SQL with parameters and return mapped entities`
• QueryFirstOrDefaultAsync<T> — Execute arbitrary SQL with parameters and return a single mapped entity`
• SelectAllAsync<T> — SELECT with optional filter, ordering, and LEFT JOIN resolution for navigation properties
• SelectByIdAsync<T> — SELECT by primary key
• UpdateAsync<T> — UPDATE by primary key
• UpdateWithAuditAsync<T, TAudit> — UPDATE with diff-based audit record
• UpsertAsync<T> — UPSERT by primary key (INSERT or UPDATE)

Abstract members:

protected abstract DbConnection CreateConnection();
protected abstract string GetLastInsertedIdCommand(string autoGeneratedColumnName);
protected abstract string GetUpsertSql(string tableName, List<string> columns, List<string> paramValues, Dictionary<string, object>? primaryKeys, string returning);

SqlBuilder

Internal component that generates parameterised SQL statements from entity metadata. Handles:

• Column and table name resolution via [Table], [Column], and [PrimaryKey] attributes
• LEFT JOIN generation for navigation properties decorated with [ForeignKey] or following the {PropertyName}Id convention
• splitOn parameter generation for Dapper multi-map queries
• WHERE clause injection from translated expressions
• ORDER BY clause injection

ExpressionTranslator

Translates a LINQ Expression<Func<T, bool>> into a parameterised SQL WHERE clause. Supports:

Entity Configuration

Entities used with SQL providers must implement IEntity and be decorated with [Table] and [PrimaryKey]. Navigation properties are resolved automatically via the [ForeignKey] attribute or naming convention.

[Table("people")]
[PrimaryKey("id")]
public class Person : IEntity
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
    public string GenderId { get; set; } = default!;

    // Explicit foreign key
    [ForeignKey(nameof(GenderId))]
    public Gender? Gender { get; set; }
}

[Table("gender")]
[PrimaryKey("id")]
public class Gender : IEntity
{
    public string Id { get; set; } = default!;
    public string Description { get; set; } = default!;
}

Navigation properties without [ForeignKey] follow the convention {PropertyName}Id:

// Convention — no attribute needed, GenderId is resolved automatically
public Gender? Gender { get; set; }
public string GenderId { get; set; } = default!;

Dependencies