### Execute Raw SQL Query with Parameters (C#)

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Shows how to execute a raw SQL query with parameters using FluentCommand and map the results to a custom entity type using manual DataReader mapping. This example uses a SQL Server connection.

```csharp
using FluentCommand;

await using var session = services.GetRequiredService<IDataSession>();

string email = "kara.thrace@battlestar.com";
string sql = "select * from [User] where EmailAddress = @EmailAddress";

var user = await session
    .Sql(sql)
    .Parameter("@EmailAddress", email)
    .QuerySingleAsync(r => new User
    {
        Id = r.GetGuid("Id"),
        EmailAddress = r.GetString("EmailAddress"),
        IsEmailAddressConfirmed = r.GetBoolean("IsEmailAddressConfirmed"),
        DisplayName = r.GetString("DisplayName"),
        PasswordHash = r.GetString("PasswordHash"),
        AccessFailedCount = r.GetInt32("AccessFailedCount"),
        LockoutEnabled = r.GetBoolean("LockoutEnabled"),
        LockoutEnd = r.GetDateTimeOffsetNull("LockoutEnd"),
        LastLogin = r.GetDateTimeOffsetNull("LastLogin"),
        IsDeleted = r.GetBoolean("IsDeleted"),
        Created = r.GetDateTimeOffset("Created"),
        CreatedBy = r.GetString("CreatedBy"),
        Updated = r.GetDateTimeOffset("Updated"),
        UpdatedBy = r.GetString("UpdatedBy")
    });

// user.EmailAddress == "kara.thrace@battlestar.com"
```

--------------------------------

### Executing Aggregate Functions

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Provides examples for performing COUNT operations and aggregate functions like SUM combined with GROUP BY clauses.

```csharp
using FluentCommand;
using FluentCommand.Query;

await using var session = services.GetRequiredService<IDataSession>();

var count = await session
    .Sql(builder => builder
        .Select<User>()
        .Count()
        .Where(p => p.EmailAddress, "@battlestar.com", FilterOperators.Contains)
    )
    .QueryValueAsync<int>();

var sumResult = await session
    .Sql(builder => builder
        .Select<Status>()
        .Aggregate(p => p.DisplayOrder, AggregateFunctions.Sum)
        .GroupBy(p => p.IsActive)
    )
    .QueryValueAsync<int>();
```

--------------------------------

### Stored Procedures

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Examples of executing stored procedures with various parameter types and return values.

```APIDOC
## Execute Stored Procedure with Output Parameters

### Description
Call a stored procedure with input parameters and capture output and return values.

### Method
POST

### Endpoint
Not applicable (direct stored procedure execution)

### Parameters
- **@EmailAddress** (string) - Input parameter for filtering by email address.
- **@Offset** (int) - Input parameter for pagination offset.
- **@Size** (int) - Input parameter for pagination size.
- **@Total** (long) - Output parameter to capture the total count.

### Request Example
```csharp
using FluentCommand;
using System.Data;

await using var session = services.GetRequiredService<IDataSession>();

long total = -1;
var email = "%@battlestar.com";

var users = session
    .StoredProcedure("[dbo].[UserListByEmailAddress]")
    .Parameter("@EmailAddress", email)
    .Parameter("@Offset", 0)
    .Parameter("@Size", 10)
    .Parameter<long>(parameter => parameter
        .Name("@Total")
        .Type(DbType.Int64)
        .Output(v => total = v)
        .Direction(ParameterDirection.Output)
    )
    .Query<User>()
    .ToList();

// users contains up to 10 User entities
// total contains the total count from the output parameter
Console.WriteLine($"Showing {users.Count} of {total} users");
```

### Response
#### Success Response (200)
- **users** (List<User>) - A list of User entities returned by the stored procedure.
- **total** (long) - The value from the output parameter `@Total`.

#### Response Example
```json
{
  "users": [
    {
      "UserId": "guid-1",
      "DisplayName": "Adama",
      "EmailAddress": "william.adama@battlestar.com"
    }
  ],
  "total": 50
}
```

## Execute Stored Procedure with Return Value

### Description
Execute a stored procedure and capture its return value using a callback.

### Method
POST

### Endpoint
Not applicable (direct stored procedure execution)

### Parameters
- **@EmailAddress** (string) - Input parameter for filtering by email address.

### Request Example
```csharp
using FluentCommand;

await using var session = services.GetRequiredService<IDataSession>();

int result = -1;
long total = -1;

var email = "william.adama@battlestar.com";

result = session
    .StoredProcedure("[dbo].[UserCountByEmailAddress]")
    .Parameter("@EmailAddress", email)
    .Return<long>(p => total = p)
    .Execute();

// result contains the number of rows affected
// total contains the return value from the stored procedure
```

### Response
#### Success Response (200)
- **result** (int) - The number of rows affected by the stored procedure execution.
- **total** (long) - The return value from the stored procedure.

#### Response Example
```json
{
  "result": 1,
  "total": 1
}
```

## Execute Upsert Stored Procedure

### Description
Call a stored procedure that performs an upsert operation and returns the resulting entity.

### Method
POST

### Endpoint
Not applicable (direct stored procedure execution)

### Parameters
- **@Id** (Guid) - Input parameter for the user ID.
- **@EmailAddress** (string) - Input parameter for the user's email address.
- **@IsEmailAddressConfirmed** (bool) - Input parameter indicating if the email is confirmed.
- **@DisplayName** (string) - Input parameter for the user's display name.
- **@PasswordHash** (string) - Input parameter for the user's password hash.
- **@ResetHash** (string) - Input parameter for the password reset hash (nullable).
- **@InviteHash** (string) - Input parameter for the invite hash (nullable).
- **@AccessFailedCount** (int) - Input parameter for the access failed count.
- **@LockoutEnabled** (bool) - Input parameter indicating if lockout is enabled.
- **@IsDeleted** (bool) - Input parameter indicating if the user is deleted.

### Request Example
```csharp
using FluentCommand;

