.NET 8 Ready

LambdifySQL

Fluent SQL Query Builder

A powerful, fluent C# library that converts lambda expressions to SQL queries with support for multiple database dialects and advanced SQL features.

Get Started View Examples

Features

Fluent API

Intuitive method chaining for building SQL queries

Type-Safe

Leverage C# type system for compile-time query validation

Multiple SQL Dialects

Support for SQL Server, MySQL, PostgreSQL, and SQLite

Advanced Query Support

CTEs, Window Functions, Joins, Aggregates, Subqueries, and more

Parameter Safety

Automatic parameterization prevents SQL injection

Rich Expression Support

Complex lambda expressions with method calls

Installation & Setup

Package Manager Console

Package Manager

Install-Package LambdifySQL

.NET CLI

CLI

dotnet add package LambdifySQL

PackageReference

XML

<PackageReference Include="LambdifySQL" Version="1.0.0" />

Prerequisites

.NET 8.0 or higher
C# 12.0 language features
Entity Framework Core (optional, for database connectivity)
System.Linq.Expressions support

Required Using Statements

using LambdifySQL;
using LambdifySQL.Core;
using LambdifySQL.Builders;
using LambdifySQL.Advanced;
using LambdifySQL.Resolver;

Quick Start Guide

Define Your Entity

Create your entity class with LambdifySQL attributes for optimal SQL generation:

C# Entity Definition

[TableName("Products", "p")]
public class Product
{
    [Pk("ProductId")]
    public int Id { get; set; }

    [Column("ProductName")]
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }

    [Column("Quantity")]
    public int Qty { get; set; }

    [Column("UnitPrice")]
    public decimal Price { get; set; }

    [Relation(typeof(Category))]
    public int CategoryId { get; set; }

    [DefaultValue("GETDATE()")]
    public DateTime CreatedAt { get; set; }

    [DefaultValue(true)]
    public bool IsActive { get; set; }
}

Build Your First Query

Use the fluent API to construct type-safe SQL queries:

C# Query Building

// Create a SELECT query with multiple conditions
var query = SqlQuery.Select<Product>()
    .Where(p => p.Price > 100 && p.IsActive)
    .OrWhere(p => p.Qty < 5)
    .OrderBy(p => p.Name)
    .Take(10);

// Get the generated SQL and parameters
var sql = query.GetSql();
var parameters = query.GetParameters();

// Or use the Build() extension method
var (sqlText, queryParams) = query.Build();

Execute with Your Data Access Layer

Integrate with Entity Framework, Dapper, or any ADO.NET provider:

C# Data Access Integration

// With Entity Framework Core
var (sql, parameters) = query.Build();
var products = await context.Products
    .FromSqlRaw(sql, parameters.Values.ToArray())
    .ToListAsync();

// With Dapper
var products = await connection.QueryAsync<Product>(sql, parameters);

// With ADO.NET
using var command = new SqlCommand(sql, connection);
foreach (var param in parameters)
{
    command.Parameters.AddWithValue($"@{param.Key}", param.Value);
}
var reader = await command.ExecuteReaderAsync();

Generated SQL Output

SELECT TOP (10) p.*
FROM [Products] AS p
WHERE ((p.[UnitPrice] > @p0) AND p.[IsActive]) OR ((p.[Quantity] < @p1))
ORDER BY p.[ProductName] ASC
Parameters: @p0=100, @p1=5

Core API Overview

SqlQuery Entry Points

SqlQuery.Select<T>() - Basic SELECT queries
SqlQuery.SelectWithJoins<T>() - JOIN operations
SqlQuery.Update<T>() - UPDATE statements
SqlQuery.Insert<T>() - INSERT operations
SqlQuery.Delete<T>() - DELETE statements
SqlQuery.Advanced<T>() - CTEs and advanced features

Specialized Builders

SqlQuery.Aggregate<T>() - Aggregation queries
SqlQuery.WindowFunction<T>() - Window functions
SqlQuery.BulkInsert<T>() - Bulk operations
SqlQuery.Raw(sql, params) - Raw SQL
SqlQuery.StoredProcedure(name, params) - Stored procedures

Comprehensive Examples

Real examples from the README with actual SQL outputs. All examples are dynamically loaded from JSON.

Loading examples...

SELECT Operations

Basic SELECT with Conditions

// Simple SELECT with WHERE conditions

var activeProducts = SqlQuery.Select()
    .Where(p => p.IsActive == true)
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .Take(10);

// SELECT with complex conditions
var searchResults = SqlQuery.Select()
      .Where(p => p.Name.Contains("Laptop"))
      .WhereIn(p => p.CategoryId, new[] { 1, 2, 3 })
      .Where(p => p.CreatedAt >= DateTime.Now.AddMonths(-6))
      .OrderBy(p => p.Price);

