FluentCommand.SqlServer 15.4.4

FluentCommand

Fluent wrapper for ADO.NET DbCommand with automatic object mapping, caching, query building, and source-generated data readers.

Package	Version
FluentCommand
FluentCommand.SqlServer
FluentCommand.Caching

Features

• Fluent wrapper over DbConnection and DbCommand
• Automatic connection state management
• Source-generated IDataReader mapping (no reflection)
• SQL query builder with Select, Insert, Update, Delete, and Upsert support
• JSON column support with [JsonColumn] attribute for source-generated readers
• JSON and CSV export directly from query results
• JSON parameter serialization with ParameterJson
• Parameterized queries with output, input-output, and return value callbacks
• Conditional parameters and query builder filters (ParameterIf, WhereIf, ValueIf)
• Result caching with sliding or absolute expiration
• Distributed cache integration via FluentCommand.Caching
• Query logging with elapsed time and parameter details
• Connection and command interceptors
• Multiple result set handling
• Multiple database configuration with discriminated registrations
• SQL Server bulk copy and merge data operations
• Tabular data import with field mapping, validation, and merge
• Multi-target: netstandard2.0, net8.0, net9.0, net10.0
• Supports SQL Server, PostgreSQL, and SQLite

Installation

dotnet add package FluentCommand

For SQL Server bulk copy, merge, and import features:

dotnet add package FluentCommand.SqlServer

For distributed caching:

dotnet add package FluentCommand.Caching

Quick Start

Configuration

Register with dependency injection for SQL Server:

services.AddFluentCommand(builder => builder
    .UseConnectionString(connectionString)
    .UseSqlServer()
);

Register using a connection name from appsettings.json:

services.AddFluentCommand(builder => builder
    .UseConnectionName("Tracker")
    .UseSqlServer()
);

{
  "ConnectionStrings": {
    "Tracker": "Data Source=(local);Initial Catalog=Tracker;Integrated Security=True;TrustServerCertificate=True;"
  }
}

For PostgreSQL:

services.AddFluentCommand(builder => builder
    .UseConnectionString(connectionString)
    .AddProviderFactory(NpgsqlFactory.Instance)
    .AddPostgreSqlGenerator()
);

For SQLite:

services.AddFluentCommand(builder => builder
    .UseConnectionName("Tracker")
    .AddProviderFactory(SqliteFactory.Instance)
    .AddSqliteGenerator()
);

Inject IDataSession where you need to run commands:

public sealed class UserRepository
{
    private readonly IDataSession _session;

    public UserRepository(IDataSession session)
    {
        _session = session;
    }

    public Task<User?> FindByEmailAsync(string email, CancellationToken cancellationToken = default)
    {
        return _session
            .Sql("select * from [User] where [EmailAddress] = @EmailAddress")
            .Parameter("@EmailAddress", email)
            .QuerySingleAsync<User>(cancellationToken: cancellationToken);
    }
}

Direct Configuration

Use DataConfiguration when not using dependency injection:

var configuration = new DataConfiguration(
    SqlClientFactory.Instance,
    connectionString,
    queryGenerator: new SqlServerGenerator()
);

await using var session = configuration.CreateSession();

Query Examples

Query Entities

var users = await session
    .Sql("select * from [User] where [EmailAddress] like @EmailAddress")
    .Parameter("@EmailAddress", "%@battlestar.com")
    .QueryAsync<User>();

Query a Single Row

var user = await session
    .Sql("select * from [User] where [EmailAddress] = @EmailAddress")
    .Parameter("@EmailAddress", "kara.thrace@battlestar.com")
    .QuerySingleAsync<User>();

Query Scalar Values

var count = await session
    .Sql("select count(*) from [User] where [IsDeleted] = @IsDeleted")
    .Parameter("@IsDeleted", false)
    .QueryValueAsync<int>();

Execute Commands

var affected = await session
    .Sql("update [User] set [LastLogin] = @LastLogin where [Id] = @Id")
    .Parameter("@Id", userId)
    .Parameter("@LastLogin", DateTimeOffset.UtcNow)
    .ExecuteAsync();

Multiple Result Sets

User? user = null;
List<Role> roles = [];
List<Priority> priorities = [];

await session
    .Sql("""
        select * from [User] where [EmailAddress] = @EmailAddress;
        select * from [Role];
        select * from [Priority];
        """)
    .Parameter("@EmailAddress", "kara.thrace@battlestar.com")
    .QueryMultipleAsync(async query =>
    {
        user = await query.QuerySingleAsync<User>();
        roles = (await query.QueryAsync<Role>()).ToList();
        priorities = (await query.QueryAsync<Priority>()).ToList();
    });

Stored Procedures with Output Parameters

long total = -1;

