Carrigan.SqlTools.Generators.SqlServer 0.10.0

<a id="carrigan.sqltools.generators.sqlserver"></a>

Carrigan.SqlTools.Generators.SqlServer

Carrigan.SqlTools.Generators.SqlServer is a .NET library that simplifies SQL generation for Microsoft SQL Server while still giving you control when you need it.

It automatically generates SELECT, INSERT, UPDATE, and DELETE statements using reflection. Carrigan.SqlTools.Generators.SqlServer adds a safe, object-oriented API for building more advanced queries.

The companion library Carrigan.SqlTools.Clients.SqlServer extends the generator by wrapping ADO.NET to execute generated queries, map rows to objects, and handle decryption of encrypted properties.

The transitive dependency Carrigan.Core provides interfaces, property attributes for custom property-level encryption, and shared helper functionality.

This package includes Roslyn analyzers bundled with the package to assist with safe usage patterns.

SQL Server is a trademark of Microsoft Corporation. This project is not affiliated with or endorsed by Microsoft.

This library may generate or execute SQL. Review generated SQL before use in production systems. Always test against non-production databases first.

Use caution with schema, migration, and data-modifying operations. The authors are not responsible for data loss, downtime, or unintended database changes.

---

Table of Contents

• Features
• Installation
• Getting Started Examples
  • Select All Rows
  • Select by Id
  • Insert
  • Insert with Auto Id
  • Update by Id
  • Update by Id (selected columns)
  • Delete
  • Delete by Id (multiple keys)
• More Complex Examples
  • Select with Joins and Order By
  • Select with Two Part Order By
  • Delete with Join and Where
  • Select Count With Where
  • Update with Joins and Where
  • GroupBy Example
  • Having Example
• SqlExpression Examples
  • Aggregate Expression Examples
    • Average Examples
    • Avg Examples
    • Count Examples
    • Max Examples
    • Min Examples
    • Sum Examples
  • Conversion Operations
    • Cast Example
    • TryCase Example
  • Date Time Operations
    • CurrentDate Example
    • CurrentTimeStamp Example
    • GetDate Example
    • DateAdd Example
    • DateFromParts Example
    • DatePart Example
    • DateTrunc Example
    • EoMonth Example
  • Math Operations
    • Abs Example
    • Add Examples
    • Ceiling Example
    • Divide Examples
    • Floor Example
    • Minus Examples
    • Mod Examples
    • Multiply Examples
    • Negate Examples
    • Power Example
    • Round Example
    • Sign Example
    • SquareRoot Example
    • Subtract Example
    • Truncate Example
  • Null Operations
    • Coalesce Example
    • NullIf Example
  • Predicate Operations
    • And Examples
    • Between Example
    • Column Example
    • ColumnValue Example
    • ColumnEqualsColumn Example
    • Contains Example
    • Equal Example
    • GreaterThan Example
    • GreaterThanEquals Example
    • IsNotNull Example
    • IsNull Example
    • LessThan Example
    • LessThanEquals Example
    • Like Example
    • NotBetween Example
    • Not Example
    • NotEqual Example
    • Or Examples
    • Parameter Examples
  • String Operations
    • CharIndexOf Example
    • Concat Example
    • IndexOf Example
    • Left Example
    • Length Example
    • Lower Example
    • LRTrim Example
    • LTrim Examples
    • PatIndex Example
    • Repeat Example
    • Replace Examples
    • Right Example
    • RTrim Examples
    • Stuff Example
    • Substring Example
    • Trim Examples
    • Upper Example
• Attribute Examples
  • Table, Column and Key
  • Identifier and Primary Key
  • Procedure and Parameter
• Running Queries (Async & Non-Async)
  • Async: ExecuteNonQueryAsync / ExecuteScalarAsync / ExecuteReaderAsync<T>
  • Non-Async: ExecuteNonQuery / ExecuteScalar / ExecuteReader<T>
• Simple ADO.NET Example With SqlQuery
• Data Type Mappings
  • Default Parameter / Return Types
  • Allowed Override Types
  • Allowed Override Types With Warnings
• ExampleEncryptor (AesGcm-based) and IDecrypters
• License

---

Features

• Automatic CRUD generation
  Build SELECT, INSERT, UPDATE, and DELETE queries with reflection.

• Carrigan.Core integration
  Interfaces and property-level attributes enable property-level encryption and decryption.

• Manual query builder
  Safely construct advanced SQL with JOIN, predicates (e.g., Equal, GreaterThan, IsNull, Like, And, Or, Xor, Not), GROUP BY, HAVING, ORDER BY, and pagination (OFFSET/FETCH NEXT).

• Dictionary → object mapping
  Use the invocation system to populate typed models from database rows.

• Focused on SQL Server
  SQL output targets Microsoft SQL Server.

• Execution helpers
  Async commands (CommandsAsync) and non-async commands (Commands) for running queries and reading results.

Table of Contents

---

Installation

Install the SQL Server convenience package when you want both SQL Server SQL generation and SQL Server execution helpers:

dotnet add package Carrigan.SqlTools.SqlServer

If you only need one side of the dialect support, you can install the generator and client packages separately:

dotnet add package Carrigan.SqlTools.Generators.SqlServer
dotnet add package Carrigan.SqlTools.Clients.SqlServer

Table of Contents

---

Getting Started Examples

We use SqlGenerator<T> to produce a SqlQuery (with QueryText, CommandType, and Parameters).

All examples use the following using statements to keep the code examples focused on SQL generation.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.Base.Tests.Helpers;
using Carrigan.SqlTools.Base.Tests.TestEntities;
using Carrigan.SqlTools.PredicatesLogic;
using Carrigan.SqlTools.Sets;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;
using System.Text;

// Example data models
public class Customer
{
    [PrimaryKey] // Note: PrimaryKey takes precedence over Key for the SQL generator.
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Phone { get; set; } = "";
}

public class Order
{
    [PrimaryKey] // Required attribute for certain SQL generation methods.
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int PaymentMethodId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
}

// Generators
public SqlGenerator<Customer> customerGenerator = new();

Table of Contents

Select All Rows

SqlQuery query = customerGenerator.SelectAll();

SELECT [Customer].* FROM [Customer]

SelectBuilder<Customer> selectBuilder = new();
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* FROM [Customer]

Table of Contents