// SELECT specific columns
var productSummary = SqlQuery.Select()
        .Column(p => p.Id)
        .Column(p => p.Name)
        .Column(p => p.Price)
        .Where(p => p.IsActive == true);

Generated SQL Output:

SELECT p.ProductId, p.ProductName, p.UnitPrice, p.Quantity, p.CategoryId, p.ExtraId, p.CreatedDate, p.ModifiedDate, p.IsActive, p.SKU, p.Weight 
SELECT TOP (10) product.*
FROM [Product] AS product
WHERE (product.[IsActive] = @p0) AND (product.[Price] > @p1)
ORDER BY product.[Name] ASC
_____________________
Parameters: @p0=True, @p1=100

SELECT product.*
FROM [Product] AS product
WHERE product.[Name] LIKE @p0 AND product.[CategoryId] IN (@p1, @p2, @p3) AND (product.[CreatedAt] >= DATEADD(month, @p4, @p5))
ORDER BY product.[Price] ASC
_________________________
Parameters: @p0=%Laptop%, @p1=1, @p2=2, @p3=3, @p4=-6, @p5=22/08/2025 23:17:11

SELECT product.[Id], product.[Name], product.[Price]
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
__________________________
Parameters: @p0=True

SELECT with JOINs



// Multiple JOINs
var fullProductInfo = SqlQuery.SelectWithJoins<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .LeftJoin<ProductExtraDetails>((p, d) => p.Id == d.ProductId)
    .Where(p => p.IsActive == true)
    .OrderBy(p => p.Name)
    .Build();

Generated SQL Output:


SELECT product.*
FROM [Product] AS product
INNER JOIN [Category] AS category ON product.Id = category.Id
LEFT JOIN [ProductExtraDetails] AS productextradetails ON product.Id = productextradetails.Id
WHERE (product.[IsActive] = @p0)
ORDER BY product.[Name] ASC
---------------------
Parameters: @p0=True

INSERT Operations

Single and Bulk INSERT

// Single INSERT
var newProduct = new Product 
{ 
    Name = "Gaming Laptop", 
    Price = 1500.00m, 
    CategoryId = 1, 
    IsActive = true 
};

var insertQuery = SqlQuery.Insert<Product>()
    .Values(newProduct)
    .Build();

// Bulk INSERT for multiple records
var products = new List<Product>
{
    new Product { Name = "Keyboard", Price = 120.00m, CategoryId = 2 },
    new Product { Name = "Monitor", Price = 350.00m, CategoryId = 3 },
    new Product { Name = "Headset", Price = 89.99m, CategoryId = 2 }
};

var bulkInsert = SqlQuery.BulkInsert<Product>()
    .Values(products)
    .Build();

Generated SQL Output:


INSERT INTO [Product]
([Name], [Qty], [Price], [CategoryId], [ExtraId], [CreatedAt], [IsActive])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
_____________________________
Parameters: @p0=Gaming Laptop, @p1=0, @p2=1500.00, @p3=1, @p4=0, @p5=GETDATE(), @p6=True

INSERT INTO [Product]
([Name], [Qty], [Price], [CategoryId], [ExtraId], [CreatedAt], [IsActive])
VALUES
(@p0, @p1, @p2, @p3, @p4, @p5, @p6),
(@p7, @p8, @p9, @p10, @p11, @p12, @p13),
(@p14, @p15, @p16, @p17, @p18, @p19, @p20)
__________________________
Parameters: @p0=Keyboard, @p1=0, @p2=120.00, @p3=2, @p4=0, @p5=22/08/2025 23:55:48, @p6=False,
 @p7=Monitor, @p8=0, @p9=350.00, @p10=3, @p11=0, @p12=22/08/2025 23:55:48, @p13=False, @p14=Headset, @p15=0, @p16=89.99, @p17=2, @p18=0, @p19=22/08/2025 23:55:48, @p20=False

UPDATE Operations

UPDATE with Conditions

// Simple UPDATE
var updateQuery = SqlQuery.Update<Product>()
    .Set(p => p.Price, 99.99m)
    .Set(p => p.ModifiedAt, DateTime.Now)
    .Where(p => p.Id == 1)
    .Build();

// Conditional UPDATE with complex WHERE
var bulkPriceUpdate = SqlQuery.Update<Product>()
    .Set(p => p.Price, p => p.Price * 1.1m) // 10% price increase
    .Where(p => p.CategoryId == 1)
    .Where(p => p.IsActive == true)
    .Where(p => p.CreatedAt < DateTime.Now.AddYears(-1))
    .Build();

Generated SQL Output:

UPDATE Products 
UPDATE product
SET product.[Price] = @p0, product.[CreatedAt] = @p1
FROM [Product] AS product
WHERE (product.[Id] = @p2)
------------------------
Parameters: @p0=99.99, @p1=23/08/2025 00:12:18, @p2=1


