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.

Name		Name	Last commit message	Last commit date
Latest commit History 1,070 Commits
.github		.github
docs		docs
src		src
test		test
.editorconfig		.editorconfig
.gitattributes		.gitattributes
.gitignore		.gitignore
Directory.Packages.props		Directory.Packages.props
FluentCommand.slnx		FluentCommand.slnx
LICENSE		LICENSE
README.md		README.md
coverlet.runsettings		coverlet.runsettings
dictionary.dic		dictionary.dic
global.json		global.json
logo.png		logo.png

Uh oh!

Folders and files

Latest commit

History

Repository files navigation

FluentCommand

Features

Installation

Quick Start

Configuration

Direct Configuration

Query Examples

Query Entities

Query a Single Row

Query Scalar Values

Execute Commands

Multiple Result Sets

Stored Procedures with Output Parameters

JSON Export

CSV Export

JSON Parameters

SQL Query Builder

Select

Conditional Filters

Joins

Insert

Update

Delete

Upsert

JSON Values in Query Builder

Aggregates and Grouping

Raw Statements

Source Generator

Generate for External Types

JSON Columns

Records and Constructor Initialization

Caching

Distributed Caching

Logging

Interceptors

SQL Server Features

Bulk Copy

Merge Data

Data Import

Multiple Database Configurations

Documentation

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 61

Sponsor this project

Uh oh!

Packages 0

Uh oh!

Uh oh!

Contributors

Uh oh!

Languages

Packages