var users = session
    .StoredProcedure("[dbo].[UserListByEmailAddress]")
    .Parameter("@EmailAddress", "%@battlestar.com")
    .Parameter("@Offset", 0)
    .Parameter("@Size", 10)
    .ParameterOut<long>("@Total", value => total = value ?? -1)
    .Query<User>()
    .ToList();

JSON Export

var json = await session
    .Sql("select * from [Status] order by [DisplayOrder]")
    .QueryJsonAsync();

CSV Export

var csv = await session
    .Sql("select * from [Status] order by [DisplayOrder]")
    .QueryCsvAsync();

JSON Parameters

var metadata = new { Source = "Import", Count = 42 };

session
    .Sql("insert into [JsonLog] ([Data]) values (@Data)")
    .ParameterJson("@Data", metadata)
    .Execute();

SQL Query Builder

Build parameterized SQL statements using fluent expressions. The builder uses DataAnnotations schema attributes to extract table and column information.

Select

var users = await session
    .Sql(builder => builder
        .Select<User>()
        .Column(u => u.Id)
        .Column(u => u.DisplayName)
        .Column(u => u.EmailAddress)
        .Where(u => u.IsDeleted, false)
        .OrderBy(u => u.DisplayName)
        .Page(page: 1, pageSize: 25)
    )
    .QueryAsync<User>();

Conditional Filters

var users = await session
    .Sql(builder => builder
        .Select<User>()
        .WhereIf(
            u => u.EmailAddress,
            emailFilter,
            FilterOperators.Contains,
            (_, value) => !string.IsNullOrWhiteSpace(value))
        .WhereInIf(
            u => u.Id,
            selectedUserIds,
            (_, values) => values.Any())
    )
    .QueryAsync<User>();

Joins

var users = await session
    .Sql(builder => builder
        .Select<User>()
        .Column(u => u.DisplayName, "u")
        .Column(u => u.EmailAddress, "u")
        .Column<Role>(r => r.Name, "r", "RoleName")
        .From(tableAlias: "u")
        .Join<UserRole>(join => join
            .Left(u => u.Id, "u")
            .Right(ur => ur.UserId, "ur")
        )
        .Join<UserRole, Role>(join => join
            .Left(ur => ur.RoleId, "ur")
            .Right(r => r.Id, "r")
        )
        .Where(u => u.EmailAddress, "@battlestar.com", "u", FilterOperators.Contains)
        .OrderBy(u => u.DisplayName, "u")
    )
    .QueryAsync<User>();

Insert

var userId = await session
    .Sql(builder => builder
        .Insert<User>()
        .Value(u => u.Id, id)
        .Value(u => u.EmailAddress, $"{id}@email.com")
        .Value(u => u.DisplayName, "Last, First")
        .Output(u => u.Id)
    )
    .QueryValueAsync<Guid>();

Update

var updatedId = await session
    .Sql(builder => builder
        .Update<User>()
        .Value(u => u.DisplayName, "Updated Name")
        .Output(u => u.Id)
        .Where(u => u.Id, id)
    )
    .QueryValueAsync<Guid>();

Delete

var deletedId = await session
    .Sql(builder => builder
        .Delete<User>()
        .Output(u => u.Id)
        .Where(u => u.Id, id)
    )
    .QueryValueAsync<Guid>();

Upsert

await session
    .Sql(builder => builder
        .Upsert<StatusUpsert>()
        .Values(status)
        .Output(s => s.Id)
    )
    .QueryValueAsync<int>();

JSON Values in Query Builder

await session
    .Sql(builder => builder
        .Insert()
        .Into("JsonLog")
        .Value("Id", Guid.NewGuid())
        .ValueJson("Data", audit)
    )
    .ExecuteAsync();

Aggregates and Grouping

var total = await session
    .Sql(builder => builder
        .Select<Status>()
        .Aggregate(s => s.DisplayOrder, AggregateFunctions.Sum, columnAlias: "Total")
        .GroupBy(s => s.IsActive)
    )
    .QueryValueAsync<int>();

Raw Statements

var statuses = await session
    .Sql(builder =>
    {
        builder
            .Statement()
            .Query("CREATE TABLE #ids (Id int);");

        builder
            .Statement()
            .Query("INSERT INTO #ids (Id) SELECT CONVERT(int, value) FROM STRING_SPLIT(@Ids, @Sep);")
            .Parameter("@Ids", values)
            .Parameter("@Sep", ",");

        builder
            .Select<Status>()
            .From(tableAlias: "s")
            .Join(join => join
                .Left("Id", "s")
                .Right("Id", "#ids", null, "i"));
    })
    .QueryAsync<Status>();

Source Generator

FluentCommand includes a source generator that creates fast IDataReader mapping code for entity types, avoiding reflection at runtime. The generator runs when it finds [Table] on a class or [GenerateReader] pointing to a type.