await using var session = services.GetRequiredService<IDataSession>();

int errorCode = -1;
var userId = Guid.NewGuid();
var username = "test." + DateTime.Now.Ticks;
var email = username + "@email.com";

var user = session
    .StoredProcedure("[dbo].[UserUpsert]")
    .Parameter("@Id", userId)
    .Parameter("@EmailAddress", email)
    .Parameter("@IsEmailAddressConfirmed", true)
    .Parameter("@DisplayName", "Unit Test")
    .Parameter("@PasswordHash", "T@est" + DateTime.Now.Ticks)
    .Parameter<string>("@ResetHash", null)
    .Parameter<string>("@InviteHash", null)
    .Parameter("@AccessFailedCount", 0)
    .Parameter("@LockoutEnabled", false)
    .Parameter("@IsDeleted", false)
    .Return<int>(p => errorCode = p)
    .QuerySingle<User>();

// user contains the inserted or updated User entity
// errorCode contains the stored procedure return value
```

### Response
#### Success Response (200)
- **user** (User) - The User entity that was inserted or updated.
- **errorCode** (int) - The return value from the stored procedure.

#### Response Example
```json
{
  "user": {
    "UserId": "generated-guid",
    "DisplayName": "Unit Test",
    "EmailAddress": "test.1234567890@email.com"
  },
  "errorCode": 0
}
```
```

--------------------------------

### Map Class to Database Table using TableAttribute

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/generation.md

Decorate a class with the TableAttribute to enable the source generator to create extension methods for database operations. This example demonstrates mapping a class to a specific schema and handling concurrency tokens.

```csharp
[Table("Status", Schema = "dbo")]
public class Status
{
    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 virtual ICollection<Task> Tasks { get; set; } = new List<Task>();
}
```

--------------------------------

### Configure Data for SQL Server with FluentCommand

Source: https://github.com/loresoft/fluentcommand/blob/master/README.md

Demonstrates how to configure FluentCommand for SQL Server using a connection string. This involves creating an IDataConfiguration instance with the appropriate factory and connection string.

```csharp
IDataConfiguration dataConfiguration  = new DataConfiguration(
    SqlClientFactory.Instance, 
    ConnectionString
);
```

--------------------------------

### Configure FluentCommand for SQL Server (Direct)

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Demonstrates direct configuration of FluentCommand using a DbProviderFactory and connection string for SQL Server. This method is suitable for scenarios where dependency injection is not used.

```csharp
using FluentCommand;
using Microsoft.Data.SqlClient;

// Direct configuration
IDataConfiguration dataConfiguration = new DataConfiguration(
    SqlClientFactory.Instance,
    "Data Source=(local);Initial Catalog=TrackerTest;Integrated Security=True;TrustServerCertificate=True;"
);

// Create a session to execute queries
using var session = dataConfiguration.CreateSession();
```

--------------------------------

### Select Count and Aggregates

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/sql.md

Demonstrates how to perform count operations and aggregate functions like SUM. It shows how to use GroupBy in conjunction with aggregation.

```csharp
await using var session = Services.GetRequiredService<IDataSession>();
var count = await session
    .Sql(builder => builder
        .Select<Status>()
        .Aggregate(p => p.DisplayOrder, AggregateFunctions.Sum)
        .GroupBy(p => p.IsActive)
    )
    .QueryValueAsync<int>();
```

--------------------------------

### Execute Command and Get Affected Rows (C#)

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/query.md

Executes a stored procedure or SQL command against a database connection and returns the number of rows affected. It can also capture an output parameter value.

```csharp
int result = -1;
long total = -1;

var email = "william.adama@battlestar.com";

using var session = Services.GetRequiredService<IDataSession>();

result = session
    .StoredProcedure("[dbo].[UserCountByEmailAddress]")
    .Parameter("@EmailAddress", email)
    .Return<long>(p => total = p)
    .Execute();
```

--------------------------------

### FluentCommand Configuration

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/configuration.md

Methods for initializing DataConfiguration and registering FluentCommand services via dependency injection.

```APIDOC
## Configuration for SQL Server

### Description
Initialize a DataConfiguration instance using the SqlClientFactory.

### Request Example
```csharp
var dataConfiguration = new DataConfiguration(SqlClientFactory.Instance, ConnectionString);
```

## Configure Data Logger

### Description
Attach a logger to the DataConfiguration to monitor executed queries.

### Request Example
```csharp
var dataLogger = new DataQueryLogger(Output.WriteLine);
var dataConfiguration = new DataConfiguration(SqlClientFactory.Instance, ConnectionString, queryLogger: dataLogger);
```

## Register with Dependency Injection

### Description
Register FluentCommand services in the IServiceCollection using the fluent builder pattern.

### Request Example
```csharp
services.AddFluentCommand(builder => builder
    .UseConnectionString(ConnectionString)
    .UseSqlServer()
);
```

## Register for PostgreSQL

### Description
Configure FluentCommand to work with PostgreSQL using NpgsqlFactory and the PostgreSqlGenerator.

### Request Example
```csharp
services.AddFluentCommand(builder => builder
    .UseConnectionName("Tracker")
    .AddProviderFactory(NpgsqlFactory.Instance)
    .AddPostgreSqlGenerator()
);
```
```

--------------------------------

### C# Status Member Accessors for FluentCommand

Source: https://github.com/loresoft/fluentcommand/blob/master/test/FluentCommand.Generators.Tests/Snapshots/TypeAccessorWriterTests.GenerateObjectInitializer.verified.txt

