Query Patterns: LINQ vs. Prisma/Drizzle Queries

For .NET engineers who know: LINQ, EF Core query composition, Include/ThenInclude, and the difference between IQueryable and IEnumerable You’ll learn: How every common LINQ pattern maps to Prisma and Drizzle, including pagination, aggregations, raw SQL, and N+1 prevention Time: 15-20 min

The .NET Way (What You Already Know)

LINQ is the backbone of EF Core data access. You compose queries against IQueryable<T>, and EF Core translates the expression tree into SQL at execution time. The query does not run until you materialize it — with ToListAsync(), FirstOrDefaultAsync(), CountAsync(), etc.

// Simple filter + projection
var users = await _db.Users
    .Where(u => u.IsActive && u.Age >= 18)
    .Select(u => new UserDto { Id = u.Id, Name = u.Name, Email = u.Email })
    .OrderBy(u => u.Name)
    .ToListAsync();

// Navigation properties with Include
var orders = await _db.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Include(o => o.Customer)
    .Include(o => o.LineItems)
        .ThenInclude(li => li.Product)
    .ToListAsync();

// Aggregations
var stats = await _db.Orders
    .Where(o => o.CreatedAt >= DateTime.UtcNow.AddDays(-30))
    .GroupBy(o => o.CustomerId)
    .Select(g => new {
        CustomerId = g.Key,
        OrderCount = g.Count(),
        TotalSpend = g.Sum(o => o.Total)
    })
    .ToListAsync();

// Existence check
bool hasOverdueInvoices = await _db.Invoices
    .AnyAsync(i => i.DueDate < DateTime.UtcNow && i.Status != InvoiceStatus.Paid);

// Offset pagination
var page = await _db.Products
    .OrderBy(p => p.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Three things are built into this model that will not be present by default in the TS ORMs:

1. Change tracking — EF Core tracks every entity you load. Mutations detected at SaveChanges() time.
2. Lazy loading — navigation properties can be loaded automatically on first access (when configured).
3. Expression tree translation — Where(u => u.Name.Contains(query)) becomes SQL LIKE; it never executes in memory.

The TypeScript Way

Prisma

Prisma queries are structured as plain method calls on the Prisma Client, typed against the schema you define in schema.prisma. The API is not a query builder in the SQL sense — it is a constraint-typed object API. You express what you want, not how to get it.

Where (filter)

// Prisma
const users = await prisma.user.findMany({
  where: {
    isActive: true,
    age: { gte: 18 },
  },
  select: {
    id: true,
    name: true,
    email: true,
  },
  orderBy: { name: 'asc' },
});

// EF Core equivalent
var users = await _db.Users
    .Where(u => u.IsActive && u.Age >= 18)
    .Select(u => new { u.Id, u.Name, u.Email })
    .OrderBy(u => u.Name)
    .ToListAsync();

Include (eager loading relations)

// Prisma — include replaces Include/ThenInclude
const orders = await prisma.order.findMany({
  where: { status: 'PENDING' },
  include: {
    customer: true,
    lineItems: {
      include: {
        product: true,
      },
    },
  },
});

// EF Core
var orders = await _db.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Include(o => o.Customer)
    .Include(o => o.LineItems).ThenInclude(li => li.Product)
    .ToListAsync();

Select vs. include — choose one per query

In Prisma, select and include are mutually exclusive at the top level of a query. select gives you explicit field picking (like a projection); include adds related records on top of all scalar fields. If you want related records and also limit which scalar fields come back, nest select inside include.

// Nested select inside include
const orders = await prisma.order.findMany({
  include: {
    customer: {
      select: { id: true, name: true }, // only these fields from customer
    },
  },
});

Aggregations

// Count
const count = await prisma.user.count({ where: { isActive: true } });

// Aggregate (sum, avg, min, max)
const result = await prisma.order.aggregate({
  _sum: { total: true },
  _count: { id: true },
  where: {
    createdAt: { gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) },
  },
});
// result._sum.total, result._count.id

// GroupBy — closest to LINQ GroupBy + Select
const stats = await prisma.order.groupBy({
  by: ['customerId'],
  _count: { id: true },
  _sum: { total: true },
  where: {
    createdAt: { gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) },
  },
});

// EF Core GroupBy equivalent
var stats = await _db.Orders
    .Where(o => o.CreatedAt >= DateTime.UtcNow.AddDays(-30))
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Count = g.Count(), Total = g.Sum(o => o.Total) })
    .ToListAsync();