Select by Id

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer entity = new() { Id = 42 };
SqlQuery query = customerGenerator.SelectById(entity);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Id] = @Id_1)

Table of Contents

Insert

Customer entity = new()
{
    Id = 42,
    Name = "Hank",
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};
InsertBuilder<Customer> insertBuilder = new()
{
    Records = [entity]
};
SqlQuery query = customerGenerator.Insert(insertBuilder);

INSERT INTO [Customer] ([Id], [Name], [Email], [Phone])
VALUES (@Id_1, @Name_2, @Email_3, @Phone_4);

Table of Contents

Insert with Auto Id

A key attribute is required, and identity columns must be generated by the database.

Customer entity = new() 
{ 
    Name = "Hank", 
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555" 
};
SqlQuery query = customerGenerator.InsertAutoId(entity);

DECLARE @OutputTable TABLE (InsertedId INT NOT NULL);
INSERT INTO [Customer] ([Name], [Email], [Phone])
OUTPUT INSERTED.[Id] INTO @OutputTable
VALUES (@Name_1, @Email_2, @Phone_3);
SELECT [InsertedId] FROM @OutputTable;

Table of Contents

Update by Id

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer entity = new() 
{ 
    Id = 42, 
    Name = "Hank", 
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};
SqlQuery query = customerGenerator.UpdateById(entity);

UPDATE [Customer] SET [Name] = @Name_1, [Email] = @Email_2, [Phone] = @Phone_3
WHERE [Id] = @Id_4;

Table of Contents

<a id="update-by-id-selected-columns"></a>

Update by Id (selected columns)

A key attribute is required. Composite keys are supported by marking multiple key properties.

ColumnCollection<T> validates property names and throws an error when a property name is not valid.

ColumnCollection<Customer> columns = new(nameof(Customer.Email));
Customer entity = new() { Id = 42, Name = "Hank", Email = "Hank@example.gov" };
SqlQuery query = customerGenerator.UpdateById(entity, columns);

UPDATE [Customer] 
SET [Email] = @Email_1 
WHERE [Id] = @Id_2;

ColumnCollection<T> validates property names and throws an error when a property name is not valid.

UpdateBuilder<Customer> updateBuilder = new()
{
    Where = new ColumnValue<Customer>(nameof(Customer.Id), 42),
    UpdateColumns = new ColumnCollection<Customer>(nameof(Customer.Email)),
    Values = new() { Email = "Hank@example.gov" }
};
SqlQuery query = updateBuilder.AsSqlQuery();

UPDATE [Customer] 
SET [Email] = @Email_1
WHERE ([Customer].[Id] = @Id_2)

Table of Contents

Delete

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer entity = new() { Id = 42 };
SqlQuery query = customerGenerator.Delete(entity);

DELETE
FROM [Customer]
WHERE ([Customer].[Id] = @Id_1)

Note: ColumnCollection<T> validates the names of the properties, and throws an error if the property isn't valid

DeleteBuilder<Customer> deleteBuilder = new()
{
    Where = new ColumnValue<Customer>(nameof(Customer.Id), 42),
};
SqlQuery query = deleteBuilder.AsSqlQuery();

DELETE 
FROM [Customer] 
WHERE ([Customer].[Id] = @Id_1)

Table of Contents

<a id="delete-by-id-multiple-keys"></a>

Delete by Id (multiple keys)

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer[] entities = [new() { Id = 1 }, new() { Id = 2 }];
SqlQuery query = customerGenerator.DeleteById(entities);

DELETE FROM [Customer] 
WHERE (([Customer].[Id] = @Id_1) 
   OR ([Customer].[Id] = @Id_2))

Note: ColumnCollection<T> validates the names of the properties, and throws an error if the property isn't valid

DeleteBuilder<Customer> deleteBuilder = new()
{
    Where = new Or
    (
        new ColumnValue<Customer>(nameof(Customer.Id), 1),
        new ColumnValue<Customer>(nameof(Customer.Id), 2)
    )
};
SqlQuery query = deleteBuilder.AsSqlQuery();

DELETE FROM [Customer] 
WHERE (([Customer].[Id] = @Id_1) 
   OR ([Customer].[Id] = @Id_2))

Table of Contents

---

More Complex Examples

We use SqlGenerator<T> to produce a SqlQuery (with QueryText, CommandType, and Parameters).

All examples use the following using statements to keep the code examples focused on SQL generation.

using Carrigan.SqlTools.AggregateLogic;
using Carrigan.SqlTools.Expressions;
using Carrigan.SqlTools.GroupByClause;
using Carrigan.SqlTools.JoinTypes;
using Carrigan.SqlTools.OrderByClause;
using Carrigan.SqlTools.PredicatesLogic;
using Carrigan.SqlTools.Sets;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;
using Carrigan.SqlTools.Tags;

// Example data models
public class Customer
{
    [PrimaryKey] // Note: PrimaryKey takes precedence over Key for the SQL generator.
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Phone { get; set; } = "";
}

public class Order
{
    [PrimaryKey] // Required attribute for certain SQL generation methods.
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int PaymentMethodId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
}

// Generators
public SqlGenerator<Customer> customerGenerator = new();
public SqlGenerator<Order> orderGenerator = new();

Select with Joins and Order By

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

OrderBy<Order> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));
InnerJoin<Order> join = new(predicate);

OrderBy<Order> orderByOrderDate = new(nameof(Order.OrderDate));

SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join,
    OrderBys = orderByOrderDate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
INNER JOIN [Order] 
   ON ([Customer].[Id] = [Order].[CustomerId]) 
ORDER BY [Order].[OrderDate] ASC

Table of Contents

Select with Two Part Order By

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

OrderBy<Order> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));

InnerJoin<Order> join = new(predicate);

OrderBy<Order> orderByOrderDate = new(nameof(Order.OrderDate));
OrderBy<Customer> orderByCustomerId = new(nameof(Customer.Id), SortDirectionEnum.Descending);
OrderBys orderBys = new(orderByCustomerId, orderByOrderDate);

SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join,
    OrderBys = orderBys
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
INNER JOIN [Order] 
   ON ([Customer].[Id] = [Order].[CustomerId])
ORDER BY [Customer].[Id] DESC,
         [Order].[OrderDate] ASC

Table of Contents

Delete with Join and Where

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

