Fastql 4.0.0

<div align="center">

<img src="https://github.com/theilgaz/fastql/blob/main/resource/fastql-logo-resized.png?raw=true" width="250"/>

Fastql

SQL query builder for .NET entity classes

Stop writing SQL strings by hand. Define your entity once, generate every query you need. Works with Dapper, ADO.NET, or any micro-ORM.

</div>

Jumpstart

dotnet add package Fastql

using Fastql;

[Table("Users", "dbo")]
public class User
{
    [PK]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

var sql = new FastqlBuilder<User>();

sql.InsertQuery();                        // INSERT INTO [dbo].[Users](Name, Email) VALUES(@Name, @Email)
sql.SelectQuery("Id = @Id");             // SELECT Id as Id, Name as Name, Email as Email FROM [dbo].[Users] WHERE Id = @Id;
sql.UpdateQuery(user, "Id = @Id");       // UPDATE [dbo].[Users] SET Name = @Name, Email = @Email WHERE Id = @Id;
sql.DeleteQuery("Id = @Id");             // DELETE FROM [dbo].[Users] WHERE Id = @Id;
sql.InsertQuery(returnIdentity: true);   // ...VALUES(@Name, @Email) ; SELECT SCOPE_IDENTITY();
sql.UpsertQuery();                       // MERGE INTO [dbo].[Users] AS target USING ...

That's it. Fastql reads your class, respects your attributes, and gives you the SQL.

Table of Contents

• Jumpstart
• How to Start
  • Step 1: Install
  • Step 2: Pick a Database
  • Step 3: Decorate Your Entity
  • Step 4: Generate Queries
  • Step 5: Wire to Dapper
• Two APIs, Same Output
• Attribute Reference
• Database Support
• Features
  • Fluent WHERE Builder
  • Validation
  • Bulk Operations
  • UPSERT / MERGE
  • PostgreSQL Type Casts
  • Custom Exceptions
• API Reference
• Migrating from v2 or v3
• License

How to Start

Step 1: Install

dotnet add package Fastql

<PackageReference Include="Fastql" Version="4.0.0" />

Step 2: Pick a Database

// SQL Server is the default, no argument needed
var sql = new FastqlBuilder<User>();

// For other databases, pass the type
var sql = new FastqlBuilder<User>(DatabaseType.Postgres);
var sql = new FastqlBuilder<User>(DatabaseType.MySql);
var sql = new FastqlBuilder<User>(DatabaseType.SQLite);
var sql = new FastqlBuilder<User>(DatabaseType.Oracle);

Step 3: Decorate Your Entity

At minimum, you need [Table] and [PK]. Everything else is optional.

using Fastql;

[Table("Products", "inventory")]
public class Product
{
    [PK]
    public int Id { get; set; }

    [Required]
    [MaxLength(200)]
    public string Name { get; set; }

    [Column("unit_price")]          // maps to a different column name
    public decimal Price { get; set; }

    [IsNotUpdatable]                // included in INSERT, excluded from UPDATE
    public DateTime CreatedAt { get; set; }

    [IsNotInsertable]               // excluded from INSERT, included in UPDATE
    public int StockCount { get; set; }

    [SelectOnly]                    // read-only, excluded from INSERT and UPDATE
    public decimal Discount { get; set; }

    [Ignore]                        // completely invisible to Fastql
    public Category? Category { get; set; }
}

Step 4: Generate Queries

var sql = new FastqlBuilder<Product>();

// INSERT (PK excluded automatically)
sql.InsertQuery();
// INSERT INTO [inventory].[Products](Name, unit_price, CreatedAt) VALUES(@Name, @Price, @CreatedAt)

// INSERT and get the new ID back
sql.InsertQuery(returnIdentity: true);
// ...VALUES(@Name, @Price, @CreatedAt) ; SELECT SCOPE_IDENTITY();

// UPDATE (PK, CreatedAt, SelectOnly excluded automatically)
var product = new Product { Id = 1, Name = "Widget", Price = 9.99m };
sql.UpdateQuery(product, "Id = @Id");
// UPDATE [inventory].[Products] SET Name = @Name, unit_price = @Price, StockCount = @StockCount WHERE Id = @Id;

// SELECT (Ignore excluded automatically)
sql.SelectQuery("Id = @Id");
// SELECT Id as Id, Name as Name, unit_price as Price, CreatedAt as CreatedAt, ... FROM [inventory].[Products] WHERE Id = @Id;

// DELETE
sql.DeleteQuery("Id = @Id");
// DELETE FROM [inventory].[Products] WHERE Id = @Id;

Step 5: Wire to Dapper

Fastql generates Dapper-compatible @Parameter syntax. Plug it straight in:

public class ProductRepository
{
    private readonly IDbConnection _db;
    private readonly FastqlBuilder<Product> _sql = new();

    public ProductRepository(IDbConnection db) => _db = db;

    public async Task<int> CreateAsync(Product p)
    {
        var query = _sql.InsertQuery(returnIdentity: true);
        return await _db.ExecuteScalarAsync<int>(query, p);
    }

