Entity Framework Core sinh SQL thay bạn, nhưng nó không sinh index, không đọc execution plan, và không biết rằng WHERE Code = @p đang so sánh varchar với nvarchar nên index bị bỏ qua. SQL và cơ sở dữ liệu quan hệ vẫn là nền tảng bắt buộc trong lộ trình .NET, kể cả khi bạn không viết một câu SELECT nào bằng tay.
Bài này đủ để bạn đọc truy vấn của ORM, thêm đúng index, hiểu vì sao transaction treo, và biết khi nào denormalize là quyết định đúng.
Mục lục
Cập nhật 2026
- SQL Server 2025 có kiểu
jsonnative và kiểuvector. EF Core 10 map complex type sang cột JSON bằngToJson(), và hỗ trợ vector search quaSqlVector<float>cùngEF.Functions.VectorDistance. - EF Core 10 là bản LTS: migration bundle, script idempotent, và
ExecuteUpdateAsyncchạy trực tiếp xuống database, không qua change tracker. - PostgreSQL 17/18 vẫn là lựa chọn mặc định cho team muốn
jsonb, GIN index vàpgvector; cú phápEXPLAIN (ANALYZE, BUFFERS)là công cụ đọc plan chuẩn. - Phần lớn lỗi production nằm ở index, isolation level và N+1 — trọng tâm dưới đây.
Mô hình quan hệ: nghĩ theo tập, không theo vòng lặp
SQL là ngôn ngữ declarative: bạn mô tả kết quả, optimizer chọn cách thực hiện. Điều này có hệ quả thực dụng — viết truy vấn theo kiểu “duyệt từng bản ghi rồi hỏi thêm” thường là dấu hiệu bạn đang dùng database như một hash map từ xa, và trả giá bằng round trip.
Schema tối thiểu dùng trong bài:
CREATE TABLE dbo.Customers (
CustomerId int IDENTITY PRIMARY KEY,
Email nvarchar(256) NOT NULL,
DisplayName nvarchar(100) NOT NULL,
CreatedAt datetime2(0) NOT NULL CONSTRAINT DF_Customers_Created DEFAULT SYSUTCDATETIME(),
CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);
CREATE TABLE dbo.Orders (
OrderId bigint IDENTITY PRIMARY KEY,
CustomerId int NOT NULL REFERENCES dbo.Customers(CustomerId),
Status tinyint NOT NULL,
Total decimal(18,2) NOT NULL,
CreatedAt datetime2(0) NOT NULL
);
Trên PostgreSQL, thay IDENTITY bằng GENERATED ALWAYS AS IDENTITY và nvarchar bằng text; phần còn lại của bài giữ nguyên logic.
JOIN và GROUP BY: hai lỗi hay gặp
SELECT c.DisplayName,
COUNT(o.OrderId) AS OrderCount,
COALESCE(SUM(o.Total), 0) AS Revenue
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON o.CustomerId = c.CustomerId
AND o.CreatedAt >= '2026-01-01'
WHERE c.CreatedAt >= '2024-01-01'
GROUP BY c.CustomerId, c.DisplayName
HAVING COUNT(o.OrderId) >= 2
ORDER BY Revenue DESC;
Lỗi thứ nhất: đặt điều kiện của bảng bên phải trong WHERE thay vì ON. Với LEFT JOIN, WHERE o.CreatedAt >= ... loại bỏ các dòng có o là NULL — truy vấn âm thầm trở thành INNER JOIN. Điều kiện lọc bảng phải nằm trong ON; điều kiện lọc bảng trái nằm trong WHERE.
Lỗi thứ hai: nhầm WHERE với HAVING. WHERE lọc trước khi gom nhóm, HAVING lọc sau khi gom nhóm và có thể dùng aggregate. Đặt COUNT(...) vào WHERE sẽ báo lỗi, còn đặt điều kiện không aggregate vào HAVING thì chạy được nhưng chậm hơn vì gom nhóm trước rồi mới lọc.
Khi cần “đơn hàng mới nhất của mỗi khách”, window function gọn hơn correlated subquery:
WITH Ranked AS (
SELECT o.OrderId, o.CustomerId, o.Total, o.CreatedAt,
ROW_NUMBER() OVER (PARTITION BY o.CustomerId ORDER BY o.CreatedAt DESC) AS rn
FROM dbo.Orders AS o
)
SELECT CustomerId, OrderId, Total, CreatedAt
FROM Ranked
WHERE rn = 1;
Index: cấu trúc, thứ tự cột và covering
Index B-tree trong SQL Server và PostgreSQL đều là cây cân bằng: tra cứu O(log n), duyệt range hiệu quả vì các lá nằm theo thứ tự khóa. Khác biệt quan trọng nhất với người viết truy vấn là clustered index (SQL Server): bảng được lưu vật lý theo khóa clustered, thường là primary key. Mọi nonclustered index đều trỏ tới clustered key, nên clustered key càng rộng thì index phụ càng lớn.
Ba quy tắc đủ để giải quyết phần lớn trường hợp:
- Thứ tự cột trong composite index quyết định index có dùng được hay không. Index
(CustomerId, CreatedAt)phục vụ truy vấn lọc theoCustomerId, hoặcCustomerId+ rangeCreatedAt. Nó không phục vụ truy vấn chỉ lọcCreatedAt. Đây là “leftmost prefix” — cột equality đứng trước, cột range đứng sau. - Covering index tránh key lookup. Nếu truy vấn chỉ cần vài cột ngoài khóa, đưa chúng vào
INCLUDEđể index chứa đủ dữ liệu. - Foreign key không tự động có index. Cả SQL Server và PostgreSQL đều không tạo index cho cột FK. Thiếu index này làm cả truy vấn join lẫn
DELETEtrên bảng cha chậm.
-- SQL Server: seek theo CustomerId, sort sẵn theo CreatedAt, cover cột Total
CREATE INDEX IX_Orders_Customer_CreatedAt
ON dbo.Orders (CustomerId, CreatedAt DESC)
INCLUDE (Total, Status);
-- PostgreSQL: cùng ý tưởng
CREATE INDEX ix_orders_customer_created_at
ON orders (customer_id, created_at DESC)
INCLUDE (total, status);
Mỗi index thêm chi phí ghi cho INSERT/UPDATE/DELETE; chỉ thêm khi có plan chứng minh.
Hai biến thể nên biết: filtered index (SQL Server) và partial index (PostgreSQL) chỉ index một phần bảng, rất hữu ích cho cột có nhiều NULL hoặc trạng thái hiếm:
CREATE INDEX IX_Orders_Pending
ON dbo.Orders (CreatedAt)
WHERE Status = 0; -- chỉ đơn hàng đang chờ
Đọc execution plan thay vì đánh giá bằng cảm giác
SQL Server: bật actual execution plan trong SSMS, hoặc dùng thống kê IO để biết truy vấn đọc bao nhiêu page:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CustomerId, COUNT(*) FROM dbo.Orders WHERE CreatedAt >= '2026-01-01' GROUP BY CustomerId;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
PostgreSQL:
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, COUNT(*) FROM orders WHERE created_at >= '2026-01-01' GROUP BY customer_id;
Đọc plan theo thứ tự sau:
- Scan hay Seek?
Table Scan/Seq Scantrên bảng lớn với predicate chọn ít dòng là dấu hiệu thiếu index hoặc index không dùng được. - Estimated rows so với actual rows. Lệch vài bậc độ lớn nghĩa là statistics cũ hoặc predicate không sargable. Cập nhật statistics (
UPDATE STATISTICS,ANALYZE) trước khi kết luận. - Key lookup / RID lookup. Nhiều lookup nghĩa là index thiếu cột cover — thêm
INCLUDE. - Sort và spill.
Sortwarning hoặcexternal merge disktrong PostgreSQL nghĩa làwork_memkhông đủ; đôi khi sửa bằng index có thứ tự đúng. - Implicit conversion. SQL Server hiển thị
CONVERT_IMPLICITkhi so sánhvarcharvớinvarchar. Index trên cộtvarcharsẽ không seek được. Đây là lỗi cực phổ biến khi cột code làvarcharcòn tham số EF Core gửi xuống lànvarchar.
Transaction và isolation: cái giá của “đọc thấy dữ liệu mới nhất”
ACID không phải một công tắc. Mỗi isolation level đổi một phần tính chất để lấy concurrency. Hiểu mức nào đang chạy là điều kiện để debug deadlock và lost update.
| Mức | Hành vi thực tế | Khi nào |
|---|---|---|
| Read Committed (mặc định SQL Server) | Đọc không thấy dữ liệu chưa commit; dùng lock chia sẻ nên writer có thể bị block | Mặc định của hầu hết ứng dụng OLTP |
| Read Committed Snapshot (RCSI) | Đọc lấy bản đã commit tại thời điểm câu lệnh bắt đầu, không block writer | Bật khi đọc nhiều, ghi vừa; tốn version store trong tempdb |
| Snapshot | Cả transaction thấy một ảnh nhất quán | Báo cáo, đọc nhiều bảng cần nhất quán |
| Serializable | Như tuần tự; PostgreSQL dùng SSI và có thể abort | Nghiệp vụ tài chính, tồn kho; phải retry |
SQL Server mặc định là Read Committed có lock. Nhiều team tưởng “đọc không block” là mặc định vì database của họ đã bật RCSI:
ALTER DATABASE Shop
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
PostgreSQL mặc định Read Committed nhưng triển khai bằng MVCC: reader không block writer và writer không block reader. Cái giá là VACUUM và bloat — một chi tiết vận hành, không phải chi tiết cú pháp.
Lost update: đừng đọc rồi ghi
-- Sai: hai request cùng đọc 10, cùng ghi 9
UPDATE dbo.Products SET Stock = 9 WHERE ProductId = 42;
-- Đúng: điều kiện nằm trong chính câu UPDATE, atomic
UPDATE dbo.Products
SET Stock = Stock - 1
WHERE ProductId = 42 AND Stock >= 1;
Nếu logic phức tạp hơn một phép trừ, dùng optimistic concurrency với rowversion trong SQL Server hoặc xmin trong PostgreSQL, và xử lý DbUpdateConcurrencyException bằng cách tải lại rồi thử lại. Với EF Core:
// File: Data/Product.cs
public sealed class Product
{
public int Id { get; set; }
public int Stock { get; set; }
public byte[] RowVersion { get; set; } = []; // rowversion, map IsRowVersion()
}
EF Core thêm WHERE RowVersion = @old vào câu UPDATE; nếu 0 dòng bị ảnh hưởng, nó ném exception thay vì ghi đè. Đây là cách rẻ nhất để tránh lost update mà không cần khóa bi quan.
Deadlock
Deadlock xảy ra khi hai transaction giữ lock theo thứ tự ngược nhau. SQL Server chọn một victim và trả lỗi 1205; PostgreSQL abort với SQLSTATE 40P01. Không có cấu hình nào loại bỏ hoàn toàn deadlock — chỉ giảm xác suất và retry đúng.
- Giữ transaction ngắn: không gọi HTTP, không gửi email, không chờ người dùng trong lúc transaction mở.
- Thứ tự truy cập nhất quán: luôn cập nhật bảng A rồi B, không có ngoại lệ.
- Index đúng predicate: scan toàn bảng trong transaction giữ nhiều lock hơn seek.
- Retry với backoff: bắt đúng mã lỗi, không retry mọi
DbException.
EnableRetryOnFailure của EF Core được thiết kế cho lỗi transient (mất kết nối, timeout), không phải để che logic deadlock. Nếu bạn muốn retry deadlock, viết execution strategy riêng hoặc bắt lỗi ở tầng repository, và đảm bảo thao tác là idempotent.
SQL injection: parameter hóa, không nối chuỗi
// ĐÚNG: tham số hóa
var customer = await db.Customers
.FromSqlInterpolated($"SELECT * FROM dbo.Customers WHERE Email = {email}")
.SingleOrDefaultAsync(ct);
// ĐÚNG với Dapper
var rows = await connection.QueryAsync<Customer>(
"SELECT * FROM dbo.Customers WHERE Email = @Email", new { Email = email });
// SAI: nối chuỗi, đầu vào do người dùng kiểm soát
var sql = $"SELECT * FROM dbo.Customers WHERE Email = '{email}'";
await db.Database.ExecuteSqlRawAsync(sql);
FromSqlInterpolated biến nội suy thành DbParameter. FromSqlRaw chỉ an toàn khi bạn tự truyền SqlParameter hoặc khi chuỗi là hằng số. Tên bảng, tên cột và ORDER BY không thể parameter hóa — nếu chúng đến từ input, dùng whitelist, tuyệt đối không nối chuỗi.
ORM giúp mặc định an toàn, nhưng không cứu được ExecuteSqlRaw với string ghép. Quy tắc review: bất kỳ Raw nào có dấu $ hoặc + đều phải giải trình.
Chuẩn hóa hay denormalize?
Mặc định là 3NF: mỗi fact nằm một chỗ, cập nhật một chỗ. Denormalize khi read pattern rõ ràng và write pattern chấp nhận được:
- Đúng: thêm cột
OrderTotalđã tính sẵn vào bảngOrdersđể tránh join 3 bảng mỗi lần hiển thị danh sách. Write path cập nhật cùng transaction, có test. - Đúng: materialized view (PostgreSQL) hoặc indexed view (SQL Server) cho báo cáo, refresh theo lịch.
- Sai: copy
CustomerNamevàoOrdersmà không có trigger/job đồng bộ, rồi tranh luận vì sao báo cáo lệch.
Quyết định dựa trên tỉ lệ đọc/ghi và yêu cầu nhất quán, không dựa trên “bảng này nhìn đẹp hơn”. Denormalize là nợ kỹ thuật có lãi suất: nếu không trả, dữ liệu sẽ lệch âm thầm.
Migration: sinh script, review, rồi mới chạy
dotnet ef migrations add AddOrdersCustomerIndex --project src/Shop.Data
dotnet ef migrations script --idempotent --output sql/2026-11-orders-index.sql
dotnet ef database update --project src/Shop.Data
Ba quy tắc vận hành:
- Review SQL sinh ra, không chỉ review file C#. EF Core có thể sinh
DROP/ADDcho rename cột, và trên bảng lớn điều đó nghĩa là mất dữ liệu hoặc khóa lâu. - Không chạy migration tự động lúc khởi động khi có nhiều instance. Hai pod cùng chạy
Migrate()sẽ đánh nhau; dùng job deploy riêng, chạy một lần. - Expand-contract cho thay đổi phá vỡ. Thêm cột nullable → backfill theo batch → deploy code đọc/ghi cột mới → sau vài ngày mới xóa cột cũ. Đây là cách duy nhất để zero-downtime mà không cần cửa sổ bảo trì.
Trên bảng lớn, thêm cột và tạo index có thể khóa ghi; đo trên staging với dữ liệu cùng kích thước.
Chọn hệ quản trị: bảng quyết định ngắn
| Bối cảnh | Lựa chọn | Lưu ý |
|---|---|---|
Stack Microsoft, cần json/vector native, tooling quen |
SQL Server 2025 | Licensing; RCSI và isolation cần hiểu rõ |
Open source, jsonb, extension, triển khai Linux |
PostgreSQL 17/18 | VACUUM/bloat; connection pooling với PgBouncer |
| Test, desktop, mobile, dữ liệu một file | SQLite | Một writer; không dùng cho tải ghi đồng thời cao |
Sai lầm thường gặp
SELECT *trong production. Kéo thêm cột phá covering index và làm payload phình ra; liệt kê cột cụ thể.- So sánh cột với hàm.
WHERE YEAR(CreatedAt) = 2026không sargable; viết range>= '2026-01-01' AND < '2027-01-01'. - Transaction bao quanh lời gọi HTTP. Giữ lock trong khi chờ mạng là công thức deadlock và timeout.
- Chạy migration tự động ở startup với nhiều instance. Dùng job riêng; xem mục trên.
2027: vector và JSON thành kỹ năng cơ bản
EF Core 11 đi cùng .NET 11 (GA tháng 11/2026, STS) và tiếp tục mở rộng truy vấn vector, JSON. Điều không đổi: index, plan và isolation vẫn quyết định ứng dụng chạy nhanh hay treo.
Checklist
- Bật logging SQL của EF Core ở môi trường dev và đếm query cho một request điển hình.
- Mỗi bảng có FK đều có index trên cột FK; kiểm tra bằng query system view, không bằng mắt.
- Đọc plan của 5 truy vấn chậm nhất trước khi thêm bất kỳ index nào.
- Biết database đang chạy isolation nào; nếu là SQL Server, xác nhận RCSI bật hay tắt.
- Mọi
UPDATEnghiệp vụ có điều kiện trong câu lệnh hoặc rowversion, không đọc-rồi-ghi. - Migration chạy bằng script review được, không tự động ở startup khi có nhiều instance.
Đi tiếp: Chọn cơ sở dữ liệu phù hợp: SQL Server, PostgreSQL hay MySQL mở rộng bảng quyết định ở trên. Làm chủ EF Core Migrations đi sâu vào expand-contract và bundle. Khi đã hiểu index và plan, bài Stored Procedures, Constraints & Triggers là bước tiếp theo về phía database.