These classes implement the IMemberAccessor interface to provide reflection capabilities for the Status entity's properties. They handle getting and setting values for IsActive (bool), Updated (DateTimeOffset), and RowVersion (byte[]).

```csharp
private sealed class IsActiveMemberAccessor : global::FluentCommand.Reflection.IMemberAccessor
        {
            public global::System.Type MemberType => typeof(System.Boolean);
            public string Name => "IsActive";
            public string Column => "IsActive";
            public string? ColumnType => null;
            public int? ColumnOrder => null;
            public bool IsKey => false;
            public bool IsNotMapped => false;
            public bool IsDatabaseGenerated => false;
            public bool IsConcurrencyCheck => false;
            public string? ForeignKey => null;
            public bool HasGetter => true;
            public bool HasSetter => true;
            public bool IsRequired => false;
            public string DisplayName => "IsActive";
            public string? DataFormatString => null;

            public object? GetValue(object instance)
                => ((global::FluentCommand.Entities.Status)instance).IsActive;

            public void SetValue(object instance, object? value)
                => ((global::FluentCommand.Entities.Status)instance).IsActive = (System.Boolean)value!;
        }

        private sealed class UpdatedMemberAccessor : global::FluentCommand.Reflection.IMemberAccessor
        {
            public global::System.Type MemberType => typeof(System.DateTimeOffset);
            public string Name => "Updated";
            public string Column => "Updated";
            public string? ColumnType => null;
            public int? ColumnOrder => null;
            public bool IsKey => false;
            public bool IsNotMapped => false;
            public bool IsDatabaseGenerated => false;
            public bool IsConcurrencyCheck => true;
            public string? ForeignKey => null;
            public bool HasGetter => true;
            public bool HasSetter => true;
            public bool IsRequired => false;
            public string DisplayName => "Updated";
            public string? DataFormatString => null;

            public object? GetValue(object instance)
                => ((global::FluentCommand.Entities.Status)instance).Updated;

            public void SetValue(object instance, object? value)
                => ((global::FluentCommand.Entities.Status)instance).Updated = (System.DateTimeOffset)value!;
        }

        private sealed class RowVersionMemberAccessor : global::FluentCommand.Reflection.IMemberAccessor
        {
            public global::System.Type MemberType => typeof(System.Byte[]);
            public string Name => "RowVersion";
            public string Column => "RowVersion";
            public string? ColumnType => null;
            public int? ColumnOrder => null;
            public bool IsKey => false;
            public bool IsNotMapped => false;
            public bool IsDatabaseGenerated => false;
            public bool IsConcurrencyCheck => false;
            public string? ForeignKey => null;
            public bool HasGetter => true;
            public bool HasSetter => true;
            public bool IsRequired => false;
            public string DisplayName => "RowVersion";
            public string? DataFormatString => null;

            public object? GetValue(object instance)
                => ((global::FluentCommand.Entities.Status)instance).RowVersion;

            public void SetValue(object instance, object? value)
                => ((global::FluentCommand.Entities.Status)instance).RowVersion = (System.Byte[])value!;
        }
```

--------------------------------

### Querying Data with Raw SQL

Source: https://github.com/loresoft/fluentcommand/blob/master/README.md

Demonstrates how to query data using raw SQL statements, including parameterization and entity mapping.

```APIDOC
## Query All Users with Email Domain

### Description
Queries all users whose email addresses match a specified domain pattern. The entity is automatically created from the DataReader.

### Method
GET (Implicit)

### Endpoint
N/A (SQL Query)

### Parameters
#### Query Parameters
- **@EmailAddress** (string) - Required - The email address pattern to filter by.

### Request Example
```csharp
string email = "%@battlestar.com";
string sql = "select * from [User] where EmailAddress like @EmailAddress";

var session = configuration.CreateSession();
var user = await session
    .Sql(sql)
    .Parameter("@EmailAddress", email)
    .QuerySingleAsync(r => new User
    {
        Id = r.GetGuid("Id"),
        EmailAddress = r.GetString("EmailAddress"),
        IsEmailAddressConfirmed = r.GetBoolean("IsEmailAddressConfirmed"),
        DisplayName = r.GetString("DisplayName"),
        PasswordHash = r.GetString("PasswordHash"),
        ResetHash = r.GetString("ResetHash"),
        InviteHash = r.GetString("InviteHash"),
        AccessFailedCount = r.GetInt32("AccessFailedCount"),
        LockoutEnabled = r.GetBoolean("LockoutEnabled"),
        LockoutEnd = r.GetDateTimeOffsetNull("LockoutEnd"),
        LastLogin = r.GetDateTimeOffsetNull("LastLogin"),
        IsDeleted = r.GetBoolean("IsDeleted"),
        Created = r.GetDateTimeOffset("Created"),
        CreatedBy = r.GetString("CreatedBy"),
        Updated = r.GetDateTimeOffset("Updated"),
        UpdatedBy = r.GetString("UpdatedBy"),
        RowVersion = r.GetBytes("RowVersion"),
    });
```

### Response
#### Success Response (200)
- **user** (User) - The User object matching the query.

#### Response Example
```json
{
  "Id": "guid-value",
  "EmailAddress": "user@battlestar.com",
  "IsEmailAddressConfirmed": true,
  "DisplayName": "User Name",
  "PasswordHash": "...",
  "ResetHash": null,
  "InviteHash": null,
  "AccessFailedCount": 0,
  "LockoutEnabled": false,
  "LockoutEnd": null,
  "LastLogin": "2023-10-27T10:00:00Z",
  "IsDeleted": false,
  "Created": "2023-10-26T09:00:00Z",
  "CreatedBy": "System",
  "Updated": "2023-10-27T09:30:00Z",
  "UpdatedBy": "Admin",
  "RowVersion": "byte-array"
}
```
```