UPDATE product
SET product.[Price] = (product.[Price] * @p0)
FROM [Product] AS product
WHERE (product.[CategoryId] = @p1) AND (product.[IsActive] = @p2) AND (product.[CreatedAt] < DATEADD(year, @p3, @p4))
------------------------
Parameters: @p0=1.1, @p1=1, @p2=True, @p3=-1, @p4=23/08/2025 00:07:34

DELETE Operations

DELETE with Safety Conditions

// Simple DELETE
var deleteQuery = SqlQuery.Delete<Product>()
    .Where(p => p.Id == 1)
    .Build();

// Soft DELETE (mark as inactive)
var softDelete = SqlQuery.Update<Product>()
    .Set(p => p.IsActive, false)
    .Set(p => p.ModifiedAt, DateTime.Now)
    .Where(p => p.Id == 1)
    .Build();

// Bulk DELETE with conditions
var bulkDelete = SqlQuery.Delete<Product>()
    .Where(p => p.IsActive == false)
    .Where(p => p.ModifiedAt < DateTime.Now.AddYears(-2))
    .Build();

Generated SQL Output:

DELETE FROM Products 
WHERE ProductId = 1

Advanced SQL Operations

Real examples from sample-output.txt including Aggregates, Window Functions, Boolean Logic, CTEs, JOINs, and Custom SQL.

8 Aggregate Queries with GROUP BY

C# Code

var queryAggregate = SqlQuery.Aggregate<Product>()
    .GroupBy(p => p.CategoryId)
    .Count(alias: "ProductCount")
    .Sum(p => p.Price, "TotalPrice")
    .Average(p => p.Price, "AvgPrice")
    .Where(p => p.IsActive);

SQL Output

SELECT product.[CategoryId], COUNT(*) AS [ProductCount], SUM(product.[Price]) AS [TotalPrice], AVG(product.[Price]) AS [AvgPrice]
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
GROUP BY product.[CategoryId]
Parameters: @p0=True

9 Window Functions

C# Code

var queryWindowFunction = SqlQuery.WindowFunction<Product>()
    .Select(p => p.Name)
    .Select(p => p.Price)
    .RowNumber(p => p.CategoryId, p => p.Price, "RowNum")
    .Rank(p => p.CategoryId, p => p.Price, "PriceRank")
    .Where(p => !p.IsActive);

SQL Output

SELECT product.[Name], product.[Price], ROW_NUMBER() OVER (PARTITION BY product.[CategoryId] ORDER BY product.[Price]) AS [RowNum], RANK() OVER (PARTITION BY product.[CategoryId] ORDER BY product.[Price]) AS [PriceRank]
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
Parameters: @p0=False

10 Boolean WHERE Clauses

C# Code

// Simple boolean property
var activeBoolQuery = SqlQuery.Select<Product>()
    .Where(p => p.IsActive);

// Negated boolean property  
var inactiveBoolQuery = SqlQuery.Select<Product>()
    .Where(p => !p.IsActive);

SQL Output

-- Simple boolean (p.IsActive):
SELECT product.*
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
Parameters: @p0=True

-- Negated boolean (!p.IsActive):
SELECT product.*
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
Parameters: @p0=False

11 Advanced CTE Example

C# Code

var cteQuery = SqlQuery.Select<Product>()
    .Where(p => p.Qty >= 1)
    .Top(10);

var mainQuery = SqlQuery.Select<Product>()
    .Where(p => p.CategoryId == 2);

var advancedQuery = SqlQuery.Advanced<Product>()
    .WithCTE("TopProducts", cteQuery)
    .Query(mainQuery);

SQL Output

WITH TopProducts AS (SELECT TOP (10) product.*
FROM [Product] AS product
WHERE (product.[Qty] >= @p0))
SELECT product.*
FROM [Product] AS product
WHERE (product.[CategoryId] = @p1)
Parameters: @p0=1, @p1=2

12 INNER JOIN - Products with Categories

C# Code

var innerJoinQuery = SqlQuery.SelectWithJoins<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where<Product>(p => p.IsActive)
    .OrderBy(p => p.Name);

SQL Output

SELECT product.*
FROM [Product] AS product
INNER JOIN [Category] AS category ON product.Id = category.Id
WHERE (product.[IsActive] = @p0)
ORDER BY product.[Name] ASC
Parameters: @p0=True

13 WHERE IN with Subquery

C# Code

var expensiveCategoriesSubquery = SqlQuery.Aggregate<Product>()
    .Where(p => p.Price > 500)
    .GroupBy(p => p.CategoryId);

var productsInExpensiveCategoriesQuery = SqlQuery.Select<Product>()
    .WhereIn<int, Product>(p => p.CategoryId, expensiveCategoriesSubquery, sub => sub.CategoryId)
    .OrderBy(p => p.Name);

SQL Output

