Library

LINQ to DB

Overview

LINQ to DB is a lightweight, SQL-first .NET ORM library. It achieves high performance through direct SQL translation of LINQ queries and supports diverse database providers. It is chosen by developers seeking more direct database control and performance as an alternative to Entity Framework.

Details

LINQ to DB 2025 edition is established as a popular choice among LINQ enthusiasts. Supported by developers needing both SQL control and LINQ syntax, it is particularly adopted in scenarios with strict performance requirements. While complex SQL queries can be expressed in LINQ syntax, it has the flexibility to execute raw SQL directly when needed. It supports over 30 databases including SQL Server, PostgreSQL, MySQL, SQLite, Oracle, and DB2.

Key Features

• Fast LINQ Provider: Optimized SQL generation
• Extensive Database Support: Over 30 databases supported
• SQL-First Design: Direct SQL control possible
• Bulk Operations: Fast bulk insert, update, and delete
• Stored Procedure Support: Complete integration
• Lightweight: Minimal dependencies and memory footprint

Pros and Cons

Pros

• Faster query execution than Entity Framework
• Complete control over complex SQL queries
• Wide database provider support
• Excellent performance through bulk operations
• Superior compatibility with existing database schemas
• Relatively gentle learning curve

Cons

• Fewer advanced ORM features than Entity Framework
• No change tracking functionality
• Smaller community and ecosystem
• Limited migration features
• More complex initial setup than Entity Framework

References

• LINQ to DB Official Site
• LINQ to DB GitHub Repository
• LINQ to DB Documentation

Examples

Basic Setup

// NuGet package installation
// Install-Package linq2db
// Install-Package linq2db.SqlServer  // For SQL Server
// Install-Package linq2db.PostgreSQL // For PostgreSQL

// Model definition
using LinqToDB.Mapping;
using System;

[Table("Users")]
public class User
{
    [PrimaryKey, Identity]
    public int Id { get; set; }
    
    [Column(Length = 100), NotNull]
    public string Name { get; set; }
    
    [Column(Length = 255), NotNull]
    public string Email { get; set; }
    
    [Column]
    public int? Age { get; set; }
    
    [Column]
    public DateTime CreatedAt { get; set; }
}

[Table("Posts")]
public class Post
{
    [PrimaryKey, Identity]
    public int Id { get; set; }
    
    [Column(Length = 200), NotNull]
    public string Title { get; set; }
    
    [Column(DataType = DataType.Text)]
    public string Content { get; set; }
    
    [Column]
    public int AuthorId { get; set; }
    
    [Column]
    public DateTime CreatedAt { get; set; }
    
    // Navigation property
    [Association(ThisKey = nameof(AuthorId), OtherKey = nameof(User.Id))]
    public User Author { get; set; }
}

// Database context
using LinqToDB;
using LinqToDB.Data;

public class BlogDb : DataConnection
{
    public BlogDb() : base("BlogConnection") { }
    
    public ITable<User> Users => this.GetTable<User>();
    public ITable<Post> Posts => this.GetTable<Post>();
}

Basic CRUD Operations

using LinqToDB;
using System;
using System.Linq;
using System.Threading.Tasks;

public class UserRepository
{
    private readonly BlogDb _db;
    
    public UserRepository(BlogDb db)
    {
        _db = db;
    }
    
    // CREATE - Create new record
    public async Task<int> CreateUserAsync(User user)
    {
        user.CreatedAt = DateTime.UtcNow;
        return await _db.InsertWithInt32IdentityAsync(user);
    }
    
    // READ - Read records
    public async Task<User> GetUserByIdAsync(int id)
    {
        return await _db.Users
            .FirstOrDefaultAsync(u => u.Id == id);
    }
    
    public async Task<List<User>> GetAllUsersAsync()
    {
        return await _db.Users
            .OrderBy(u => u.Name)
            .ToListAsync();
    }
    
    // UPDATE - Update records
    public async Task<int> UpdateUserAsync(User user)
    {
        return await _db.UpdateAsync(user);
    }
    
    // Partial update
    public async Task<int> UpdateUserAgeAsync(int userId, int newAge)
    {
        return await _db.Users
            .Where(u => u.Id == userId)
            .Set(u => u.Age, newAge)
            .UpdateAsync();
    }
    
    // DELETE - Delete records
    public async Task<int> DeleteUserAsync(int id)
    {
        return await _db.Users
            .Where(u => u.Id == id)
            .DeleteAsync();
    }
    
    // Complex queries
    public async Task<List<User>> SearchUsersAsync(
        string keyword, 
        int? minAge, 
        int? maxAge)
    {
        var query = _db.Users.AsQueryable();
        
        if (!string.IsNullOrEmpty(keyword))
        {
            query = query.Where(u => 
                u.Name.Contains(keyword) || 
                u.Email.Contains(keyword));
        }
        
        if (minAge.HasValue)
        {
            query = query.Where(u => u.Age >= minAge.Value);
        }
        
        if (maxAge.HasValue)
        {
            query = query.Where(u => u.Age <= maxAge.Value);
        }
        
        return await query
            .OrderBy(u => u.Name)
            .ToListAsync();
    }
}