    public async Task<Product?> GetAsync(int id)
    {
        var query = _sql.SelectQuery("Id = @Id");
        return await _db.QueryFirstOrDefaultAsync<Product>(query, new { Id = id });
    }

    public async Task UpdateAsync(Product p)
    {
        var query = _sql.UpdateQuery(p, "Id = @Id");
        await _db.ExecuteAsync(query, p);
    }

    public async Task DeleteAsync(int id)
    {
        var query = _sql.DeleteQuery("Id = @Id");
        await _db.ExecuteAsync(query, new { Id = id });
    }
}

Two APIs, Same Output

Fastql gives you two ways to generate queries. Pick whichever fits your style:

FastqlBuilder is instance-based, great for DI and repositories:

var sql = new FastqlBuilder<Product>(DatabaseType.Postgres);
sql.InsertQuery();
sql.SelectQuery("Id = @Id");

FastqlHelper is static, great for quick one-liners:

FastqlHelper<Product>.DatabaseType = DatabaseType.Postgres;
FastqlHelper<Product>.InsertQuery();
FastqlHelper<Product>.SelectQuery("Id = @Id");

Both produce identical SQL. Every method on FastqlBuilder has a matching static method on FastqlHelper.

Attribute Reference

Table & Key

OutputName options:

Column Control

Column Mapping

Validation

Database Support

Fastql generates the correct syntax per database automatically. Just pass DatabaseType to the constructor.

Features

Fluent WHERE Builder

Build WHERE clauses without string concatenation:

using Fastql.Where;

// Simple
string w = Where.Column("Id").Equals("@Id");
// Id = @Id

// AND / OR chaining
string w = Where.Column("Active").Equals("@Active")
    .And.Column("Role").Equals("@Role")
    .Build();
// Active = @Active AND Role = @Role

string w = Where.Column("Status").Equals("@A")
    .Or.Column("Status").Equals("@B")
    .Build();
// Status = @A OR Status = @B

// Use directly with queries (implicit string conversion)
var sql = new FastqlBuilder<User>();
sql.SelectQuery(Where.Column("Id").Equals("@Id"));
sql.DeleteQuery(Where.Column("Id").Equals("@Id").And.Column("Active").Equals("@Active"));

All operators: Equals, NotEquals, GreaterThan, LessThan, GreaterThanOrEqual, LessThanOrEqual, Like, In, IsNull, IsNotNull, Between

Validation

Enforce [Required] and [MaxLength] before hitting the database:

using Fastql.Validation;

var product = new Product { Name = null! };

// Via builder
var result = sql.Validate(product);

// Or directly
var result = EntityValidator.Validate(product);

if (!result.IsValid)
{
    foreach (var err in result.Errors)
        Console.WriteLine($"{err.PropertyName}: {err.Message}");
    // Name: Name is required.
}

// Validate a single property
var result = EntityValidator.ValidateProperty(product, "Name");

Bulk Operations

Insert or update many entities in one query:

var users = new List<User>
{
    new() { Name = "Alice", Email = "alice@example.com" },
    new() { Name = "Bob",   Email = "bob@example.com" }
};

// Multi-row INSERT with indexed parameters
sql.BulkInsertQuery(users);
// INSERT INTO [dbo].[Users](Name, Email) VALUES(@Name_0, @Email_0), (@Name_1, @Email_1);

// Batch UPDATE (one statement per entity)
sql.BulkUpdateQuery(users);
// UPDATE [dbo].[Users] SET Name = @Name_0, Email = @Email_0 WHERE Id = @Id_0;
// UPDATE [dbo].[Users] SET Name = @Name_1, Email = @Email_1 WHERE Id = @Id_1;

Oracle bulk insert uses INSERT ALL INTO ... SELECT 1 FROM DUAL automatically.

UPSERT / MERGE

Insert-or-update in a single query. Fastql picks the right syntax for your database:

// PostgreSQL
var sql = new FastqlBuilder<User>(DatabaseType.Postgres);
sql.UpsertQuery();
// INSERT INTO public.Users(Name, Email) VALUES(@Name, @Email)
// ON CONFLICT (Id) DO UPDATE SET Name = @Name, Email = @Email;

// SQL Server
var sql = new FastqlBuilder<User>();
sql.UpsertQuery();
// MERGE INTO [dbo].[Users] AS target
// USING (SELECT @Id AS Id) AS source ON target.Id = source.Id
// WHEN MATCHED THEN UPDATE SET target.Name = @Name, target.Email = @Email
// WHEN NOT MATCHED THEN INSERT (Name, Email) VALUES(@Name, @Email);

// MySQL → ON DUPLICATE KEY UPDATE
// SQLite → INSERT OR REPLACE INTO
// Oracle → MERGE INTO ... USING (SELECT ... FROM DUAL)

PostgreSQL Type Casts

When using PostgreSQL, add type casts to get proper data handling:

[Table("events", "public", OutputName.TableAndSchema)]
public class Event
{
    [PK] public int Id { get; set; }

    public string Name { get; set; }

    [Field("event_data", FieldType.Jsonb)]       // → @EventData::jsonb
    public string EventData { get; set; }