SELECT product.*
FROM [Product] AS product
WHERE product.[CategoryId] IN (SELECT product.[CategoryId] FROM (SELECT product.[CategoryId]
FROM [Product] AS product
WHERE (product.[Price] > @p0)
GROUP BY product.[CategoryId]) subq)
ORDER BY product.[Name] ASC
Parameters: @p0=500

14 Window Functions with JOINs

C# Code

var windowWithJoinQuery = SqlQuery.SelectWithJoins<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .SelectWindow("ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC)", "PriceRank")
    .SelectWindow("AVG(p.Price) OVER (PARTITION BY p.CategoryId)", "AvgCategoryPrice")
    .Where<Product>(p => p.IsActive)
    .OrderBy(p => p.CategoryId)
    .ThenBy(p => p.Price);

SQL Output

SELECT ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS PriceRank, AVG(p.Price) OVER (PARTITION BY p.CategoryId) AS AvgCategoryPrice
FROM [Product] AS product
INNER JOIN [Category] AS category ON product.Id = category.Id
WHERE (product.[IsActive] = @p0)
ORDER BY product.[CategoryId] ASC, product.[Price] ASC
Parameters: @p0=True

15 Ultimate Complex Query with CTEs and JOINs

C# Code

// CTE for high-value products
var highValueProductsCte = SqlQuery.Select<Product>()
    .Where(p => p.Price > 300 && p.IsActive);

// CTE for category statistics
var categoryStatsCte = SqlQuery.Aggregate<Product>()
    .GroupBy(p => p.CategoryId)
    .Count(null, "ProductCount")
    .Average(p => p.Price, "AvgPrice")
    .Sum(p => p.Qty, "TotalQty");

// Main query with multiple joins and window functions
var complexMainQuery = SqlQuery.SelectWithJoins<Product>()
    .InnerJoin<Category>("cat", (p, c) => p.CategoryId == c.Id)
    .LeftJoin<ProductExtraDetails>("details", (p, d) => p.ExtraId == d.Id)
    .SelectWindow("RANK() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC)", "PriceRank")
    .SelectWindow("COUNT(*) OVER (PARTITION BY p.CategoryId)", "CategoryProductCount")
    .Where<Product>(p => p.IsActive)
    .Where<Category>("cat", c => c.IsActive)
    .Having<Product>(p => p.Price > 100)
    .OrderBy(p => p.CategoryId)
    .ThenBy(p => p.Price);

var ultimateComplexQuery = SqlQuery.Advanced<Product>()
    .WithCTE("HighValueProducts", highValueProductsCte)
    .WithCTE("CategoryStats", categoryStatsCte)
    .Query(complexMainQuery);

SQL Output

WITH HighValueProducts AS (SELECT product.*
FROM [Product] AS product
WHERE ((product.[Price] > @p0) AND product.[IsActive])), CategoryStats AS (SELECT product.[CategoryId], COUNT(*) AS [ProductCount], AVG(product.[Price]) AS [AvgPrice], SUM(product.[Qty]) AS [TotalQty]
FROM [Product] AS product
GROUP BY product.[CategoryId])
SELECT RANK() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS PriceRank, COUNT(*) OVER (PARTITION BY p.CategoryId) AS CategoryProductCount
FROM [Product] AS product
INNER JOIN [Category] AS cat ON product.Id = cat.Id
LEFT JOIN [ProductExtraDetails] AS details ON product.Id = details.Id
WHERE (product.[IsActive] = @p1) AND (category.[IsActive] = @p1)
HAVING (product.[Price] > @p2)
ORDER BY product.[CategoryId] ASC, product.[Price] ASC
Parameters: @p0=300, @p1=True, @p2=100

16 Custom Raw SQL with Advanced Parameters

C# Code

var customRawQuery = SqlQuery.Raw(@"
    WITH RecentProducts AS (
        SELECT p.*, c.Name as CategoryName
        FROM Product p
        INNER JOIN Category c ON p.CategoryId = c.Id
        WHERE p.CreatedAt >= @startDate AND p.IsActive = @isActive
    ),
    CategoryTotals AS (
        SELECT CategoryId, COUNT(*) as ProductCount, AVG(Price) as AvgPrice
        FROM Product
        WHERE IsActive = @isActive
        GROUP BY CategoryId
    )
    SELECT rp.*, ct.ProductCount, ct.AvgPrice,
           ROW_NUMBER() OVER (PARTITION BY rp.CategoryId ORDER BY rp.Price DESC) as PriceRank
    FROM RecentProducts rp
    INNER JOIN CategoryTotals ct ON rp.CategoryId = ct.CategoryId
    WHERE rp.Price >= @minPrice
    ORDER BY rp.CategoryId, rp.Price DESC",
    new 
    { 
        startDate = DateTime.Now.AddMonths(-6),
        isActive = true,
        minPrice = 50.00m
    });

SQL Output