```APIDOC
## Query User by Email with Related Entities

### Description
Queries a user by their email address and also retrieves related 'Status' and 'Priority' entities in separate result sets.

### Method
GET (Implicit)

### Endpoint
N/A (SQL Query)

### Parameters
#### Query Parameters
- **@EmailAddress** (string) - Required - The email address of the user to query.

### Request Example
```csharp
string email = "kara.thrace@battlestar.com";
string sql = "select * from [User] where EmailAddress = @EmailAddress; " +
             "select * from [Status]; " +
             "select * from [Priority]; ";

User user = null;
List<Status> status = null;
List<Priority> priorities = null;

var session = configuration.CreateSession();
session
    .Sql(sql)
    .Parameter("@EmailAddress", email)
    .QueryMultiple(q =>
    {
        user = q.QuerySingle<User>();
        status = q.Query<Status>().ToList();
        priorities = q.Query<Priority>().ToList();
    });
```

### Response
#### Success Response (200)
- **user** (User) - The User object matching the email.
- **status** (List<Status>) - A list of all Status entities.
- **priorities** (List<Priority>) - A list of all Priority entities.

#### Response Example
```json
{
  "user": {
    "Id": "guid-value",
    "EmailAddress": "kara.thrace@battlestar.com",
    "DisplayName": "Kara Thrace"
    // ... other user properties
  },
  "status": [
    { "Id": 1, "Name": "Active" },
    { "Id": 2, "Name": "Inactive" }
  ],
  "priorities": [
    { "Id": 1, "Name": "High" },
    { "Id": 2, "Name": "Medium" }
  ]
}
```
```

--------------------------------

### Configure FluentCommand for PostgreSQL

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Illustrates configuring FluentCommand for PostgreSQL databases by adding the Npgsql provider factory and the PostgreSQL generator.

```csharp
using FluentCommand;
using Npgsql;

services.AddFluentCommand(builder => builder
    .UseConnectionName("Tracker")
    .AddProviderFactory(NpgsqlFactory.Instance)
    .AddPostgreSqlGenerator()
);
```

--------------------------------

### SQL Query for User Table Changes

Source: https://github.com/loresoft/fluentcommand/blob/master/test/FluentCommand.Tests/Query/Snapshots/SelectBuilderTest.SelectEntityChangeTableBuilder.verified.txt

This SQL query retrieves the latest changes from the User table using CHANGETABLE. It joins the changes with the User table to get the full details of modified records. This is useful for implementing auditing or synchronization features.

```sql
SELECT [t].[Id], [t].[EmailAddress], [t].[IsEmailAddressConfirmed], [t].[DisplayName], [t].[FirstName], [t].[LastName], [t].[PasswordHash], [t].[ResetHash], [t].[InviteHash], [t].[AccessFailedCount], [t].[LockoutEnabled], [t].[LockoutEnd], [t].[LastLogin], [t].[IsDeleted], [t].[Created], [t].[CreatedBy], [t].[Updated], [t].[UpdatedBy], [t].[RowVersion]
FROM CHANGETABLE (CHANGES [User], @p0000) AS [c]
INNER JOIN [User] AS [t] ON [c].[Id] = [t].[Id];
```

--------------------------------

### Execute Upsert Stored Procedure (C#)

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/parameter.md

Illustrates executing an upsert stored procedure '[dbo].[UserUpsert]' to insert or update a user record. It sets various parameters including GUID, string, boolean, and integer types, and captures the return value into 'errorCode'.

```csharp
int errorCode = -1;

var userId = Guid.NewGuid();
var username = "test." + DateTime.Now.Ticks;
var email = username + "@email.com";

using var session = Services.GetRequiredService<IDataSession>();

var user = session
    .StoredProcedure("[dbo].[UserUpsert]")
    .Parameter("@Id", userId)
    .Parameter("@EmailAddress", email)
    .Parameter("@IsEmailAddressConfirmed", true)
    .Parameter("@DisplayName", "Unit Test")
    .Parameter("@PasswordHash", "T@est" + DateTime.Now.Ticks)
    .Parameter<string>("@ResetHash", null)
    .Parameter<string>("@InviteHash", null)
    .Parameter("@AccessFailedCount", 0)
    .Parameter("@LockoutEnabled", false)
    .Parameter("@IsDeleted", false)
    .Return<int>(p => errorCode = p)
    .QuerySingle<User>();
```

--------------------------------

### Aggregate Queries

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/sql.md

Demonstrates how to perform COUNT and SUM aggregate functions.

```APIDOC
## GET /query/aggregate

### Description
Executes aggregate functions like COUNT or SUM on database entities, optionally grouped by specific columns.
```

--------------------------------

### SQL MERGE Statement for DataType Table Synchronization

Source: https://github.com/loresoft/fluentcommand/blob/master/test/FluentCommand.SqlServer.Tests/Snapshots/DataMergeGeneratorTests.BuildMergeDataTableTests.verified.txt

This SQL MERGE statement efficiently synchronizes data into the [dbo].[DataType] table. It compares records from a source temporary table ('#MergeTable') with the target table based on the 'Id' column. If a record does not exist in the target table, it is inserted; if it exists, it is updated. This operation handles a wide array of data types, including various numeric, date/time, and GUID types, as well as their nullable counterparts.