[Table("Status", Schema = "dbo")]
public class Status
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int DisplayOrder { get; set; }
    public bool IsActive { get; set; }
    public DateTimeOffset Created { get; set; }
    public string CreatedBy { get; set; }
    public DateTimeOffset Updated { get; set; }
    public string UpdatedBy { get; set; }

    [ConcurrencyCheck]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [DataFieldConverter(typeof(ConcurrencyTokenHandler))]
    public ConcurrencyToken RowVersion { get; set; }

    [NotMapped]
    public ICollection<Task> Tasks { get; set; } = new List<Task>();
}

Generated extension methods are used automatically by QueryAsync<T> and QuerySingleAsync<T>:

var statuses = await session
    .Sql("select * from [dbo].[Status] order by [DisplayOrder]")
    .QueryAsync<Status>();

Generate for External Types

Use [GenerateReader] at the assembly level when you cannot modify the type:

[assembly: GenerateReader(typeof(ProductDto))]
[assembly: GenerateReader(typeof(CustomerDto))]

JSON Columns

Use [JsonColumn] for properties whose database column stores JSON text:

[Table("Import", Schema = "dbo")]
public class ImportRecord
{
    public int Id { get; set; }

    [JsonColumn]
    public ImportMetadata Metadata { get; set; }

    [JsonColumn(typeof(ImportJsonOptionsProvider))]
    public ImportMetadata MetadataWithOptions { get; set; }

    [JsonColumn(typeof(ImportJsonContext), nameof(ImportJsonContext.ImportMetadata))]
    public ImportMetadata MetadataWithContext { get; set; }
}

Records and Constructor Initialization

Records with primary constructors are supported:

[Table("Status", Schema = "dbo")]
public record StatusRecord(int Id, string Name, bool IsActive);

Caching

Opt-in caching per command with sliding or absolute expiration:

var statuses = await session
    .Sql(builder => builder
        .Select<Status>()
        .OrderBy(p => p.DisplayOrder)
    )
    .UseCache(TimeSpan.FromMinutes(5))
    .QueryAsync<Status>();

Distributed Caching

services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = redisConnectionString;
    options.InstanceName = "FluentCommand";
});

services.AddFluentCommand(builder => builder
    .UseConnectionString(connectionString)
    .UseSqlServer()
    .AddDistributedDataCache()
);

Logging

FluentCommand logs executed commands through IDataQueryLogger with command text, parameters, and elapsed time:

services.AddFluentCommand(builder => builder
    .UseConnectionString(connectionString)
    .UseSqlServer()
    .AddQueryLogger<DataQueryLogger>()
);

Executed DbCommand (12.3 ms) [CommandType='Text', CommandTimeout='30']
select * from [User] where [EmailAddress] = @EmailAddress
-- @EmailAddress: Input String(Size=0; Precision=0; Scale=0) [kara.thrace@battlestar.com]

Interceptors

Run code during connection open/close and before command execution:

services.AddFluentCommand(builder => builder
    .UseConnectionString(connectionString)
    .UseSqlServer()
    .AddInterceptor<CommandAuditInterceptor>()
    .AddInterceptor(sp => new SessionContextInterceptor(sp.GetRequiredService<IUserContext>()))
);

SQL Server Features

dotnet add package FluentCommand.SqlServer

Bulk Copy

await session
    .BulkCopy<User>()
    .Mapping<User>(map => map
        .Ignore(u => u.Id)
        .Ignore(u => u.RowVersion))
    .WriteToServerAsync(users);

Merge Data

var processed = await session
    .MergeData("dbo.User")
    .Map<UserImport>(map => map
        .AutoMap()
        .Column(u => u.EmailAddress).Key())
    .ExecuteAsync(users);

Data Import

Higher-level import workflow with field mapping, type conversion, defaults, validation, and merge:

services.AddFluentImport();

var definition = ImportDefinition.Build(builder => builder
    .Name("User")
    .TargetTable("dbo.User")
    .CanInsert()
    .CanUpdate()
    .MaxErrors(10)
    .Field(field => field
        .FieldName("EmailAddress")
        .DisplayName("Email Address")
        .DataType<string>()
        .IsKey()
        .Expression("^email$"))
    .Field(field => field
        .FieldName("FirstName")
        .DisplayName("First Name")
        .DataType<string>())
);

var processor = Services.GetRequiredService<IImportProcessor>();
var result = await processor.ImportAsync(definition, importData, username);

Multiple Database Configurations

Use discriminated registrations for multiple databases:

services.AddFluentCommand(builder => builder
    .UseConnectionString(primaryConnectionString)
    .UseSqlServer()
);

services.AddFluentCommand<ReadOnlyIntent>(builder => builder
    .UseConnectionString(readOnlyConnectionString)
    .UseSqlServer()
);

public sealed class ReportRepository
{
    private readonly IDataSession<ReadOnlyIntent> _session;

    public ReportRepository(IDataSession<ReadOnlyIntent> session)
    {
        _session = session;
    }
}

Documentation

Full documentation is available at the FluentCommand documentation site.