Any / All / Exists

// Any — does at least one match exist?
const hasOverdue = await prisma.invoice.count({
  where: {
    dueDate: { lt: new Date() },
    status: { not: 'PAID' },
  },
}) > 0;

// More efficiently with findFirst (stops at the first match)
const firstOverdue = await prisma.invoice.findFirst({
  where: { dueDate: { lt: new Date() }, status: { not: 'PAID' } },
  select: { id: true }, // minimise data transfer
});
const hasOverdue2 = firstOverdue !== null;

Prisma has no dedicated .exists() or .any() method. findFirst with a minimal select is the idiomatic pattern.

Skip / Take — offset pagination

// Prisma offset pagination
const page = await prisma.product.findMany({
  orderBy: { name: 'asc' },
  skip: (pageNumber - 1) * pageSize,
  take: pageSize,
});

Cursor pagination (preferred for large datasets)

Offset pagination degrades as offsets grow — the database must scan and skip rows. Cursor pagination is stable and scales.

// First page — no cursor
const firstPage = await prisma.product.findMany({
  take: 20,
  orderBy: { id: 'asc' },
});

// Subsequent pages — pass the last ID as cursor
const nextPage = await prisma.product.findMany({
  take: 20,
  cursor: { id: lastId },
  skip: 1, // skip the cursor row itself
  orderBy: { id: 'asc' },
});

Raw SQL

// Tagged template literal — safe parameterization
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM "User"
  WHERE email ILIKE ${'%' + domain}
  LIMIT 100
`;

// Unsafe raw (no parameterization — use only for dynamic identifiers you control)
const tableName = 'User';
const result = await prisma.$queryRawUnsafe(
  `SELECT COUNT(*) FROM "${tableName}"`
);

// Execute (no return value — UPDATE, DELETE, DDL)
const affected = await prisma.$executeRaw`
  UPDATE "User" SET "lastLoginAt" = NOW()
  WHERE id = ${userId}