```sql
MERGE INTO [dbo].[DataType] AS t
USING
(
    SELECT
        [Id],
        [Name],
        [Boolean],
        [Short],
        [Long],
        [Float],
        [Double],
        [Decimal],
        [DateTime],
        [DateTimeOffset],
        [Guid],
        [TimeSpan],
        [DateOnly],
        [TimeOnly],
        [BooleanNull],
        [ShortNull],
        [LongNull],
        [FloatNull],
        [DoubleNull],
        [DecimalNull],
        [DateTimeNull],
        [DateTimeOffsetNull],
        [GuidNull],
        [TimeSpanNull],
        [DateOnlyNull],
        [TimeOnlyNull]
    FROM [#MergeTable]
)
AS s
ON
(
    t.[Id] = s.[Id]
)
WHEN NOT MATCHED BY TARGET THEN 
    INSERT
    (
        [Id],
        [Name],
        [Boolean],
        [Short],
        [Long],
        [Float],
        [Double],
        [Decimal],
        [DateTime],
        [DateTimeOffset],
        [Guid],
        [TimeSpan],
        [DateOnly],
        [TimeOnly],
        [BooleanNull],
        [ShortNull],
        [LongNull],
        [FloatNull],
        [DoubleNull],
        [DecimalNull],
        [DateTimeNull],
        [DateTimeOffsetNull],
        [GuidNull],
        [TimeSpanNull],
        [DateOnlyNull],
        [TimeOnlyNull]
    )
    VALUES
    (
        s.[Id],
        s.[Name],
        s.[Boolean],
        s.[Short],
        s.[Long],
        s.[Float],
        s.[Double],
        s.[Decimal],
        s.[DateTime],
        s.[DateTimeOffset],
        s.[Guid],
        s.[TimeSpan],
        s.[DateOnly],
        s.[TimeOnly],
        s.[BooleanNull],
        s.[ShortNull],
        s.[LongNull],
        s.[FloatNull],
        s.[DoubleNull],
        s.[DecimalNull],
        s.[DateTimeNull],
        s.[DateTimeOffsetNull],
        s.[GuidNull],
        s.[TimeSpanNull],
        s.[DateOnlyNull],
        s.[TimeOnlyNull]
    )
WHEN MATCHED THEN 
    UPDATE SET
        t.[Name] = s.[Name],
        t.[Boolean] = s.[Boolean],
        t.[Short] = s.[Short],
        t.[Long] = s.[Long],
        t.[Float] = s.[Float],
        t.[Double] = s.[Double],
        t.[Decimal] = s.[Decimal],
        t.[DateTime] = s.[DateTime],
        t.[DateTimeOffset] = s.[DateTimeOffset],
        t.[Guid] = s.[Guid],
        t.[TimeSpan] = s.[TimeSpan],
        t.[DateOnly] = s.[DateOnly],
        t.[TimeOnly] = s.[TimeOnly],
        t.[BooleanNull] = s.[BooleanNull],
        t.[ShortNull] = s.[ShortNull],
        t.[LongNull] = s.[LongNull],
        t.[FloatNull] = s.[FloatNull],
        t.[DoubleNull] = s.[DoubleNull],
        t.[DecimalNull] = s.[DecimalNull],
        t.[DateTimeNull] = s.[DateTimeNull],
        t.[DateTimeOffsetNull] = s.[DateTimeOffsetNull],
        t.[GuidNull] = s.[GuidNull],
        t.[TimeSpanNull] = s.[TimeSpanNull],
        t.[DateOnlyNull] = s.[DateOnlyNull],
        t.[TimeOnlyNull] = s.[TimeOnlyNull]
;
```

--------------------------------

### Execute Basic Select Query

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/sql.md

Demonstrates how to perform a basic SELECT query with filtering, ordering, and pagination. It maps the result set to a User object using the QuerySingleAsync method.

```csharp
await using var session = Services.GetRequiredService<IDataSession>();
string email = "kara.thrace@battlestar.com";
var user = await session
    .Sql(builder => builder
        .Select<User>()
        .Where(p => p.EmailAddress, email)
        .OrderBy(p => p.EmailAddress)
        .Limit(0, 10)
    )
    .QuerySingleAsync(r => new User
    {
        Id = r.GetGuid("Id"),
        EmailAddress = r.GetString("EmailAddress"),
        IsEmailAddressConfirmed = r.GetBoolean("IsEmailAddressConfirmed"),
        DisplayName = r.GetString("DisplayName"),
        PasswordHash = r.GetString("PasswordHash"),
        ResetHash = r.GetString("ResetHash"),
        InviteHash = r.GetString("InviteHash"),
        AccessFailedCount = r.GetInt32("AccessFailedCount"),
        LockoutEnabled = r.GetBoolean("LockoutEnabled"),
        LockoutEnd = r.GetDateTimeOffsetNull("LockoutEnd"),
        LastLogin = r.GetDateTimeOffsetNull("LastLogin"),
        IsDeleted = r.GetBoolean("IsDeleted"),
        Created = r.GetDateTimeOffset("Created"),
        CreatedBy = r.GetString("CreatedBy"),
        Updated = r.GetDateTimeOffset("Updated"),
        UpdatedBy = r.GetString("UpdatedBy"),
        RowVersion = r.GetBytes("RowVersion"),
    });
```

--------------------------------

### Perform Bulk Data Import with FluentCommand

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Demonstrates how to use the BulkCopy feature to efficiently import large collections into SQL Server tables. It supports automatic mapping, batch sizing, and configuration of identity and trigger behaviors.

```csharp
using FluentCommand;
using FluentCommand.Bulk;

var users = new List<User> { new User { Id = Guid.NewGuid(), EmailAddress = "user1@email.com", DisplayName = "User 1" } };
using var session = configuration.CreateSession();

session.BulkCopy("[User]")
    .AutoMap()
    .Ignore("RowVersion")
    .BatchSize(1000)
    .BulkCopyTimeout(600)
    .KeepIdentity()
    .CheckConstraints()
    .FireTriggers()
    .WriteToServer(users);

await session.BulkCopy("[User]").AutoMap().WriteToServerAsync(users);
```