Advanced Features

using LinqToDB;
using LinqToDB.Linq;
using System.Linq;

public class AdvancedQueries
{
    private readonly BlogDb _db;
    
    public AdvancedQueries(BlogDb db)
    {
        _db = db;
    }
    
    // JOIN operations
    public async Task<List<PostWithAuthor>> GetPostsWithAuthorsAsync()
    {
        var result = await (
            from p in _db.Posts
            join u in _db.Users on p.AuthorId equals u.Id
            select new PostWithAuthor
            {
                PostId = p.Id,
                Title = p.Title,
                Content = p.Content,
                AuthorName = u.Name,
                AuthorEmail = u.Email,
                CreatedAt = p.CreatedAt
            }
        ).ToListAsync();
        
        return result;
    }
    
    // Aggregate functions
    public async Task<UserStatistics> GetUserStatisticsAsync()
    {
        var stats = await _db.Users
            .Select(g => new UserStatistics
            {
                TotalUsers = _db.Users.Count(),
                AverageAge = _db.Users.Average(u => u.Age) ?? 0,
                MaxAge = _db.Users.Max(u => u.Age) ?? 0,
                MinAge = _db.Users.Min(u => u.Age) ?? 0
            })
            .FirstOrDefaultAsync();
            
        return stats;
    }
    
    // Grouping
    public async Task<List<AgeGroupCount>> GetUsersByAgeGroupAsync()
    {
        var result = await (
            from u in _db.Users
            where u.Age.HasValue
            group u by u.Age.Value / 10 into g
            orderby g.Key
            select new AgeGroupCount
            {
                AgeGroup = g.Key * 10,
                Count = g.Count()
            }
        ).ToListAsync();
        
        return result;
    }
    
    // Subqueries
    public async Task<List<User>> GetActiveUsersAsync()
    {
        var activeUserIds = _db.Posts
            .Where(p => p.CreatedAt >= DateTime.UtcNow.AddDays(-30))
            .GroupBy(p => p.AuthorId)
            .Where(g => g.Count() >= 5)
            .Select(g => g.Key);
        
        return await _db.Users
            .Where(u => activeUserIds.Contains(u.Id))
            .ToListAsync();
    }
    
    // Bulk operations
    public async Task BulkInsertUsersAsync(List<User> users)
    {
        // Fast bulk copy
        await _db.BulkCopyAsync(users);
    }
    
    public async Task BulkUpdateUsersAsync(List<User> users)
    {
        // Bulk update
        foreach (var batch in users.Batch(1000))
        {
            await _db.UpdateAsync(batch);
        }
    }
    
    // Stored procedure execution
    public async Task<List<User>> ExecuteStoredProcAsync(string keyword)
    {
        return await _db.QueryProc<User>("SearchUsers",
            new DataParameter("@Keyword", keyword))
            .ToListAsync();
    }
    
    // Raw SQL execution
    public async Task<List<CustomResult>> ExecuteRawSqlAsync()
    {
        var sql = @"
            SELECT 
                u.Name as UserName,
                COUNT(p.Id) as PostCount,
                MAX(p.CreatedAt) as LastPostDate
            FROM Users u
            LEFT JOIN Posts p ON u.Id = p.AuthorId
            GROUP BY u.Id, u.Name
            HAVING COUNT(p.Id) > 0
            ORDER BY PostCount DESC";
        
        return await _db.Query<CustomResult>(sql).ToListAsync();
    }
    
    // Transaction processing
    public async Task<bool> TransferPostsAsync(
        int fromUserId, 
        int toUserId)
    {
        using (var transaction = await _db.BeginTransactionAsync())
        {
            try
            {
                // Verify source user exists
                var fromUser = await _db.Users
                    .FirstOrDefaultAsync(u => u.Id == fromUserId);
                if (fromUser == null)
                    throw new InvalidOperationException("Source user not found");
                
                // Verify target user exists
                var toUser = await _db.Users
                    .FirstOrDefaultAsync(u => u.Id == toUserId);
                if (toUser == null)
                    throw new InvalidOperationException("Target user not found");
                
                // Transfer posts
                var updatedCount = await _db.Posts
                    .Where(p => p.AuthorId == fromUserId)
                    .Set(p => p.AuthorId, toUserId)
                    .UpdateAsync();
                
                await transaction.CommitAsync();
                return true;
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }
    }
}

// DTO classes
public class PostWithAuthor
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public string AuthorName { get; set; }
    public string AuthorEmail { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class UserStatistics
{
    public int TotalUsers { get; set; }
    public double AverageAge { get; set; }
    public int MaxAge { get; set; }
    public int MinAge { get; set; }
}

public class AgeGroupCount
{
    public int AgeGroup { get; set; }
    public int Count { get; set; }
}

public class CustomResult
{
    public string UserName { get; set; }
    public int PostCount { get; set; }
    public DateTime? LastPostDate { get; set; }
}

Practical Example

// ASP.NET Core Web API usage example
using Microsoft.AspNetCore.Mvc;
using LinqToDB.Configuration;

// Startup.cs / Program.cs
public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        // LINQ to DB configuration
        services.AddSingleton<IDataProvider>(SqlServerTools.GetDataProvider());
        services.AddScoped<BlogDb>();
        services.AddScoped<UserRepository>();
        
        services.AddControllers();
    }
}

