LINQ Fluent Query API

EntityTS brings the full expressive power of Language Integrated Query (LINQ) and Entity Framework Core (EF Core) to TypeScript.

All querying is structured around strongly-typed, composable LINQ method chaining—providing autocompletion, compile-time safety, and automatic SQL compilation across PostgreSQL, MySQL, SQLite, and MSSQL.

---

🔍 Filtering (where)

EntityTS provides pure LINQ lambda predicates matching C# / EF Core syntax—with zero string-like operators needed:

1. Pure LINQ Lambda Predicates

Write standard TypeScript expressions. EntityTS parses the AST and compiles directly to parameterized SQL:

// Comparisons, equality, and boolean shorthand
const adultUsers = await db.users.where(u => u.age >= 18 && u.isActive).toList();

// String LINQ methods (translated to SQL LIKE patterns)
const matching = await db.users
  .where(u => u.name.startsWith('Al'))
  .where(u => u.email.endsWith('@company.com'))
  .where(u => u.bio.includes('engineer'))
  .toList();

// Set containment (translated to SQL IN / NOT IN)
const staff = await db.users.where(u => ['admin', 'manager', 'lead'].includes(u.role)).toList();

// Null / Undefined checks (translated to SQL IS NULL / IS NOT NULL)
const pendingReview = await db.users.where(u => u.reviewedAt === null).toList();

// Logical Disjunction (OR)
const privileged = await db.users.where(u => u.role === 'admin' || u.role === 'moderator').toList();

2. Direct Key-Value Equality Shorthand

const verifiedStaff = await db.users
  .where({ isVerified: true, department: 'Engineering' })
  .toList();

---

🎯 Projections (select)

Project database records into refined DTOs, picking only the columns you need:

// Select specific columns with type-safe property names
const summaries = await db.users
  .where(u => u.isActive, '=', true)
  .select('id', 'name', 'email')
  .toList();
// Result type: Array<{ id: number; name: string; email: string }>

---

📊 Sorting (orderBy & thenBy)

Chain primary and secondary sort criteria with compile-time property verification:

const sortedUsers = await db.users
  .where(u => u.isActive, '=', true)
  .orderBy(u => u.department, 'asc')
  .thenBy(u => u.createdAt, 'desc')
  .toList();

---

🔗 Eager Loading (include & thenInclude)

Eagerly load related entities without N+1 query overhead:

const usersWithDetails = await db.users
  .include(u => u.posts)
  .thenInclude(p => p.comments)
  .include(u => u.profile)
  .where(u => u.isActive, '=', true)
  .toList();

---

🔢 Pagination & Slicing (skip & take)

Implement standard LINQ offset pagination or high-performance keyset cursor pagination:

// Standard LINQ skip & take
const pageRecords = await db.users
  .orderBy(u => u.id, 'asc')
  .skip(20)
  .take(10)
  .toList();

// Keyset Cursor Pagination
const cursorPage = await db.posts
  .orderBy(p => p.id, 'desc')
  .toCursorPage({ limit: 25, cursor: previousCursorToken });

---

🔎 Single Element Lookups

Retrieve specific elements matching LINQ predicates:

// Returns first matching entity, or null if none found
const user = await db.users.firstOrDefault(u => u.email === 'alice@example.com');

// Throws EntityNotFoundException if not found
const requiredUser = await db.users.firstOrThrow(u => u.id === 42);

// Fast primary key lookup
const userById = await db.users.find(42);

// Single entity verification (throws if multiple records match)
const singleUser = await db.users.singleOrDefault(u => u.username === 'alice_dev');

---

📈 Aggregations & Quantifiers

Perform aggregate calculations directly in the database engine:

// Count
const totalCount = await db.users.count();
const activeAdmins = await db.users.where('role', '=', 'admin').count();

// Quantifiers (any / all)
const hasSuperAdmin = await db.users.any(u => u.role === 'superadmin');
const allVerified = await db.users.all(u => u.isVerified === true);

// Numeric Aggregations (sum, avg, min, max)
const totalRevenue = await db.orders.sum(o => o.totalAmount);
const averageAge = await db.users.avg(u => u.age);
const lowestPrice = await db.products.min(p => p.price);
const peakScore = await db.scores.max(s => s.score);

---

📦 Grouping & Summary Projections (groupBy)

Group records by key and project calculated aggregate summaries:

const salesByDepartment = await db.orders
  .groupBy(o => o.department)
  .select((group, g) => ({
    department: g.department,
    orderCount: group.count(),
    totalRevenue: group.sum('totalAmount'),
    avgTicket: group.avg('totalAmount'),
  }))
  .toList();

---

⚡ Performance Modifiers & Tracking

// AsNoTracking: Bypass change tracking for read-only query performance
const readOnlyData = await db.users
  .asNoTracking()
  .where(u => u.isActive)
  .toList();

// AsTracking: Explicitly opt-in to ChangeTracker observation
const trackedUsers = await db.users
  .asTracking()
  .where(u => u.role === 'member')
  .toList();

trackedUsers[0].role = 'admin';
await db.saveChanges(); // Automatically detects mutations and persists SQL UPDATE

// Distinct: Eliminate duplicate rows
const uniqueRoles = await db.users.select('role').distinct().toList();

---

⚡ Direct Batch Mutations (executeUpdate & executeDelete)

Execute high-performance bulk updates and bulk deletes directly on the database server in a single SQL statement without loading entities into memory or attaching them to the ChangeTracker (similar to EF Core's ExecuteUpdate & ExecuteDelete).

Bulk Updates with executeUpdate

Pass either a partial entity patch or a fluent UpdateSetBuilder callback:

// 1. Partial object patch with pure comparison predicate
const updatedCount = await db.users
  .where(u => u.lastLoginAt < thirtyDaysAgo)
  .executeUpdate({ isActive: false });

// 2. Fluent UpdateSetBuilder callback
await db.users
  .where(u => u.department === 'Sales')
  .executeUpdate(s => s.set(u => u.bonusEligible, true).set(u => u.reviewStatus, 'approved'));

// 3. Shorthand updateWhere on DbSet
await db.users.updateWhere(u => u.role === 'guest', { isActive: false });

Bulk Deletions with executeDelete

Delete all matching records directly at the database level. If an entity uses @SoftDelete(), executeDelete() automatically issues a soft-delete update instead of a physical deletion:

// Direct batch delete on LINQ query with compound predicate
const deletedCount = await db.notifications
  .where(n => n.isRead && n.createdAt < cutoffDate)
  .executeDelete();

// Shorthand removeWhere on DbSet
await db.logs.removeWhere(l => l.level === 'debug');