--------------------------------

### Configure FluentCommand Query Logging

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Demonstrates how to set up query logging in FluentCommand to output executed SQL statements for debugging purposes by providing a custom DataQueryLogger.

```csharp
using FluentCommand;
using Microsoft.Data.SqlClient;

var dataLogger = new DataQueryLogger(Console.WriteLine);
var dataConfiguration = new DataConfiguration(
    SqlClientFactory.Instance,
    "Data Source=(local);Initial Catalog=TrackerTest;Integrated Security=True;TrustServerCertificate=True;",
    queryLogger: dataLogger
);
```

--------------------------------

### Initialize SQL Server Data Configuration

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/configuration.md

Configures the DataConfiguration object using the SqlClientFactory and a provided connection string. This is the foundational step for establishing database connectivity.

```csharp
var dataConfiguration  = new DataConfiguration(
    SqlClientFactory.Instance, 
    ConnectionString
);
```

--------------------------------

### Materializing Data Commands to Entities

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/generation.md

Demonstrates how to use the generated extension methods to execute SQL queries and materialize the results directly into strongly-typed entity objects.

```APIDOC
## Materializing Data Commands to Entities

### Description
Once the source generator has processed your classes with the `TableAttribute`, you can leverage the generated extension methods to execute SQL queries and directly materialize the results into your entity objects. This example shows how to query for a user by email and retrieve a single `User` object.

### Method
Extension Method Invocation

### Endpoint
N/A

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
```csharp
string email = "kara.thrace@battlestar.com";
string sql = "select * from [User] where EmailAddress = @EmailAddress";
var session = configuration.CreateSession();
var user = await session
    .Sql(sql)
    .Parameter("@EmailAddress", email)
    .QuerySingleAsync<User>();
```

### Response
#### Success Response (200)
- **user** (User) - The materialized User object from the query result.

#### Response Example
```json
{
  "Id": 1,
  "EmailAddress": "kara.thrace@battlestar.com",
  "Name": "Kara Thrace"
  // ... other user properties
}
```
```

--------------------------------

### Select with Joins

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/sql.md

Illustrates how to perform SQL JOIN operations between multiple tables. It demonstrates specifying column aliases and joining across related entities.

```csharp
await using var session = Services.GetRequiredService<IDataSession>();
string email = "@battlestar.com";
var users = await session
    .Sql(builder => builder
        .Select<User>()
        .Column(p => p.DisplayName, "u")
        .Column(p => p.EmailAddress, "u")
        .Column<Role>(p => p.Name, "r", "RoleName")
        .From(tableAlias: "u")
        .Join<UserRole>(j => j
            .Left(u => u.Id, "u")
            .Right(u => u.UserId, "ur")
        )
        .Join<UserRole, Role>(j => j
            .Left(u => u.RoleId, "ur")
            .Right(u => u.Id, "r")
        )
        .Where(p => p.EmailAddress, email, "u", FilterOperators.Contains)
        .OrderBy(p => p.Updated, "r")
        .Limit(0, 10)
    )
    .QueryAsync<User>();
```

--------------------------------

### Configure FluentCommand with Dependency Injection

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Shows how to register FluentCommand with the .NET dependency injection container. It supports configuration via connection string or a connection name defined in appsettings.json.

```csharp
using FluentCommand;
using Microsoft.Extensions.DependencyInjection;

// Register with connection string
services.AddFluentCommand(builder => builder
    .UseConnectionString("Data Source=(local);Initial Catalog=TrackerTest;Integrated Security=True;TrustServerCertificate=True;")
    .UseSqlServer()
);

// Or register using connection name from appsettings.json
services.AddFluentCommand(builder => builder
    .UseConnectionName("Tracker")
    .UseSqlServer()
);

// appsettings.json configuration:
// {
//   "ConnectionStrings": {
//     "Tracker": "Data Source=(local);Initial Catalog=TrackerTest;Integrated Security=True;TrustServerCertificate=True;"
//   }
// }

// Resolve and use in your application
var session = services.GetRequiredService<IDataSession>();
```

--------------------------------

### Constructing SELECT Queries with Filters and Ordering

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Demonstrates how to use the fluent query builder to select entities, apply a where clause for filtering, order results, and limit the output. This approach ensures type safety when referencing entity properties.

```csharp
using FluentCommand;
using FluentCommand.Query;

await using var session = services.GetRequiredService<IDataSession>();

string email = "kara.thrace@battlestar.com";

var user = await session
    .Sql(builder => builder
        .Select<User>()
        .Where(p => p.EmailAddress, email)
        .OrderBy(p => p.EmailAddress)
        .Limit(0, 10)
    )
    .QuerySingleAsync<User>();
```

--------------------------------

### Executing Stored Procedures

Source: https://github.com/loresoft/fluentcommand/blob/master/README.md

Shows how to execute a stored procedure, including passing parameters and handling output parameters.