ColumnValue<T> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));

InnerJoin<Customer> join = new(predicate);

ColumnValue<Customer> customerEmail = new(nameof(Customer.Email), "spam@example.com");

DeleteBuilder<Order> deleteBuilder = new()
{
    Joins = join,
    Where = customerEmail
};

SqlQuery query = orderGenerator.Delete(deleteBuilder);

DELETE [Order] 
FROM [Order] 
INNER JOIN [Customer] 
   ON ([Customer].[Id] = [Order].[CustomerId]) 
WHERE ([Customer].[Email] = @Email_1)

Table of Contents

Select Count With Where

Column<T> validates property names and throws an error when a property name is not valid.

Column<Order> totalCol = new(nameof(Order.Total));
Parameter minTotal = new(500m, "Total");
GreaterThan greaterThan = new(totalCol, minTotal);

SelectBuilder<Order> selectBuilder = new()
{
    Selects = new SelectTag(new Count(), "OrderCount"),
    Where = greaterThan
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT(*) AS [OrderCount]
FROM [Order] 
WHERE ([Order].[Total] > @Total_1)

Table of Contents

Update with Joins and Where

ColumnCollection<T> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

ColumnValue<T> validates property names and throws an error when a property name is not valid.

Order entity = new() { Id = 10, Total = 123.45m };

ColumnCollection<Order> columnCollection = new(nameof(Order.Total));

ColumnEqualsColumn<Order, Customer> predicate = new(nameof(Order.CustomerId), nameof(Customer.Id));

InnerJoin<Customer> join = new(predicate);

ColumnValue<Customer> customerEmailEquals = new(nameof(Customer.Email), "spam@example.com");

UpdateBuilder<Order> updateBuilder = new()
{
    Values = entity,
    UpdateColumns = columnCollection,
    Joins = join,
    Where = customerEmailEquals
};

SqlQuery query = orderGenerator.Update(updateBuilder);

UPDATE [Order] 
SET [Order].[Total] = @Total_1 
FROM [Order] 
INNER JOIN [Customer] 
   ON ([Order].[CustomerId] = [Customer].[Id]) 
WHERE ([Customer].[Email] = @Email_2)

Table of Contents

GroupBy Example

Column<Grades> gradePoint = new(nameof(Grades.GradePoint));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.CourseCode)),
        new SelectTag(new Average(gradePoint), "AverageGradePoint"),
        new SelectTag(new Sum(gradePoint), "TotalGradePoints"),
        new SelectTag(new Min(gradePoint), "MinimumGradePoint"),
        new SelectTag(new Max(gradePoint), "MaximumGradePoint"),
        new SelectTag(new Count(gradePoint), "GradePointCount")
    ),
    GroupBys = new GroupBys<Grades>(nameof(Grades.StudentId))
        .Append<Grades>(nameof(Grades.CourseCode))
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT 
    [Grades].[StudentId], 
    [Grades].[CourseCode], 
    AVG([Grades].[GradePoint]) AS [AverageGradePoint], 
    SUM([Grades].[GradePoint]) AS [TotalGradePoints], 
    MIN([Grades].[GradePoint]) AS [MinimumGradePoint], 
    MAX([Grades].[GradePoint]) AS [MaximumGradePoint], 
    COUNT([Grades].[GradePoint]) AS [GradePointCount]
FROM [Grades]
GROUP BY 
    [Grades].[StudentId], 
    [Grades].[CourseCode]

Table of Contents

---

Having Example

Average semesterGpa = new(new Column<Grades>(nameof(Grades.GradePoint)));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.AcademicYear)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.SemesterNumber)),
        new SelectTag(semesterGpa, "SemesterGPA")
    ),
    GroupBys = GroupBys.New<Grades>(nameof(Grades.StudentId), nameof(Grades.AcademicYear), nameof(Grades.SemesterNumber)),
    Having = new GreaterThan(semesterGpa, new Parameter(3.5, "HonorRollGpa"))
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT 
    [Grades].[StudentId], 
    [Grades].[AcademicYear], 
    [Grades].[SemesterNumber], 
    AVG([Grades].[GradePoint]) AS [SemesterGPA] 
FROM 
    [Grades] 
GROUP BY 
    [Grades].[StudentId], 
    [Grades].[AcademicYear], 
    [Grades].[SemesterNumber] 
HAVING 
    (AVG([Grades].[GradePoint]) > @HonorRollGpa_1)

Table of Contents

---

SqlExpression Examples

Aggregate Expression Examples

Column<Grades> gradePoint = new(nameof(Grades.GradePoint));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.CourseCode)),
        new SelectTag(new Average(gradePoint), "AverageGradePoint"),
        new SelectTag(new Sum(gradePoint), "TotalGradePoints"),
        new SelectTag(new Min(gradePoint), "MinimumGradePoint"),
        new SelectTag(new Max(gradePoint), "MaximumGradePoint"),
        new SelectTag(new Count(gradePoint), "GradePointCount")
    ),
    GroupBys = new GroupBys<Grades>(nameof(Grades.StudentId))
        .Append<Grades>(nameof(Grades.CourseCode))
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT 
    [Grades].[StudentId], 
    [Grades].[CourseCode], 
    AVG([Grades].[GradePoint]) AS [AverageGradePoint], 
    SUM([Grades].[GradePoint]) AS [TotalGradePoints],
    MIN([Grades].[GradePoint]) AS [MinimumGradePoint], 
    MAX([Grades].[GradePoint]) AS [MaximumGradePoint], 
    COUNT([Grades].[GradePoint]) AS [GradePointCount] 
FROM [Grades] 
GROUP BY 
    [Grades].[StudentId],
    [Grades].[CourseCode]

Table of Contents

---

Average Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Average(new Column<Grades>(nameof(Grades.GradePoint))),
            "OverallAverageGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG([Grades].[GradePoint]) AS [OverallAverageGradePoint] FROM [Grades]

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Average(new Column<Grades>(nameof(Grades.GradePoint)), true),
            "OverallAverageGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG(DISTINCT [Grades].[GradePoint]) AS [OverallAverageGradePoint] FROM [Grades]

Table of Contents

---