    [Field("created_at", FieldType.Timestamp)]    // → @CreatedAt::timestamp
    public DateTime CreatedAt { get; set; }
}

var sql = new FastqlBuilder<Event>(DatabaseType.Postgres);
sql.InsertQuery(returnIdentity: true);
// INSERT INTO public.events(Name, event_data, created_at)
// VALUES(@Name, @EventData::jsonb, @CreatedAt::timestamp) RETURNING Id;

Available casts: FieldType.Jsonb (::jsonb), FieldType.Timestamp (::timestamp), FieldType.Date (::date), FieldType.Time (::time)

Custom Exceptions

All Fastql exceptions inherit from FastqlException so you can catch them in one place:

using Fastql.Exceptions;

try
{
    sql.InsertQuery();
}
catch (MissingParametersException)   { /* no insertable properties */ }
catch (MissingWhereClauseException)  { /* WHERE required but missing */ }
catch (DuplicateFieldException ex)   { /* field declared twice: ex.FieldName */ }
catch (FastqlException)              { /* catch-all for any Fastql error */ }

API Reference

FastqlBuilder<TEntity>

var sql = new FastqlBuilder<TEntity>();                    // SQL Server
var sql = new FastqlBuilder<TEntity>(DatabaseType.Postgres); // other DB

FastqlHelper<TEntity>

Same methods as above, but static:

FastqlHelper<User>.DatabaseType = DatabaseType.Postgres;
FastqlHelper<User>.InsertQuery();
FastqlHelper<User>.TableName();   // public accessor

Migrating from v2 or v3

From v2 to v4

v2 used raw reflection on every call. v4 caches everything and adds new features.

What you need to change:

1. Add new() constraint. Your entities must have a parameterless constructor. This was always required at runtime but now it's enforced at compile time.

   // v2: worked at runtime, no constraint
   // v4: compile-time constraint
   public class MyEntity { }  // ✓ parameterless constructor exists
   

2. Nullable reference types. Fastql now has <Nullable>enable</Nullable>. Mark nullable properties with ?:

   [Ignore]
   public Order? Order { get; set; }  // was: public Order Order { get; set; }
   

3. Catch new exceptions. v2 threw System.Exception and DuplicateNameException. v4 throws typed exceptions from Fastql.Exceptions:

   // v2
   catch (Exception ex) when (ex.Message.Contains("parameters"))
   
   // v4
   catch (MissingParametersException) { }
   catch (MissingWhereClauseException) { }
   catch (DuplicateFieldException) { }
   

4. SetDatabaseType is obsolete. Use the property instead:

   // v2
   FastqlHelper<User>.SetDatabaseType(DatabaseType.Postgres);
   
   // v4
   FastqlHelper<User>.DatabaseType = DatabaseType.Postgres;
   

Everything else is additive. Your existing InsertQuery, UpdateQuery, SelectQuery, and DeleteQuery calls work the same way.

From v3 to v4

v3 to v4 is mostly painless. Here's what changed:

Exception types (breaking if you catch exceptions):

// v3: System.Exception, DuplicateNameException
// v4: MissingParametersException, MissingWhereClauseException, DuplicateFieldException

Postgres RETURNING now uses your actual PK name (breaking if you parsed the SQL):

// v3: RETURNING ID    (hardcoded uppercase)
// v4: RETURNING Id    (actual column name from your entity)

Oracle identity return now generates SQL (breaking if you relied on no-op):

// v3: (no identity clause for Oracle)
// v4: RETURNING Id INTO :Id

SelectQuery(columns, where, top) is now database-aware:

// v3: always SELECT TOP(n), wrong for Postgres/MySQL/SQLite/Oracle
// v4: TOP(n) for SQL Server, LIMIT n for Postgres/MySQL/SQLite, FETCH FIRST for Oracle

FastqlHelper.SelectQuery(where) now uses QueryGenerator:

// v3: SELECT * FROM [dbo].[Users] WHERE ...  (raw string, no column aliases)
// v4: SELECT Id as Id, Name as Name, ... FROM [dbo].[Users] WHERE ...  (matches FastqlBuilder)

Minor:

• SetDatabaseType() / GetDatabaseType() → still work, marked [Obsolete]. Use DatabaseType property.
• FastqlHelper.TableName() is now public.
• QueryBuilder class deleted (was unused).
• AddCondition method removed from FastQueryBuilder (was dead code).

New in v4 (no migration needed, just start using them):

• Validate(entity) to enforce [Required] and [MaxLength]
• BulkInsertQuery(entities) / BulkUpdateQuery(entities) for batch operations
• UpsertQuery() for MERGE / ON CONFLICT per database
• Where.Column("x").Equals("@x") fluent WHERE builder
• Custom exceptions under Fastql.Exceptions

Repository Pattern with Unit of Work

For a complete example using Fastql with the Repository Pattern and Unit of Work, check out the sample project.

License

MIT. See LICENSE.

Contributing

Contributions welcome. Open a PR or file an issue.

Author

Abdullah Ilgaz | GitHub