```APIDOC
## Execute Stored Procedure with Output Parameters

### Description
Executes a stored procedure (`aspnet_Membership_CreateUser`) and captures output parameters for user ID and an error code.

### Method
POST (Implicit)

### Endpoint
N/A (Stored Procedure Execution)

### Parameters
#### Stored Procedure Parameters
- **@ApplicationName** (string) - Required - The application name.
- **@UserName** (string) - Required - The username to create.
- **@Password** (string) - Required - The user's password.
- **@Email** (string) - Required - The user's email address.
- **@PasswordSalt** (string) - Required - The password salt.
- **@PasswordQuestion** (string) - Optional - The password question.
- **@PasswordAnswer** (string) - Optional - The password answer.
- **@IsApproved** (bool) - Required - Whether the user is approved.
- **@CurrentTimeUtc** (DateTime) - Required - The current UTC time.
- **@UniqueEmail** (int) - Required - Flag for unique email (1 for true).
- **@PasswordFormat** (int) - Required - The password format.
- **@UserId** (out Guid) - Output - The ID of the created user.
- **Return Value** (out int) - Output - An error code indicating the result of the operation.

### Request Example
```csharp
Guid userId = Guid.Empty;
int errorCode = -1;

var username = "test." + DateTime.Now.Ticks;
var email = username + "@email.com";

var session = configuration.CreateSession();
var result = session
    .StoredProcedure("[dbo].[aspnet_Membership_CreateUser]")
    .Parameter("@ApplicationName", "/")
    .Parameter("@UserName", username)
    .Parameter("@Password", "T@est" + DateTime.Now.Ticks)
    .Parameter("@Email", email)
    .Parameter("@PasswordSalt", "test salt")
    .Parameter<string>("@PasswordQuestion", null)
    .Parameter<string>("@PasswordAnswer", null)
    .Parameter("@IsApproved", true)
    .Parameter("@CurrentTimeUtc", DateTime.UtcNow)
    .Parameter("@UniqueEmail", 1)
    .Parameter("@PasswordFormat", 1)
    .ParameterOut<Guid>("@UserId", p => userId = p)
    .Return<int>(p => errorCode = p)
    .Execute();
```

### Response
#### Success Response (0)
- **userId** (Guid) - The ID of the newly created user.
- **errorCode** (int) - 0 indicates success.

#### Response Example
```json
{
  "userId": "generated-guid-value",
  "errorCode": 0
}
```
```

--------------------------------

### Register FluentCommand with Dependency Injection using Connection Name for SQL Server

Source: https://github.com/loresoft/fluentcommand/blob/master/README.md

Illustrates registering FluentCommand with dependency injection, using a connection name defined in the appsettings.json file for SQL Server. This approach is useful for managing multiple database connections.

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

--------------------------------

### Basic Select Query

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/sql.md

Demonstrates how to perform a basic select operation with filtering, ordering, and pagination.

```APIDOC
## GET /query/select

### Description
Retrieves a single user record based on email address with ordering and pagination.

### Method
GET

### Parameters
#### Request Body
- **email** (string) - Required - The email address to filter by.

### Request Example
{
  "email": "kara.thrace@battlestar.com"
}

### Response
#### Success Response (200)
- **User** (object) - The user entity matching the criteria.
```

--------------------------------

### Query Builder API

Source: https://github.com/loresoft/fluentcommand/blob/master/README.md

Demonstrates using the FluentCommand Query Builder to construct SQL statements dynamically.

```APIDOC
## Query Builder - Select Statement

### Description
Constructs a SELECT statement using the Query Builder, leveraging DataAnnotations for schema information. It selects a user by email address.

### Method
GET (Implicit)

### Endpoint
N/A (Query Builder)

### Parameters
#### Query Builder Parameters
- **Select<User>()**: Specifies the table to select from based on the User entity's attributes.
- **Where(p => p.EmailAddress, email)**: Adds a WHERE clause to filter by the EmailAddress field.

### Request Example
```csharp
var session = configuration.CreateSession();

string email = "kara.thrace@battlestar.com";

var user = await session
    .Sql(builder => builder
        .Select<User>() // table name comes from type
        .Where(p => p.EmailAddress, email)
    )
    .QuerySingleAsync<User>();
```

### Response
#### Success Response (200)
- **user** (User) - The User object matching the query.

#### Response Example
```json
{
  "Id": "guid-value",
  "EmailAddress": "kara.thrace@battlestar.com",
  "DisplayName": "Kara Thrace"
  // ... other user properties
}
```
```

```APIDOC
## Query Builder - Count Statement

### Description
Constructs a COUNT query using the Query Builder to efficiently get the number of records matching specific criteria.

### Method
GET (Implicit)

### Endpoint
N/A (Query Builder)

### Parameters
#### Query Builder Parameters
- **Select<User>()**: Specifies the table to count from.
- **Count()**: Specifies that a count aggregation should be performed.
- **Where(p => p.EmailAddress, email)**: Adds a WHERE clause to filter the count.

### Request Example
```csharp
string email = "kara.thrace@battlestar.com";

var count = await session
    .Sql(builder => builder
        .Select<User>()
        .Count()
        .Where(p => p.EmailAddress, email)
    )
    .QueryValueAsync<int>();
```

### Response
#### Success Response (200)
- **count** (int) - The number of records matching the criteria.

#### Response Example
```json
{
  "count": 5
}
```
```

```APIDOC
## Query Builder - Insert Statement

### Description
Constructs an INSERT statement using the Query Builder. It inserts a new user record and outputs the generated ID.

### Method
POST (Implicit)

### Endpoint
N/A (Query Builder)

### Parameters
#### Query Builder Parameters
- **Insert<User>()**: Specifies the table to insert into.
- **Value(p => p.FieldName, value)**: Sets the value for a specific column.
- **Output(p => p.Id)**: Specifies that the ID column should be returned as an output value.
- **Tag()**: Adds a comment tag to the generated SQL.

### Request Example
```csharp
var id = Guid.NewGuid();

var userId = await session
    .Sql(builder => builder
        .Insert<User>()
        .Value(p => p.Id, id)
        .Value(p => p.EmailAddress, $"{id}@email.com")
        .Value(p => p.DisplayName, "Last, First")
        .Value(p => p.FirstName, "First")
        .Value(p => p.LastName, "Last")
        .Output(p => p.Id) // return key as output value
        .Tag() // add comment tag to query
    )
    .QueryValueAsync<Guid>();