WITH RecentProducts AS (
    SELECT p.*, c.Name as CategoryName
    FROM Product p
    INNER JOIN Category c ON p.CategoryId = c.Id
    WHERE p.CreatedAt >= @startDate AND p.IsActive = @isActive
),
CategoryTotals AS (
    SELECT CategoryId, COUNT(*) as ProductCount, AVG(Price) as AvgPrice
    FROM Product
    WHERE IsActive = @isActive
    GROUP BY CategoryId
)
SELECT rp.*, ct.ProductCount, ct.AvgPrice,
       ROW_NUMBER() OVER (PARTITION BY rp.CategoryId ORDER BY rp.Price DESC) as PriceRank
FROM RecentProducts rp
INNER JOIN CategoryTotals ct ON rp.CategoryId = ct.CategoryId
WHERE rp.Price >= @minPrice
ORDER BY rp.CategoryId, rp.Price DESC
Parameters: @startDate=22/02/2025 17:34:39, @isActive=True, @minPrice=50.00

Basic Examples

Step-by-step examples to get you started with LambdifySQL.

1 Basic SELECT Query

C# Code

var basicSelectQuery = SqlQuery.Select<Product>()
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name);

SQL Output

SELECT product.*
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
ORDER BY product.[Name] ASC
Parameters: @p0=True

2 Complex WHERE Conditions

C# Code

var searchTerms = new List<string> { "laptop", "phone", "tablet" };
var categoryIds = new List<int> { 1, 2, 3 };

var complexWhere = SqlQuery.Select<Product>()
    .Where(p => p.Name.Contains("electronics") || searchTerms.Contains(p.Name))
    .Where(p => categoryIds.Contains(p.CategoryId))
    .Where(p => p.Price >= 100 && p.Price <= 1000)
    .OrderBy(p => p.Price);

SQL Output

SELECT product.*
FROM [Product] AS product
WHERE (product.[Name] LIKE @p0 OR product.[Name] IN (@p1, @p2, @p3)) 
  AND product.[CategoryId] IN (@p4, @p5, @p6) 
  AND ((product.[Price] >= @p7) AND (product.[Price] <= @p8))
ORDER BY product.[Price] ASC
Parameters: @p0=%electronics%, @p1=laptop, @p2=phone, @p3=tablet, 
           @p4=1, @p5=2, @p6=3, @p7=100, @p8=1000

3 UPDATE Query

C# Code

var updateQuery = SqlQuery.Update<Product>()
    .Set(p => p.Price, 199.99m)
    .Set(p => p.IsActive, true)
    .Where(p => p.CategoryId == 1 && p.Price < 200);

SQL Output

UPDATE product
SET product.[Price] = @p0, product.[IsActive] = @p1
FROM [Product] AS product
WHERE ((product.[CategoryId] = @p2) AND (product.[Price] < @p3))
Parameters: @p0=199.99, @p1=True, @p2=1, @p3=200

4 INSERT Query

C# Code

var newProduct = new Product
{
    Name = "New Laptop",
    Qty = 10,
    Price = 999.99m,
    CategoryId = 1,
    ExtraId = 1,
    IsActive = true
};

var insertQuery = SqlQuery.Insert<Product>()
    .Values(newProduct);

SQL Output

INSERT INTO [Product]
([Name], [Qty], [Price], [CategoryId], [ExtraId], [CreatedAt], [IsActive])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
Parameters: @p0=New Laptop, @p1=10, @p2=999.99, @p3=1, @p4=1, 
           @p5=01/01/0001 00:00:00, @p6=True

5 DELETE Query

C# Code

var deleteQuery = SqlQuery.Delete<Product>()
    .Where(p => p.IsActive == false)
    .Where(p => p.CreatedAt < DateTime.Now.AddYears(-1));

SQL Output

DELETE product
FROM [Product] AS product
WHERE (product.[IsActive] = @p0) AND (product.[CreatedAt] < DATEADD(year, @p1, @p2))
Parameters: @p0=False, @p1=-1, @p2=22/08/2025 17:34:39

6 Aggregate Queries

C# Code

var queryAggregate = SqlQuery.Aggregate<Product>()
    .GroupBy(p => p.CategoryId)
    .Count(alias: "ProductCount")
    .Sum(p => p.Price, "TotalPrice")
    .Average(p => p.Price, "AvgPrice")
    .Where(p => p.IsActive);

SQL Output

SELECT product.[CategoryId], COUNT(*) AS [ProductCount], 
       SUM(product.[Price]) AS [TotalPrice], 
       AVG(product.[Price]) AS [AvgPrice]
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
GROUP BY product.[CategoryId]
Parameters: @p0=True

7 Window Functions

C# Code

var queryWindowFunction = SqlQuery.WindowFunction<Product>()
    .Select(p => p.Name)
    .Select(p => p.Price)
    .RowNumber(p => p.CategoryId, p => p.Price, "RowNum")
    .Rank(p => p.CategoryId, p => p.Price, "PriceRank")
    .Where(p => !p.IsActive);