Avg Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Avg(new Column<Grades>(nameof(Grades.GradePoint))),
            "OverallAvgGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG([Grades].[GradePoint]) AS [OverallAvgGradePoint] FROM [Grades]

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Avg(new Column<Grades>(nameof(Grades.GradePoint)), true),
            "OverallAvgGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG(DISTINCT [Grades].[GradePoint]) AS [OverallAvgGradePoint] FROM [Grades]

Table of Contents

---

Count Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Count(new Column<Grades>(nameof(Grades.GradePoint))),
            "GradePointCount"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT([Grades].[GradePoint]) AS [GradePointCount] FROM [Grades]

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Count(new Column<Grades>(nameof(Grades.GradePoint)), true),
            "GradePointCount"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT(DISTINCT [Grades].[GradePoint]) AS [GradePointCount] FROM [Grades]

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Count(),
            "GradeRecordCount"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT(*) AS [GradeRecordCount] FROM [Grades]

Table of Contents

---

Max Examples

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Max(new Column<Grades>(nameof(Grades.GradePoint))),
                    "MaximumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MAX([Grades].[GradePoint]) AS [MaximumGradePoint] FROM [Grades]

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Max(new Column<Grades>(nameof(Grades.GradePoint)), true),
                    "MaximumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MAX(DISTINCT [Grades].[GradePoint]) AS [MaximumGradePoint] FROM [Grades]

Table of Contents

---

Min Examples

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Min(new Column<Grades>(nameof(Grades.GradePoint))),
                    "MinimumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MIN([Grades].[GradePoint]) AS [MinimumGradePoint] FROM [Grades]

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Min(new Column<Grades>(nameof(Grades.GradePoint)), true),
                    "MinimumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MIN(DISTINCT [Grades].[GradePoint]) AS [MinimumGradePoint] FROM [Grades]

Table of Contents

---

Sum Examples

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Sum(new Column<Grades>(nameof(Grades.GradePoint))),
                    "TotalGradePoints"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT SUM([Grades].[GradePoint]) AS [TotalGradePoints] FROM [Grades]

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Sum(new Column<Grades>(nameof(Grades.GradePoint)), true),
                    "TotalGradePoints"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT SUM(DISTINCT [Grades].[GradePoint]) AS [TotalGradePoints] FROM [Grades]

Table of Contents

---

Conversion Operations

Cast Example

Cast expression = new(new Parameter("123"), SqlServerTypesProvider.AsInt(true));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CAST(@Parameter_1 AS INT) AS [Value] FROM [Customer]

Table of Contents

---

TryCast Example

TryCast expression = new(new Parameter("123"), SqlServerTypesProvider.AsInt(true));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT TRY_CAST(@Parameter_1 AS INT) AS [Value] FROM [Customer]

Table of Contents

---

Date Time Operations

CurrentDate Example