```

### Response
#### Success Response (200)
- **userId** (Guid) - The ID of the newly inserted record.

#### Response Example
```json
{
  "userId": "generated-guid-value"
}
```
```

```APIDOC
## Query Builder - Update Statement

### Description
Constructs an UPDATE statement using the Query Builder to modify an existing user record and outputs the updated ID.

### Method
PUT (Implicit)

### Endpoint
N/A (Query Builder)

### Parameters
#### Query Builder Parameters
- **Update<User>()**: Specifies the table to update.
- **Value(p => p.FieldName, value)**: Sets the new value for a column.
- **Output(p => p.Id)**: Specifies that the ID column should be returned.
- **Where(p => p.Id, id)**: Adds a WHERE clause to specify which record to update.
- **Tag()**: Adds a comment tag to the generated SQL.

### Request Example
```csharp
var updateId = await session
    .Sql(builder => builder
        .Update<User>()
        .Value(p => p.DisplayName, "Updated Name")
        .Output(p => p.Id)
        .Where(p => p.Id, id)
        .Tag()
    )
    .QueryValueAsync<Guid>();
```

### Response
#### Success Response (200)
- **updateId** (Guid) - The ID of the updated record.

#### Response Example
```json
{
  "updateId": "guid-of-updated-record"
}
```
```

```APIDOC
## Query Builder - Delete Statement

### Description
Constructs a DELETE statement using the Query Builder to remove a user record and outputs the ID of the deleted record.

### Method
DELETE (Implicit)

### Endpoint
N/A (Query Builder)

### Parameters
#### Query Builder Parameters
- **Delete<User>()**: Specifies the table to delete from.
- **Output(p => p.Id)**: Specifies that the ID column should be returned.
- **Where(p => p.Id, id)**: Adds a WHERE clause to specify which record to delete.
- **Tag()**: Adds a comment tag to the generated SQL.

### Request Example
```csharp
var deleteId = await session
    .Sql(builder => builder
        .Delete<User>()
        .Output(p => p.Id)
        .Where(p => p.Id, id)
        .Tag()
    )
    .QueryValueAsync<Guid>();
```

### Response
#### Success Response (200)
- **deleteId** (Guid) - The ID of the deleted record.

#### Response Example
```json
{
  "deleteId": "guid-of-deleted-record"
}
```
```

--------------------------------

### SELECT Query with Filtering

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Demonstrates how to perform a SELECT operation with WHERE clauses, ordering, and pagination limits.

```APIDOC
## SELECT Query with Where Clause

### Description
Constructs a SELECT statement with specific filtering, ordering, and pagination using the fluent builder.

### Method
GET (Conceptual)

### Endpoint
N/A (Fluent C# API)

### Parameters
#### Request Body
- **EmailAddress** (string) - Required - The email address to filter by.

### Request Example
{
  "email": "kara.thrace@battlestar.com"
}

### Response
#### Success Response (200)
- **User** (object) - The retrieved user record.
```

--------------------------------

### Configure Data Logger for SQL Server

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/configuration.md

Sets up a DataQueryLogger to capture and output SQL execution details. It integrates the logger into the DataConfiguration instance.

```csharp
var dataLogger = new DataQueryLogger(Output.WriteLine);
var dataConfiguration  = new DataConfiguration(
    SqlClientFactory.Instance, 
    ConnectionString,
    queryLogger: dataLogger
);
```

--------------------------------

### Execute Complex Multiple SQL Statements

Source: https://github.com/loresoft/fluentcommand/blob/master/docs/guide/sql.md

Demonstrates how to chain multiple SQL statements including table creation, data insertion, and complex joins using the FluentCommand builder.

```csharp
await using var session = Services.GetRequiredService<IDataSession>();
var values = new[] { 1, 2, 3 }.ToDelimitedString();
var results = await session
    .Sql(builder =>
    {
        builder.Statement().Query("CREATE TABLE #identifiers (Id int);");
        builder.Statement().Query("INSERT INTO #identifiers (Id) SELECT CONVERT(int, value) FROM STRING_SPLIT(@Identifiers, @Separator);").Parameter("@Identifiers", values).Parameter("@Separator", ",");
        builder.Select<Status>().Tag().From(tableAlias: "s").Join(j => j.Left("Id", "s").Right("Id", "#identifiers", null, "i"));
    })
    .QueryAsync<Status>();
```

--------------------------------

### Performing Table Joins

Source: https://context7.com/loresoft/fluentcommand/llms.txt

Illustrates how to perform SQL joins between multiple entities using strongly-typed column references and table aliases. This is useful for retrieving related data across different tables.

```csharp
using FluentCommand;
using FluentCommand.Query;

await using var session = services.GetRequiredService<IDataSession>();

string email = "@battlestar.com";

var users = await session
    .Sql(builder => builder
        .Select<User>()
        .Column(p => p.DisplayName, "u")
        .Column(p => p.EmailAddress, "u")
        .Column<Role>(p => p.Name, "r", "RoleName")
        .From(tableAlias: "u")
        .Join<UserRole>(j => j
            .Left(u => u.Id, "u")
            .Right(u => u.UserId, "ur")
        )
        .Join<UserRole, Role>(j => j
            .Left(u => u.RoleId, "ur")
            .Right(u => u.Id, "r")
        )
        .Where(p => p.EmailAddress, email, "u", FilterOperators.Contains)
        .OrderBy(p => p.Updated, "r")
        .Limit(0, 10)
    )
    .QueryAsync<User>();
```