SQL Output

SELECT product.[Name], product.[Price], 
       ROW_NUMBER() OVER (PARTITION BY product.[CategoryId] ORDER BY product.[Price]) AS [RowNum], 
       RANK() OVER (PARTITION BY product.[CategoryId] ORDER BY product.[Price]) AS [PriceRank]
FROM [Product] AS product
WHERE (product.[IsActive] = @p0)
Parameters: @p0=False

8 JOIN Operations

C# Code

var multiJoinQuery = SqlQuery.SelectWithJoins<Product>()
    .InnerJoin<Category>("cat", (p, c) => p.CategoryId == c.Id)
    .LeftJoin<ProductExtraDetails>("details", (p, d) => p.ExtraId == d.Id)
    .Where<Product>(p => p.Price > 100)
    .OrderBy(p => p.Price)
    .ThenBy(p => p.Name);

SQL Output

SELECT product.*
FROM [Product] AS product
INNER JOIN [Category] AS cat ON product.Id = cat.Id
LEFT JOIN [ProductExtraDetails] AS details ON product.Id = details.Id
WHERE (product.[Price] > @p0)
ORDER BY product.[Price] ASC, product.[Name] ASC
Parameters: @p0=100

9 Advanced CTE Example

C# Code

var cteQuery = SqlQuery.Select<Product>()
    .Where(p => p.Qty >= 1)
    .Top(10);

var mainQuery = SqlQuery.Select<Product>()
    .Where(p => p.CategoryId == 2);

var advancedQuery = SqlQuery.Advanced<Product>()
    .WithCTE("TopProducts", cteQuery)
    .Query(mainQuery);

SQL Output

WITH TopProducts AS (SELECT TOP (10) product.*
FROM [Product] AS product
WHERE (product.[Qty] >= @p0))
SELECT product.*
FROM [Product] AS product
WHERE (product.[CategoryId] = @p1)
Parameters: @p0=1, @p1=2

10 Raw SQL Example

C# Code

var rawQuery = SqlQuery.Raw(
    "SELECT * FROM Product WHERE Price BETWEEN @minPrice AND @maxPrice",
    new { minPrice = 100, maxPrice = 500 }
);

SQL Output

SELECT * FROM Product WHERE Price BETWEEN @minPrice AND @maxPrice
Parameters: @minPrice=100, @maxPrice=500

Supported Attributes

`[TableName]`

Specify table name and alias

`[Pk]`

Mark primary key properties

`[Column]`

Specify column name and constraints

`[Relation]`

Define foreign key relationships

`[IgnoreMe]`

Exclude properties from SQL generation

`[Required]`

Mark properties as NOT NULL

`[MaxLength]`

Specify maximum length for strings

`[DefaultValue]`

Set default values

SQL Dialect Support

// SQL Server (default)
var query = SqlQuery.Select<Product>(SqlDialectConfig.SqlServer);

// MySQL
var query = SqlQuery.Select<Product>(SqlDialectConfig.MySql);

// PostgreSQL
var query = SqlQuery.Select<Product>(SqlDialectConfig.PostgreSql);

// SQLite
var query = SqlQuery.Select<Product>(SqlDialectConfig.SQLite);

Advanced Features

OrWhere Support

LambdifySQL provides full support for OR conditions:

                                var query = SqlQuery.Select<Product>()
    .Where(p => p.Price > 100)
    .OrWhere(p => p.Qty < 5)
    .OrWhere(p => p.CategoryId == 1);
                            

Parameterization

All values are automatically parameterized to prevent SQL injection:

                                var query = SqlQuery.Select<Product>()
    .Where(p => p.Name.Contains(userInput)); // Automatically parameterized
                            

Method Chaining

Fluent interface allows for intuitive query building:

                                var query = SqlQuery.Select<Product>()
    .Where(p => p.IsActive)
    .OrWhere(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .ThenBy(p => p.Price)
    .Take(10);
                            

Query Validation

var query = SqlQuery.Select<Product>()
    .Where(p => p.Name.Contains("'; DROP TABLE Product; --"));

var issues = query.Validate();
if (issues.Any())
{
    foreach (var issue in issues)
    {
        Console.WriteLine($"Security issue: {issue}");
    }
}

Debug and Testing

var query = SqlQuery.Select<Product>()
    .Where(p => p.Price > 100);

// Get SQL with debug information
Console.WriteLine(query.ToDebugString());

// Get parameterized SQL (for testing)
Console.WriteLine(query.ToParameterizedSql());

// Get SQL and parameters separately
var (sql, parameters) = query.Build();

Stored Procedure Calls

var procCall = SqlQuery.StoredProcedure("GetProductsByCategory", 
    new { CategoryId = 1, IsActive = true });

Requirements

.NET 8.0 or higher

Built for modern .NET with the latest features

C# 12.0 language features

Takes advantage of latest C# language enhancements

Complete API Reference

Comprehensive reference for all LambdifySQL classes, methods, and extension points.

SqlQuery Entry Points

SqlQuery.Select<T>()

Creates a SELECT query builder for the specified entity type.

public static ISelectQueryBuilder<T> Select<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.Select<Product>()
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name);