CurrentDate currentDate = new();
ColumnBase columnBase = new Column<Customer>(nameof(Customer.Name));
SelectBuilder<Customer> selectBuilder = new()
{
    Selects = new SelectTags(new SelectTag(currentDate, "Date"), new SelectTag(columnBase, "Name"))
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CURRENT_DATE AS [Date], [Customer].[Name] AS [Name] FROM [Customer]

Table of Contents

---

CurrentTimeStamp Example

CurrentTimeStamp currentTimeStamp = new();
ColumnBase columnBase = new Column<Customer>(nameof(Customer.Name));
SelectBuilder<Customer> selectBuilder = new()
{
    Selects = new SelectTags(new SelectTag(currentTimeStamp, "TimeStamp"), new SelectTag(columnBase, "Name"))
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CURRENT_TIMESTAMP AS [TimeStamp], [Customer].[Name] AS [Name] FROM [Customer]

Table of Contents

---

DateAdd Example

DateAdd expression = new(DateAddDateTimePartEnum.Day, new Parameter(2), new Parameter(new DateTime(2026, 9, 23)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT DATEADD(day, @Parameter_1, @Parameter_2) AS [Value] FROM [Customer]

Table of Contents

---

DateFromParts Example

DateFromParts expression = new(new Parameter(2026), new Parameter(9), new Parameter(23));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT DATEFROMPARTS(@Parameter_1, @Parameter_2, @Parameter_3) AS [Value] FROM [Customer]

Table of Contents

---

DatePart Example

DatePart expression = new(DatePartDateTimePartEnum.Year, new Parameter(new DateTime(2026, 9, 23)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT DATEPART(year, @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

DateTrunc Example

DateTrunc expression = new(DateTruncDateTimePartEnum.Month, new Parameter(new DateTime(2026, 9, 23)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT DATETRUNC(month, @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

EOMonth Example

EOMonth expression = new(new Parameter(new DateTime(2026, 9, 23)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT EOMONTH(@Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

GetDate Example

GetDate getDate = new();
ColumnBase columnBase = new Column<Customer>(nameof(Customer.Name));
SelectBuilder<Customer> selectBuilder = new()
{
    Selects = new SelectTags(new SelectTag(getDate, "Date"), new SelectTag(columnBase, "Name"))
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT GETDATE() AS [Date], [Customer].[Name] AS [Name] FROM [Customer]

Table of Contents

---

Math Operations

Abs Example

Abs abs = new(new Column<Order>(nameof(Order.Total)));
SelectTags selects = new(new SelectTag(abs, "AbsValue"));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT ABS([Order].[Total]) AS [AbsValue] FROM [Order]

Table of Contents

---

Add Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Add
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(1)
            ),
            "ArthemicResult"
        )
    )
};
SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ([Grades].[CreditHours] + @Parameter_1) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Ceiling Example

Ceiling expression = new (new Column<Order>(nameof(Order.Total)));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT CEILING([Order].[Total]) AS [Value] FROM [Order]

Table of Contents

---

Divide Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Divide
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(2)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ([Grades].[CreditHours] / @Parameter_1) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Floor Example

Floor expression = new(new Column<Order>(nameof(Order.Total)));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT FLOOR([Order].[Total]) AS [Value] FROM [Order]

Table of Contents

---

Minus Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Minus
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(1)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ([Grades].[CreditHours] - @Parameter_1) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Mod Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Mod
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(2)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ([Grades].[CreditHours] % @Parameter_1) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Multiply Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Multiply
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(2)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ([Grades].[CreditHours] * @Parameter_1) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Negate Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Negate
            (
                new Column<Grades>(nameof(Grades.CreditHours))
            ),
            "ArthemicResult"
        )
    )
};

SELECT (-[Grades].[CreditHours]) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Power Example

Power expression = new(new Column<Order>(nameof(Order.Total)), 2);

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT POWER([Order].[Total], @Parameter_1) AS [Value] FROM [Order]

Table of Contents

---

Sign Example

Sign expression = new(new Column<Order>(nameof(Order.Total)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT SIGN([Order].[Total]) AS [Value] FROM [Order]

Table of Contents

---

SquareRoot Example

SquareRoot expression = new(new Column<Order>(nameof(Order.Total)));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT SQRT([Order].[Total]) AS [Value] FROM [Order]

Table of Contents

---

Subtract Example

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Subtract
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(1)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ([Grades].[CreditHours] - @Parameter_1) AS [ArthemicResult] FROM [Grades]

Table of Contents

---

Truncate Example

Truncate expression = new(new Column<Order>(nameof(Order.Total)), 2);

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT ROUND([Order].[Total], @Parameter_1, 1) AS [Value] FROM [Order]

Table of Contents

---

Null Operations

Coalesce Example

Coalesce coalesce = new(new Column<Customer>(nameof(Customer.Phone)), new Column<Customer>(nameof(Customer.Email)));
SelectTags selects = new(new SelectTag(coalesce, "Coalescence"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT COALESCE([Customer].[Phone], [Customer].[Email]) AS [Coalescence] FROM [Customer]

Table of Contents

---

NullIf Example

NullIf nullIf = new(new Column<Customer>(nameof(Customer.Phone)), new Parameter(string.Empty));
SelectTags selects = new(new SelectTag(nullIf, "NullIf"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT NULLIF([Customer].[Phone], @Parameter_1) AS [NullIf] FROM [Customer]

Table of Contents

---

Predicate Operations

And Examples

ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
ColumnValue<Customer> equalEmail = new(nameof(Customer.Email), "Hank@example.com");
ColumnValue<Customer> equalPhone = new(nameof(Customer.Phone), "+1(555)555-5555");
And and = new(equalName, equalEmail, equalPhone);

SelectBuilder<Customer> selectBuilder = new()
{
    Where = and
};

SqlQuery query = customerGenerator.Select(selectBuilder);

This method of having an And with only one predicate is supported for uses cases when you might only have one predicate, or might have more. This way you don't have to test for an the only one edge case. Instead And intelligently ignores itself.

SELECT [Customer].* 
FROM [Customer] 
WHERE (([Customer].[Name] = @Name_1) 
  AND ([Customer].[Email] = @Email_2) 
  AND ([Customer].[Phone] = @Phone_3))

ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
And and = new(equalName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = and
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* FROM [Customer] WHERE ([Customer].[Name] = @Name_1)

Table of Contents

---

Between Example

Parameter minimumTotal = new(100.00m, "MinimumTotal");
Parameter maximumTotal = new(500.00m, "MaximumTotal");
Column<Order> columnTotal = new(nameof(Order.Total));
Between predicate = new(columnTotal, minimumTotal, maximumTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT [Order].* 
FROM [Order] 
WHERE ([Order].[Total] BETWEEN @MinimumTotal_1 AND @MaximumTotal_2)

Table of Contents

---

Column Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Name] = @Name_1)

Table of Contents

---

ColumnValue Example

ColumnValue<Customer> columnValue = new(nameof(Customer.Name), "Hank");
SelectBuilder<Customer> selectBuilder = new()
{
    Where = columnValue
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Name] = @Name_1)

Table of Contents

---

ColumnEqualsColumn Example

Note: ColumnEqualsColumn<leftT, rightT> validates the names of the properties, and throws an error if the property isn't valid

//Note: 
ColumnEqualsColumn<Customer, Order> columnValue = new(nameof(Customer.Id), nameof(Order.CustomerId));
LeftJoin<Order> join = new(columnValue);
SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
LEFT JOIN [Order] 
  ON ([Customer].[Id] = [Order].[CustomerId])

Table of Contents

---

Contains Example

Parameter parameterEmail = new("@example.", "Email");
Column<Customer> columnEmail = new(nameof(Customer.Email));
Contains<Customer> predicate = new(columnEmail, parameterEmail);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE CONTAINS([Customer].[Email], @Email_1)

Table of Contents

---

Equal Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Name] = @Name_1)

Table of Contents

---

GreaterThan Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
GreaterThan predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT [Order].* 
FROM [Order] 
WHERE ([Order].[Total] > @Total_1)

Table of Contents

---

GreaterThanEquals Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
GreaterThanEqual predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT [Order].* 
FROM [Order] 
WHERE ([Order].[Total] >= @Total_1)

Table of Contents

---

IsNotNull Example

Column<Customer> columnName = new(nameof(Customer.Name));
IsNotNull notNull = new(columnName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = notNull
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer]
WHERE ([Customer].[Name] IS NOT NULL)

Table of Contents

---

IsNull Example

Column<Customer> columnName = new(nameof(Customer.Name));
IsNull isNull = new(columnName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = isNull
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].*
FROM [Customer] 
WHERE ([Customer].[Name] IS NULL)

Table of Contents

---

LessThan Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
LessThan predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT [Order].* 
FROM [Order] 
WHERE ([Order].[Total] < @Total_1)

Table of Contents

---

LessThanEquals Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
LessThanEqual predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT [Order].*
FROM [Order] 
WHERE ([Order].[Total] <= @Total_1)

Table of Contents

---

Like Example

Parameter parameterEmail = new("%@example.com", "Email");
Column<Customer> columnEmail = new(nameof(Customer.Email));
Like predicate = new(columnEmail, parameterEmail);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Email] LIKE @Email_1)

Table of Contents

---

NotBetween Example

Parameter minimumTotal = new(100.00m, "MinimumTotal");
Parameter maximumTotal = new(500.00m, "MaximumTotal");
Column<Order> columnTotal = new(nameof(Order.Total));
NotBetween predicate = new(columnTotal, minimumTotal, maximumTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT [Order].* 
FROM [Order]
WHERE ([Order].[Total] NOT BETWEEN @MinimumTotal_1 AND @MaximumTotal_2)

Table of Contents

---

Not Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equal = new(columnName, parameterName);
Not not = new(equal);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = not
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].*
FROM [Customer] 
WHERE (NOT ([Customer].[Name] = @Name_1))

Table of Contents

---

NotEqual Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
NotEqual predicate = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Name] <> @Name_1)

Table of Contents

---

Or Examples

        ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
        ColumnValue<Customer> equalEmail = new(nameof(Customer.Email), "Hank@example.com");
        ColumnValue<Customer> equalPhone = new(nameof(Customer.Phone), "+1(555)555-5555");
        Or or = new(equalName, equalEmail, equalPhone);

        SelectBuilder<Customer> selectBuilder = new()
        {
            Where = or
        };

        SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].*
FROM [Customer]
WHERE (([Customer].[Name] = @Name_1) 
  OR ([Customer].[Email] = @Email_2)
  OR ([Customer].[Phone] = @Phone_3))

This method of having an Or with only one predicate is supported for uses cases when you might only have one predicate, or might have more. This way you don't have to test for an the only one edge case. Instead OR intelligently ignores itself.

ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
Or or = new(equalName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = or
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Name] = @Name_1)

Table of Contents

---

Parameter Examples

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].*
FROM [Customer]
WHERE ([Customer].[Name] = @Name_1)

Parameter<Customer> parameterName = new (nameof(Customer.Name), "Hank");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT [Customer].* 
FROM [Customer] 
WHERE ([Customer].[Name] = @Name_1)

Table of Contents

---

String Operations

CharIndexOf Example

CharIndex expression = new("a", new Column<Customer>(nameof(Customer.Name)));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CHARINDEX(@Parameter_1, [Customer].[Name]) AS [Value] FROM [Customer]

Table of Contents

---

Concat Example

Coalesce coalesce = new(new Column<Customer>(nameof(Customer.Phone)), new Column<Customer>(nameof(Customer.Email)));
SelectTags selects = new(new SelectTag(coalesce, "Coalescence"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT COALESCE([Customer].[Phone], [Customer].[Email]) AS [Coalescence] FROM [Customer]

Table of Contents

---

IndexOf Example

IndexOf expression = new(new Column<Customer>(nameof(Customer.Name)), "a");

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CHARINDEX(@Parameter_1, [Customer].[Name]) AS [Value] FROM [Customer]

Table of Contents

---

Left Example

Length expression = new(new Column<Customer>(nameof(Customer.Name)));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LEFT([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

Length Example

        Length expression = new(new Column<Customer>(nameof(Customer.Name)));

        SelectBuilder<Customer> selectBuilder = new()
        {
            Selects = expression.AsSelectTag("Value")
        };

        SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LEN([Customer].[Name]) AS [Value] FROM [Customer]

Table of Contents

---

Lower Example

Lower expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LOWER([Customer].[Name]) AS [Value] FROM [Customer]

Table of Contents

---

LRTrim Example

LRTrim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LTRIM(RTRIM([Customer].[Name])) AS [Value] FROM [Customer]

Table of Contents

---

LTrim Examples

LTrim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LTRIM([Customer].[Name]) AS [Value] FROM [Customer]

Note: When a trim string is specified, it is encapsulated in a parameter to help prevent SQL injection.

LTrim expression = new(new Column<Customer>(nameof(Customer.Name)), " x");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LTRIM([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

PatIndex Example

PatIndex expression = new("%[0-9]%", new Column<Customer>(nameof(Customer.Name)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT PATINDEX(@Parameter_1, [Customer].[Name]) AS [Value] FROM [Customer]

Table of Contents

---

Repeat Example

Repeat expression = new(new Column<Customer>(nameof(Customer.Name)), 2);

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT REPLICATE([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

Replace Examples

Replace expression = new(new Column<Customer>(nameof(Customer.Name)), new Parameter("'"), new Parameter(" "));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT REPLACE([Customer].[Name], @Parameter_1, @Parameter_2) AS [Value] FROM [Customer]

Note: When a replace string is specified, it is encapsulated in a parameter to help prevent SQL injection.

Replace expression = new(new Column<Customer>(nameof(Customer.Name)), "'", " ");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT REPLACE([Customer].[Name], @Parameter_1, @Parameter_2) AS [Value] FROM [Customer]

Table of Contents

---

Right Example

Right expression = new(new Column<Customer>(nameof(Customer.Name)), 3);

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT RIGHT([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

Round Example

Note: The precision value is encapsulated in a parameter to help prevent SQL injection.

Round expression = new(new Column<Customer>(nameof(Customer.Name)), 2);
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT ROUND([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

RTrim Examples

RTrim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT RTRIM([Customer].[Name]) AS [Value] FROM [Customer]

Note: When a trim string is specified, it is encapsulated in a parameter to help prevent SQL injection.

RTrim expression = new(new Column<Customer>(nameof(Customer.Name)), " x");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT RTRIM([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

Stuff Example

Stuff expression = new(new Column<Customer>(nameof(Customer.Name)), 2, 3, "X");

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT STUFF([Customer].[Name], @Parameter_1, @Parameter_2, @Parameter_3) AS [Value] FROM [Customer]

Table of Contents

---

Substring Example

Substring expression = new(new Column<Customer>(nameof(Customer.Name)), 2, 3);

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT SUBSTRING([Customer].[Name], @Parameter_1, @Parameter_2) AS [Value] FROM [Customer]

Table of Contents

---

Trim Examples

Trim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT TRIM([Customer].[Name]) AS [Value] FROM [Customer]

Note: When a trim string is specified, it is encapsulated in a parameter to help prevent SQL injection.

RTrim expression = new(new Column<Customer>(nameof(Customer.Name)), " x");

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT TRIM([Customer].[Name], @Parameter_1) AS [Value] FROM [Customer]

Table of Contents

---

Upper Example

Upper expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT UPPER([Customer].[Name]) AS [Value] FROM [Customer]

Table of Contents

---

Attribute Examples

We use SqlGenerator<T> to produce a SqlQuery (with QueryText, CommandType, and Parameters).

You can use the [Table] attribute from System.ComponentModel.DataAnnotations.Schema to override the table name, otherwise the table name is assumed to be the same as the class. You can also specify the schema name or not.

All examples use using statements to keep the code examples focused on SQL generation.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.Base.Tests.Helpers;
using Carrigan.SqlTools.Base.Tests.TestEntities;
using Carrigan.SqlTools.SqlGenerators;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Carrigan.SqlTools.SqlServer;

// Generators
public SqlGenerator<PhoneModel> phoneGenerator = new();
public SqlGenerator<EmailModel> emailGenerator = new();
public SqlGenerator<ProcedureExec> procedureExecGenerator = new();

<a id="table-column-and-key"></a>

Table, Column and Key

[Table("Phone", Schema="schema")]
internal class PhoneModel
{
    [Key]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    [Column("Phone")]
    public string? PhoneNumber { get; set; }
}

PhoneModel phone = new()
{
    Id = 2718,
    CustomerId = 3141,
    PhoneNumber = "07700 900461"
};
SqlQuery query = phoneGenerator.UpdateById(phone);

UPDATE [schema].[Phone] 
SET [CustomerId] = @CustomerId_1, [Phone] = @Phone_2
WHERE [Id] = @Id_3;

Table of Contents

Identifier and Primary Key

[Identifier("Email", "schema")]
internal class EmailModel
{
    [PrimaryKey]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    [Identifier("Email")]
    public string? EmailAddress { get; set; }
}

EmailModel email = new()
{
    Id = 10,
    CustomerId = 313,
    EmailAddress = "Exterminate@GenericTinCanLand.gov"
};
SqlQuery query = emailGenerator.UpdateById(email);

UPDATE [schema].[Email] 
SET [CustomerId] = @CustomerId_1, [Email] = @Email_2 
WHERE [Id] = @Id_3;

Table of Contents

Procedure and Parameter

[Identifier("UpdateThing", "schema")]
internal class ProcedureExec
{
    [Parameter("SomeValue")]
    public string? ValueColumn { get; set; }
}

ProcedureExec procedureExec = new()
{
    ValueColumn = "DangIt"
};
SqlQuery query = procedureExecGenerator.Procedure(procedureExec);

[schema].[UpdateThing]

Table of Contents

---

<a id="running-queries-async--non-async"></a>

Running Queries (Async & Non-Async)

The SQL Server helpers live in Carrigan.SqlTools.Clients.SqlServer.
These examples use using statements to keep the examples focused on command execution.

using System.Collections.Generic;
using System.Data.Common;
using Carrigan.Core.Interfaces;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;
using Microsoft.Data.SqlClient;

<a id="async-executenonqueryasync--executescalarasync--executereaderasynct"></a>

Async: ExecuteNonQueryAsync / ExecuteScalarAsync / ExecuteReaderAsync<T>

// Build a query (example: SelectAll)
SqlQuery query = customerGenerator.SelectAll();

SqlConnection connection = new("<your connection string>");
DbTransaction? transaction = null;

// Implement IDecrypters (see ExampleEncryptor section)
IDecrypters decrypters = new MyDecrypters();

// Execute (async)
IEnumerable<Customer> rows =
    await CommandsAsync.ExecuteReaderAsync<Customer>(query, transaction, connection, decrypters);

// Write/update (async)
int affected =
    await CommandsAsync.ExecuteNonQueryAsync(query, transaction, connection);

// Single scalar (async)
object result =
    await CommandsAsync.ExecuteScalarAsync(query, transaction, connection);

Example Encryptor (AesGcm-based) and IDecrypters

Table of Contents

<a id="non-async-executenonquery--executescalar--executereadert"></a>

Non-Async: ExecuteNonQuery / ExecuteScalar / ExecuteReader<T>

Customer[] toDelete = [new() { Id = 7 }];
SqlQuery query = customerGenerator.DeleteById(toDelete);

SqlConnection connection = new SqlConnection("Server=.;Database=AppDb;Integrated Security=true;");
DbTransaction? transaction = null;

// IDecrypters (same interface as async)
IDecrypters decrypters = new MyDecrypters();

// Execute (sync)
int affected = Commands.ExecuteNonQuery(query, transaction, connection);
object scalar = Commands.ExecuteScalar(query, transaction, connection);
IEnumerable<Customer> customers =
    Commands.ExecuteReader<Customer>(query, transaction, connection, decrypters);

Table of Contents

---

<a id="simple-ado.net-example-with-sqlquery"></a>

Simple ADO.NET Example With SqlQuery

If you prefer raw ADO.NET, you can still use SqlQuery for the SQL text, command type, and parameters:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Carrigan.SqlTools.SqlGenerators;
using Microsoft.Data.SqlClient;

// 1) Generate the query
SqlQuery query = customerGenerator.SelectById(new Customer { Id = 42 });

// 2) Use ADO.NET directly
SqlConnection connection = new SqlConnection("Server=.;Database=AppDb;Integrated Security=true;");
connection.Open();

DbCommand command = connection.CreateCommand();
command.CommandText = query.QueryText;   // ← from SqlQuery
command.CommandType = query.CommandType; // ← from SqlQuery

// Add parameters
foreach (KeyValuePair<string, object?> p in query.Parameters)
{
    command.Parameters.Add(new SqlParameter(p.Key, p.Value ?? DBNull.Value));
}

// Execute
DbDataReader reader = command.ExecuteReader();
// ... map rows (or use invoker) ...
reader.Close();
connection.Close();

Table of Contents

---

Data Type Mappings

<a id="default-parameter-return-types"></a>

Default Parameter / Return Types

C# CLR Type	Default SqlDbType
Guid	UniqueIdentifier
string	NVarChar
char	NChar
byte[]	VarBinary
bool	Bit
byte	TinyInt
sbyte	SmallInt
short	SmallInt
int	Int
long	BigInt
float	Real
double	Float
decimal	Decimal
DateTime	DateTime2
DateOnly	Date
TimeOnly	Time
DateTimeOffset	DateTimeOffset
XDocument	XML
XmlDocument	XML

Table of Contents

Allowed Override Types

C# CLR Type	Allowed SqlTypeAttribute-derived attributes	SqlDbType values allowed by those attributes
byte[]	SqlBinaryAttribute<br>SqlVarBinaryMaxAttribute	SqlDbType.Binary<br>SqlDbType.VarBinary
char	SqlCharAttribute	SqlDbType.Char<br>SqlDbType.NChar<br>SqlDbType.NVarChar<br>SqlDbType.VarChar
string	SqlCharAttribute<br>SqlVarCharMaxAttribute	SqlDbType.Char<br>SqlDbType.NChar<br>SqlDbType.NVarChar<br>SqlDbType.VarChar
DateTime	SqlDateTime2Attribute	SqlDbType.DateTime2
DateOnly	SqlDateAttribute	SqlDbType.Date
TimeOnly	SqlTimeAttribute	SqlDbType.Time
DateTimeOffset	SqlDateTimeOffsetAttribute	SqlDbType.DateTimeOffset
double	SqlFloatAttribute	SqlDbType.Float
decimal	SqlDecimalAttribute	SqlDbType.Decimal

Table of Contents

Allowed Override Types With Warnings

C# CLR Type	Allowed SqlTypeAttribute-derived attributes	SqlDbType values allowed by those attributes
byte[]	SqlImageAttribute	SqlDbType.Image
char	SqlVarCharMaxAttribute<br>SqlTextAttribute	SqlDbType.VarChar<br>SqlDbType.NVarChar<br>SqlDbType.Text<br>SqlDbType.NText
string	SqlTextAttribute	SqlDbType.Text<br>SqlDbType.NText
DateTime	SqlDateTimeAttribute<br>SqlDateAttribute<br>SqlTimeAttribute	SqlDbType.DateTime<br>SqlDbType.SmallDateTime<br>SqlDbType.Date<br>SqlDbType.Time
DateOnly	SqlDateTimeAttribute<br>SqlDateTime2Attribute	SqlDbType.DateTime<br>SqlDbType.SmallDateTime<br>SqlDbType.DateTime2
TimeOnly	SqlDateTimeAttribute<br>SqlDateTime2Attribute	SqlDbType.DateTime<br>SqlDbType.SmallDateTime<br>SqlDbType.DateTime2
float	SqlFloatAttribute<br>SqlDecimalAttribute<br>SqlMoneyAttribute	SqlDbType.Float<br>SqlDbType.Decimal<br>SqlDbType.Money<br>SqlDbType.SmallMoney
double	SqlDecimalAttribute<br>SqlMoneyAttribute	SqlDbType.Decimal<br>SqlDbType.Money<br>SqlDbType.SmallMoney
decimal	SqlFloatAttribute<br>SqlMoneyAttribute	SqlDbType.Float<br>SqlDbType.Money<br>SqlDbType.SmallMoney

Table of Contents

---

<a id="exampleencryptor-aesgcm-based-and-idecrypters"></a>

ExampleEncryptor (AesGcm-based) and IDecrypters

Below is an example encryptor that uses an AesGcm-style API. Replace key handling with your own secure storage.

//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.

using System;
using System.Text;
using System.Security.Cryptography;
using Carrigan.Core.Interfaces;

public sealed class ExampleNonceGenerator : INonceGenerator
{
    public byte[] GenerateNonce()
    {
        byte[] nonce = new byte[12]; // 96-bit nonce
        RandomNumberGenerator.Fill(nonce);
        return nonce;
    }
}

public sealed class ExampleEncryptor : IEncryption
{
    private readonly byte[] _key;
    private const int TagSize = 16;

    public int? Version => 1;

    public byte[] KeyBytes => _key;

    public ExampleEncryptor(byte[] key)
    {
        if (key == null || key.Length != 32) throw new ArgumentException("Key must be 32 bytes.");
        _key = key;
    }

    public string? Encrypt(string? plainText)
    {
        if (plainText == null) return null;

        byte[] pt = Encoding.UTF8.GetBytes(plainText);
        byte[] ct = new byte[pt.Length];
        byte[] nonce = new byte[12];
        byte[] tag = new byte[TagSize];
        RandomNumberGenerator.Fill(nonce);

        using (AesGcm gcm = new AesGcm(_key, TagSize))
        {
            gcm.Encrypt(nonce, pt, ct, tag);
        }

        byte[] combined = new byte[nonce.Length + ct.Length + tag.Length];
        Buffer.BlockCopy(nonce, 0, combined, 0, nonce.Length);
        Buffer.BlockCopy(ct, 0, combined, nonce.Length, ct.Length);
        Buffer.BlockCopy(tag, 0, combined, nonce.Length + ct.Length, tag.Length);

        return Convert.ToBase64String(combined);
    }

    public string? Decrypt(string? cipherText)
    {
        if (cipherText == null) return null;

        byte[] combined = Convert.FromBase64String(cipherText);
        byte[] nonce = new byte[12];
        byte[] tag = new byte[TagSize];

        int cipherLen = combined.Length - nonce.Length - tag.Length;
        byte[] ct = new byte[cipherLen];

        Buffer.BlockCopy(combined, 0, nonce, 0, nonce.Length);
        Buffer.BlockCopy(combined, nonce.Length, ct, 0, ct.Length);
        Buffer.BlockCopy(combined, nonce.Length + ct.Length, tag, 0, tag.Length);

        byte[] pt = new byte[ct.Length];

        using (AesGcm gcm = new AesGcm(_key, TagSize))
        {
            gcm.Decrypt(nonce, ct, tag, pt);
        }

        return Encoding.UTF8.GetString(pt);
    }
}

// Minimal IDecrypters implementation mapping versions to encryptors.

//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.


public sealed class MyDecrypters : IDecrypters
{
    private readonly System.Collections.Generic.Dictionary<int, IEncryption> _map =
        new System.Collections.Generic.Dictionary<int, IEncryption>()
        {
            { 1, new ExampleEncryptor(RandomNumberGenerator.GetBytes(32)) }
        };

    public System.Collections.Generic.IEnumerable<int> Keys => _map.Keys;

    public IEncryption? Decryptor(int version)
    {
        return _map.TryGetValue(version, out IEncryption enc) ? enc : null;
    }
}


//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.

Plug MyDecrypters into ExecuteReaderAsync<T> / ExecuteReader<T> to transparently decrypt properties annotated in your models.

Table of Contents

---

License

Carrigan.SqlTools.SqlServer
Copyright © 2025-2026 Carrigan Software Solutions LLC

Licensed under the Apache License, Version 2.0]: http://www.apache.org/licenses/LICENSE-2.0

Table of Contents

---

Disclaimers

SQL Server is a trademark of Microsoft Corporation. This project is not affiliated with or endorsed by Microsoft.

This library may generate or execute SQL. Review generated SQL before use in production systems. Always test against non-production databases first.

Use caution with schema, migration, and data-modifying operations. The authors are not responsible for data loss, downtime, or unintended database changes.