`;

Drizzle

Drizzle takes a different philosophy: it is a query builder that looks like SQL and generates SQL you can inspect. The API is composable and closer to what you would write by hand.

import { db } from './db';
import { users, orders, lineItems, products } from './schema';
import { eq, gte, and, like, sql, count, sum, desc } from 'drizzle-orm';

// Where + select + orderBy
const result = await db
  .select({ id: users.id, name: users.name, email: users.email })
  .from(users)
  .where(and(eq(users.isActive, true), gte(users.age, 18)))
  .orderBy(users.name);

// Join (Drizzle uses explicit joins, not include)
const ordersWithCustomers = await db
  .select({
    orderId: orders.id,
    status: orders.status,
    customerName: users.name,
  })
  .from(orders)
  .innerJoin(users, eq(orders.customerId, users.id))
  .where(eq(orders.status, 'PENDING'));

// Aggregations
const stats = await db
  .select({
    customerId: orders.customerId,
    orderCount: count(orders.id),
    totalSpend: sum(orders.total),
  })
  .from(orders)
  .groupBy(orders.customerId);

// Skip / Take
const page = await db
  .select()
  .from(products)
  .orderBy(products.name)
  .offset((pageNumber - 1) * pageSize)
  .limit(pageSize);

// Raw SQL with Drizzle's sql`` tag
const raw = await db.execute(
  sql`SELECT * FROM users WHERE email ILIKE ${'%' + domain}`
);

Key Differences

Gotchas for .NET Engineers

1. No change tracking — mutations are always explicit

In EF Core, loading an entity and modifying a property is enough. SaveChanges() detects the diff and issues an UPDATE. Neither Prisma nor Drizzle tracks anything. Every update requires an explicit update call with the changed fields.

// WRONG mental model (will not work)
const user = await prisma.user.findUnique({ where: { id: userId } });
user.name = 'New Name'; // modifying the JS object does nothing
await prisma.save(); // this method does not exist

// CORRECT — always issue an explicit update
await prisma.user.update({
  where: { id: userId },
  data: { name: 'New Name' },
});

2. Prisma select and include cannot both appear at the query root

This will throw a runtime error, not a compile-time error in older Prisma versions:

// WRONG — mutually exclusive at the top level
const order = await prisma.order.findUnique({
  where: { id: orderId },
  select: { id: true, total: true },
  include: { customer: true }, // runtime error
});

// CORRECT — nest select inside include, or use include only
const order = await prisma.order.findUnique({
  where: { id: orderId },
  include: {
    customer: { select: { id: true, name: true } },
  },
});

3. N+1 is not prevented automatically

EF Core with Include() issues a single JOIN query (or a split query with AsSplitQuery()). Prisma issues one query per include level — which is efficient for small sets but becomes N+1 if you fetch a list and then access relations inside a loop:

// N+1 — DO NOT do this
const orders = await prisma.order.findMany({ where: { status: 'PENDING' } });
for (const order of orders) {
  // This issues a new DB query per iteration — N+1
  const customer = await prisma.user.findUnique({ where: { id: order.customerId } });
  console.log(customer.name);
}

// CORRECT — fetch with include in one query
const orders = await prisma.order.findMany({
  where: { status: 'PENDING' },
  include: { customer: true },
});
for (const order of orders) {
  console.log(order.customer.name); // no additional query
}

In Drizzle, this is even more explicit because you write joins yourself — but the same mistake is possible if you loop and query inside a map.

4. $queryRaw returns unknown[], not your model type

EF Core’s FromSqlRaw<T> returns properly typed results. Prisma’s $queryRaw returns unknown[] at runtime. The generic <User[]> annotation is a cast, not a runtime check.

// This compiles, but the cast is not validated at runtime
const users = await prisma.$queryRaw<User[]>`SELECT * FROM "User"`;
// users[0].email exists in TS types but could be undefined at runtime
// if your SQL doesn't select that column

// Safer: use Zod to validate the raw result
import { z } from 'zod';
const UserSchema = z.object({ id: z.string(), email: z.string() });
const raw = await prisma.$queryRaw`SELECT id, email FROM "User"`;
const users = z.array(UserSchema).parse(raw);

5. Prisma groupBy does not support computed fields in having directly

Complex HAVING clauses often require raw SQL in Prisma. EF Core’s LINQ-to-SQL translator handles most HAVING expressions naturally.

6. EXPLAIN ANALYZE — use it

When a Prisma or Drizzle query is slow, check the query plan. Prisma logs queries in development with log: ['query']; Drizzle can be wrapped with a logger.

// Prisma — enable query logging
const prisma = new PrismaClient({
  log: ['query', 'warn', 'error'],
});

// Then inspect slow queries with EXPLAIN ANALYZE in psql:
// EXPLAIN ANALYZE SELECT * FROM "Order" WHERE "customerId" = '...' ORDER BY "createdAt" DESC;

The most common findings:

• A missing index on a foreign key column (Prisma does not create these automatically).
• A sequential scan on a large table caused by a filter on a non-indexed column.
• An unexpected Nested Loop join from a sub-optimal Prisma include chain.

Hands-On Exercise

You have this EF Core query in a .NET API. Rewrite it twice: once using Prisma, once using Drizzle.

// Find all active customers who placed at least one order in the last 90 days,
// along with their most recent order and the count of total orders.
var results = await _db.Customers
    .Where(c => c.IsActive)
    .Where(c => c.Orders.Any(o => o.CreatedAt >= DateTime.UtcNow.AddDays(-90)))
    .Select(c => new {
        c.Id,
        c.Name,
        c.Email,
        TotalOrders = c.Orders.Count(),
        LatestOrder = c.Orders
            .OrderByDescending(o => o.CreatedAt)
            .Select(o => new { o.Id, o.Total, o.CreatedAt })
            .FirstOrDefault()
    })
    .OrderBy(c => c.Name)
    .ToListAsync();

Tasks:

1. Write the Prisma version. Note that Any() has no direct equivalent — decide how you handle it.
2. Write the Drizzle version using explicit joins and aggregations.
3. Add EXPLAIN ANALYZE output for your Prisma version and identify which columns need indexes.
4. Rewrite the Prisma version to use cursor pagination instead of offset pagination.
5. Add Zod validation for the raw SQL fallback if Prisma’s groupBy cannot express the query you need.

Quick Reference

Common Prisma filter operators:

Further Reading

• Prisma Client API reference — the canonical reference for all query options, filter operators, and aggregation APIs
• Prisma — Relation queries — detailed coverage of include, nested select, and how Prisma issues queries for each
• Drizzle ORM documentation — covers the full query builder API, joins, and raw SQL
• Prisma — Pagination — offset vs. cursor pagination with benchmarks and guidance on when each applies
• Use the Index, Luke — database indexing fundamentals that apply equally when diagnosing slow Prisma or Drizzle queries; the “Slow Indexes” chapter is particularly relevant