SqlQuery.SelectWithJoins<T>()

Creates a SELECT query builder with JOIN capabilities.

public static IJoinQueryBuilder<T> SelectWithJoins<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.SelectWithJoins<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.IsActive);

SqlQuery.Update<T>()

Creates an UPDATE query builder for the specified entity type.

public static IUpdateQueryBuilder<T> Update<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.Update<Product>()
    .Set(p => p.Price, 99.99m)
    .Where(p => p.Id == 1);

SqlQuery.Insert<T>()

Creates an INSERT query builder for the specified entity type.

public static IInsertQueryBuilder<T> Insert<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.Insert<Product>()
    .Values(new Product { Name = "New Product", Price = 99.99m });

SqlQuery.BulkInsert<T>()

Creates a bulk INSERT query builder for the specified entity type.

public static BulkInsertQueryBuilder<T> BulkInsert<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.BulkInsert<Product>()
    .Values(productList);

SqlQuery.Delete<T>()

Creates a DELETE query builder for the specified entity type.

public static IDeleteQueryBuilder<T> Delete<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.Delete<Product>()
    .Where(p => p.IsActive == false);

SqlQuery.Advanced<T>()

Creates an advanced query builder with CTE support.

public static AdvancedQueryBuilder<T> Advanced<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.Advanced<Product>()
    .FromCTE("ProductCTE");

SqlQuery.Aggregate<T>()

Creates an aggregate query builder for complex aggregations.

public static AggregateQueryBuilder<T> Aggregate<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.Aggregate<Product>();

SqlQuery.WindowFunction<T>()

Creates a window function query builder.

public static WindowFunctionBuilder<T> WindowFunction<T>(SqlDialectConfig dialect = null)

var query = SqlQuery.WindowFunction<Product>();

SqlQuery.Raw()

Creates a raw SQL query with parameters.

public static SqlComponent Raw(string sql, object parameters = null)

var query = SqlQuery.Raw("SELECT * FROM Products WHERE Price > @price", 
    new { price = 100 });

SqlQuery.StoredProcedure()

Creates a stored procedure call.

public static SqlComponent StoredProcedure(string procedureName, object parameters = null)

var query = SqlQuery.StoredProcedure("GetProductsByCategory", 
    new { categoryId = 1 });

Core Interfaces

ISelectQueryBuilder<T>

Interface for building SELECT queries with fluent syntax.

Key Methods:

Where(Expression<Func<T, bool>> predicate) - Add WHERE conditions
OrWhere(Expression<Func<T, bool>> predicate) - Add OR WHERE conditions
OrderBy<TProperty>(Expression<Func<T, TProperty>> selector) - Order results
OrderByDescending<TProperty>(Expression<Func<T, TProperty>> selector) - Order descending
ThenBy<TProperty>(Expression<Func<T, TProperty>> selector) - Secondary ordering
ThenByDescending<TProperty>(Expression<Func<T, TProperty>> selector) - Secondary ordering descending
Top(int count) - Limit results (SQL Server style)
Take(int count) - Limit results
WhereIn<TProperty, TSubQuery>(...) - WHERE IN with subquery
WhereRaw(string rawCondition) - Raw WHERE clause
FromCTE(string cteName) - Select from CTE

IJoinQueryBuilder<T>

Interface for building queries with JOIN operations.

Key Methods:

InnerJoin<TJoin>(Expression<Func<T, TJoin, bool>> condition)
InnerJoin<TJoin>(string alias, Expression<Func<T, TJoin, bool>> condition)
LeftJoin<TJoin>(Expression<Func<T, TJoin, bool>> condition)
LeftJoin<TJoin>(string alias, Expression<Func<T, TJoin, bool>> condition)
RightJoin<TJoin>(Expression<Func<T, TJoin, bool>> condition)
RightJoin<TJoin>(string alias, Expression<Func<T, TJoin, bool>> condition)
FullOuterJoin<TJoin>(Expression<Func<T, TJoin, bool>> condition)
FullOuterJoin<TJoin>(string alias, Expression<Func<T, TJoin, bool>> condition)
Where<TEntity>(Expression<Func<TEntity, bool>> predicate)
Where<TEntity>(string alias, Expression<Func<TEntity, bool>> predicate)
OrWhere<TEntity>(Expression<Func<TEntity, bool>> predicate)
OrderBy<TEntity, TProperty>(Expression<Func<TEntity, TProperty>> selector)
GroupBy<TEntity, TProperty>(Expression<Func<TEntity, TProperty>> selector)
Having<TEntity>(Expression<Func<TEntity, bool>> predicate)