// appsettings.json
{
  "ConnectionStrings": {
    "BlogConnection": "Server=localhost;Database=BlogDB;User Id=sa;Password=yourPassword;"
  }
}

// Controllers/UsersController.cs
[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly UserRepository _userRepository;
    private readonly BlogDb _db;
    
    public UsersController(UserRepository userRepository, BlogDb db)
    {
        _userRepository = userRepository;
        _db = db;
    }
    
    [HttpGet]
    public async Task<ActionResult<IEnumerable<User>>> GetUsers(
        [FromQuery] string keyword,
        [FromQuery] int? minAge,
        [FromQuery] int? maxAge,
        [FromQuery] int page = 1,
        [FromQuery] int pageSize = 10)
    {
        var query = _db.Users.AsQueryable();
        
        // Filtering
        if (!string.IsNullOrEmpty(keyword))
        {
            query = query.Where(u => 
                u.Name.Contains(keyword) || 
                u.Email.Contains(keyword));
        }
        
        if (minAge.HasValue)
            query = query.Where(u => u.Age >= minAge.Value);
            
        if (maxAge.HasValue)
            query = query.Where(u => u.Age <= maxAge.Value);
        
        // Get total count
        var totalCount = await query.CountAsync();
        
        // Pagination
        var users = await query
            .OrderBy(u => u.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();
        
        // Include total count in header
        Response.Headers.Add("X-Total-Count", totalCount.ToString());
        
        return Ok(users);
    }
    
    [HttpGet("{id}")]
    public async Task<ActionResult<User>> GetUser(int id)
    {
        var user = await _userRepository.GetUserByIdAsync(id);
        
        if (user == null)
            return NotFound();
        
        return Ok(user);
    }
    
    [HttpPost]
    public async Task<ActionResult<User>> CreateUser(CreateUserDto dto)
    {
        var user = new User
        {
            Name = dto.Name,
            Email = dto.Email,
            Age = dto.Age
        };
        
        user.Id = await _userRepository.CreateUserAsync(user);
        
        return CreatedAtAction(
            nameof(GetUser), 
            new { id = user.Id }, 
            user);
    }
    
    [HttpPut("{id}")]
    public async Task<IActionResult> UpdateUser(int id, UpdateUserDto dto)
    {
        var user = await _userRepository.GetUserByIdAsync(id);
        if (user == null)
            return NotFound();
        
        user.Name = dto.Name;
        user.Email = dto.Email;
        user.Age = dto.Age;
        
        await _userRepository.UpdateUserAsync(user);
        
        return NoContent();
    }
    
    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteUser(int id)
    {
        var deleted = await _userRepository.DeleteUserAsync(id);
        
        if (deleted == 0)
            return NotFound();
        
        return NoContent();
    }
    
    [HttpPost("bulk")]
    public async Task<IActionResult> BulkCreateUsers(List<CreateUserDto> dtos)
    {
        var users = dtos.Select(dto => new User
        {
            Name = dto.Name,
            Email = dto.Email,
            Age = dto.Age,
            CreatedAt = DateTime.UtcNow
        }).ToList();
        
        await _db.BulkCopyAsync(users);
        
        return Ok(new { Count = users.Count });
    }
    
    [HttpGet("statistics")]
    public async Task<ActionResult> GetStatistics()
    {
        var stats = await _db.Users
            .Select(u => new
            {
                TotalUsers = _db.Users.Count(),
                UsersWithPosts = _db.Posts.Select(p => p.AuthorId).Distinct().Count(),
                AverageAge = _db.Users.Where(x => x.Age.HasValue).Average(x => x.Age),
                TotalPosts = _db.Posts.Count()
            })
            .FirstOrDefaultAsync();
        
        return Ok(stats);
    }
}

// DTO classes
public class CreateUserDto
{
    public string Name { get; set; }
    public string Email { get; set; }
    public int? Age { get; set; }
}

public class UpdateUserDto
{
    public string Name { get; set; }
    public string Email { get; set; }
    public int? Age { get; set; }
}

// Repository pattern and UnitOfWork
public interface IUnitOfWork : IDisposable
{
    BlogDb Database { get; }
    Task<int> CommitAsync();
    Task RollbackAsync();
}

public class UnitOfWork : IUnitOfWork
{
    private BlogDb _database;
    private IDbTransaction _transaction;
    
    public BlogDb Database => _database;
    
    public UnitOfWork(string connectionString)
    {
        _database = new BlogDb();
        _transaction = _database.BeginTransaction();
    }
    
    public async Task<int> CommitAsync()
    {
        try
        {
            await _transaction.CommitAsync();
            return 0;
        }
        catch
        {
            await _transaction.RollbackAsync();
            throw;
        }
    }
    
    public async Task RollbackAsync()
    {
        await _transaction.RollbackAsync();
    }
    
    public void Dispose()
    {
        _transaction?.Dispose();
        _database?.Dispose();
    }
}