AdvancedQueryBuilder<T>

Concrete class for advanced query operations including CTEs.

Note:

This is a concrete builder class, not an interface. Check the actual implementation for available methods.

Entity Attributes

[TableName(string tableName, string alias = null)]

Maps a class to a database table with optional alias.

[TableName("Products", "p")]
public class Product { }

[Pk(string pk = null)]

Marks a property as the primary key with optional column name.

[Pk("ProductId")]
public int Id { get; set; }

[Column(string columnName)]

Maps a property to a specific database column. Supports MaxLength, DataType, and IsNullable properties.

[Column("ProductName", MaxLength = 100, DataType = "NVARCHAR")]
public string Name { get; set; }

[Relation(Type type)]

Defines relationships between entities. Supports ForeignKey and ReferencedKey properties.

[Relation(typeof(Category), ForeignKey = "CategoryId", ReferencedKey = "Id")]
public int CategoryId { get; set; }

[Required]

Marks a property as required (NOT NULL). Supports ErrorMessage property.

[Required(ErrorMessage = "Name is required")]
public string Name { get; set; }

[MaxLength(int maxLength)]

Specifies the maximum length for string properties.

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

[DefaultValue(object value)]

Specifies a default value for the property.

[DefaultValue(true)]
public bool IsActive { get; set; }

[Index(string indexName = null)]

Indicates that the property should be indexed. Supports IsUnique, IsClustered, and Order properties.

[Index("IX_Product_SKU", IsUnique = true, Order = 1)]
public string SKU { get; set; }

[IgnoreMe]

Excludes the property from SQL generation.

[IgnoreMe]
public virtual Category Category { get; set; }

[Computed(string expression = null)]

Marks a property as computed (read-only) with optional expression.

[Computed("([Name] + ' - ' + [SKU])")]
public string FullDisplayName { get; set; }

SQL Dialect Support

SqlDialectConfig

Configuration class for different SQL dialects with specific settings for each database.

Available Dialects:

SqlDialectConfig.SqlServer - Microsoft SQL Server (default)
SqlDialectConfig.MySql - MySQL Database
SqlDialectConfig.PostgreSql - PostgreSQL Database

var query = SqlQuery.Select<Product>(SqlDialectConfig.PostgreSql)
    .Where(p => p.IsActive)
    .Build();

Dialect-Specific Features:

SQL Server (Default)

Parameter prefix: @
Identifier quotes: [ ]
Uses TOP clause for LIMIT
UseTop = true, UseLimit = false

PostgreSQL

Parameter prefix: @
Identifier quotes: " "
Uses LIMIT/OFFSET for pagination
UseTop = false, UseLimit = true

MySQL

Parameter prefix: @
Identifier quotes: ` `
Uses LIMIT/OFFSET for pagination
UseTop = false, UseLimit = true

Extension Methods

Build() Extension

Converts a query builder to a tuple containing SQL and parameters.

public static (string Sql, Dictionary<string, object> Parameters) Build(this ISqlQuery query)

var (sql, parameters) = SqlQuery.Select<Product>()
    .Where(p => p.IsActive)
    .Build();

GetSql() and GetParameters()

Separate methods to get SQL and parameters independently.

var query = SqlQuery.Select<Product>().Where(p => p.IsActive);
var sql = query.GetSql();
var parameters = query.GetParameters();

Performance Guidelines

Query Optimization

Use specific column selection instead of SELECT *
Apply WHERE conditions early in the query chain
Use appropriate indexes defined with [Index] attributes
Prefer EXISTS over IN for subqueries

Memory Management

Reuse SqlDialectConfig instances
Use parameterized queries (automatic with LambdifySQL)
Consider using bulk operations for large datasets
Cache compiled expressions where possible

Security Best Practices

Always use parameterized queries (built-in protection)
Validate input data before query construction
Use strongly-typed entities for type safety
Implement proper error handling and logging

About the Author

Lead Developer

Oscar Itaba

Experienced Software Engineer & Open Source Contributor

With over 16+ years of experience in .NET development and database technologies, Oscar is passionate about creating tools that simplify complex data operations. He specializes in building high-performance applications and has extensive experience with Entity Framework, SQL Server, PostgreSQL, and MySQL.

LambdifySQL was born from his frustration with existing ORM limitations and the need for a more flexible, type-safe SQL query builder. The library combines the power of LINQ expressions with direct SQL control, making it easier for developers to write maintainable and performant database queries.

When not coding, Oscar enjoys mentoring junior developers, contributing to open-source projects, and exploring new technologies in the .NET ecosystem, Node and Typescript

LinkedIn Github

"Great software should empower developers to focus on solving business problems, not wrestling with technical complexity. LambdifySQL embodies this philosophy by providing a clean, intuitive API that doesn't sacrifice performance or flexibility."
— Oscar, Creator of LambdifySQL