Sử dụng Stored Procedures và Functions Với EF Core và PostgreSQL

Bạn đang xây dựng một ứng dụng .NET với EF Core. Hầu hết các truy vấn của bạn hoạt động tốt với LINQ, nhưng giờ đây bạn gặp phải các tình huống cần một giải pháp mạnh mẽ hơn.

Có thể bạn có một báo cáo phức tạp kết hợp năm bảng với các phép tổng hợp và hàm cửa sổ. Truy vấn LINQ của bạn tạo ra SQL chậm hơn mong đợi, và bạn biết bạn có thể viết SQL tốt hơn bằng tay.

Hoặc có thể bạn cần cập nhật kho với khóa phù hợp để ngăn chặn điều kiện tranh chấp. Bạn có thể quản lý giao dịch và khóa rõ ràng trong C#, nhưng cảm giác như bạn đang chống lại framework.

Đây là điều thường xảy ra: bạn tìm kiếm “EF Core stored procedures” và thấy những lời khuyên mâu thuẫn. Một số bài viết nói tránh sử dụng SQL thô bằng mọi giá. Số khác đề nghị bỏ EF hoàn toàn và viết ADO.NET. Cả hai đều không cảm thấy đúng.

Thực tế, EF Core hoạt động rất tốt với các hàm và thủ tục cơ sở dữ liệu. Bạn có được sức mạnh của cơ sở dữ liệu cho những gì nó làm tốt nhất, và sự tiện lợi của EF cho mọi thứ khác. Hãy để tôi chỉ cho bạn cách thức hoạt động thực sự của điều này.

Tôi sẽ sử dụng PostgreSQL cho các ví dụ, nhưng các nguyên tắc tương tự áp dụng cho SQL Server và các cơ sở dữ liệu quan hệ khác.

Khi Nào Bạn Nên Sử Dụng SQL Thô?

Hãy thành thật: hầu hết thời gian, LINQ là đủ tốt. EF Core dịch C# của bạn thành SQL khá tốt, và bạn có được tính an toàn kiểu và hỗ trợ tái cấu trúc.

Nhưng có những lúc SQL thô có ý nghĩa hơn:

  • Bạn cần hiệu suất mà bạn không thể nhận được từ LINQ. Các phép tổng hợp phức tạp với nhiều phép kết hợp, hàm cửa sổ hoặc các truy vấn báo cáo thường chạy nhanh hơn khi được viết trực tiếp bằng SQL. Bạn có thể kiểm tra và điều chỉnh truy vấn trong công cụ cơ sở dữ liệu trước khi đưa vào code.
  • Bạn đang sử dụng các tính năng đặc thù cơ sở dữ liệu. PostgreSQL có các khả năng mạnh mẽ như tìm kiếm toàn văn, toán tử JSON và biểu thức bảng chung (CTEs) mà không phải lúc nào cũng có các tương đương LINQ sạch sẽ. Đôi khi con đường thẳng nhất chỉ là viết SQL.
  • Bạn có logic cơ sở dữ liệu hiện có. Nếu cơ sở dữ liệu của bạn đã có các stored procedures và functions (có thể từ hệ thống kế thừa), gọi chúng trực tiếp tốt hơn việc viết lại mọi thứ trong C#.
  • Bạn cần các thao tác nguyên tử với khóa phù hợp. Một stored procedure phối hợp nhiều cập nhật với khóa FOR UPDATE đơn giản và an toàn hơn việc cố gắng quản lý điều đó từ code ứng dụng.
  • Bạn muốn giảm số lượt truy vấn. Một lời gọi hàm tổng hợp dữ liệu từ năm bảng hiệu quả hơn năm truy vấn LINQ riêng biệt.

Bây giờ hãy xem cách thực hiện điều này.

Ví Dụ 1: Hàm Scalar Đơn Giản

Đây là một hàm đơn giản cho bạn biết còn bao nhiêu vé:


CREATE OR REPLACE FUNCTION ticketing.tickets_left(p_ticket_type_id uuid)
RETURNS numeric
LANGUAGE sql
AS $$
SELECT tt.available_quantity
FROM ticketing.ticket_types tt
WHERE tt.id = p_ticket_type_id
$$;

Không có gì đặc biệt, chỉ là một truy vấn được bọc trong hàm.

Gọi nó từ EF Core rất đơn giản:


app.MapGet("ticket-types/{ticketTypeId}/available-quantity",
async (Guid ticketTypeId, EventManagementContext dbContext) =>
{
var result = await dbContext.Database.SqlQuery<int>(
$"""
SELECT ticketing.tickets_left({ticketTypeId}) AS "Value"
""")
.FirstAsync();

return Results.Ok(result);
});

Chú ý bí danh AS "Value". Khi EF Core ánh xạ đến một kiểu nguyên thủy, nó mong đợi một thuộc tính có tên Value. Dấu ngoặc kép bảo toàn chính xác kiểu chữ (PostgreSQL mặc định chuyển đổi các định danh không có ngoặc kép thành chữ thường).

Cú pháp chuỗi nội suy ($"{ticketTypeId}") có thể trông nguy hiểm, nhưng EF Core tự động chuyển đổi điều này thành một truy vấn có tham số. Bạn không xây dựng chuỗi SQL, bạn đang sử dụng phép nội suy C# như một cú pháp tiện lợi cho các tham số.

Ví Dụ 2: Table-Valued Function

Các hàm có thể trả về toàn bộ tập kết quả, đây là nơi chúng thực sự tỏa sáng:


CREATE OR REPLACE FUNCTION ticketing.customer_order_summary(p_customer_id uuid)
RETURNS TABLE (
order_id uuid,
created_at_utc timestamptz,
total_price numeric,
currency text,
item_count numeric
)
LANGUAGE sql
AS $$
SELECT
o.id,
o.created_at_utc,
o.total_price,
o.currency,
COALESCE(SUM(oi.quantity), 0) AS item_count
FROM ticketing.orders o
LEFT JOIN ticketing.order_items oi ON oi.order_id = o.id
WHERE o.customer_id = p_customer_id
GROUP BY o.id, o.created_at_utc, o.total_price, o.currency
ORDER BY o.created_at_utc DESC
$$;

Hàm này kết hợp các đơn hàng với các mục của chúng, tổng hợp số lượng và trả về nhiều hàng. Bạn có thể viết điều này bằng LINQ, nhưng SQL rõ ràng hơn và bạn có thể kiểm tra trực tiếp trong công cụ cơ sở dữ liệu.

Để sử dụng nó từ C#, tạo một DTO khớp với đầu ra của hàm:


public class OrderSummaryDto
{
public Guid OrderId { get; set; }
public DateTime CreatedAtUtc { get; set; }
public decimal TotalPrice { get; set; }
public string Currency { get; set; }
public int ItemCount { get; set; }
}

Sau đó truy vấn hàm như bất kỳ bảng nào khác:


app.MapGet("customers/{customerId}/order-summary",
async (Guid customerId, EventManagementContext dbContext) =>
{
var orders = await dbContext.Database
.SqlQuery<OrderSummaryDto>(
$"""
SELECT
order_id AS OrderId,
created_at_utc AS CreatedAtUtc,
total_price AS TotalPrice,
currency AS Currency,
item_count AS ItemCount
FROM ticketing.customer_order_summary({customerId})
""")
.ToListAsync();

return Results.Ok(orders);
});

Chìa khóa là ánh xạ tên cột đến các thuộc tính DTO của bạn bằng cách sử dụng bí danh. EF Core tự động xử lý phần còn lại.

Hiểu Sự Khác Biệt Giữa PostgreSQL Functions và Procedures

PostgreSQL phân biệt giữa functions và procedures theo những cách quan trọng:

  • Functions được thiết kế để trả về giá trị. Chúng có thể trả về giá trị scalar, bảng hoặc thậm chí các đối tượng JSON phức tạp. Bạn gọi chúng với SELECT và có thể sử dụng chúng trong các truy vấn như bất kỳ biểu thức nào khác. Functions chạy trong một giao dịch và có thể được sử dụng trong các mệnh đề WHERE, phép kết hợp và các ngữ cảnh truy vấn khác.
  • Procedures được thiết kế cho hiệu ứng phụ. Chúng không trả về giá trị trực tiếp nhưng có thể sửa đổi dữ liệu và có tham số OUT. Bạn gọi chúng với CALL và chúng lý tưởng cho các thao tác phức tạp cần quản lý giao dịch rõ ràng hoặc thực hiện nhiều cập nhật liên quan.

Hãy nghĩ theo cách này: sử dụng functions khi bạn cần dữ liệu, sử dụng procedures khi bạn cần thay đổi điều gì đó.

Sự phân biệt này quan trọng vì nó ảnh hưởng đến cách bạn thiết kế logic cơ sở dữ liệu và cách bạn gọi các thủ tục này từ C#.

Ví Dụ 3: Stored Procedure với Validation

Đây là nơi procedures thực sự chứng minh giá trị của chúng. Giả sử bạn cần điều chỉnh kho vé, nhưng bạn muốn ngăn chặn điều kiện tranh chấp và xác thực thao tác:


CREATE OR REPLACE PROCEDURE ticketing.adjust_available_quantity(
p_ticket_type_id uuid,
p_delta numeric,
p_reason text DEFAULT 'manual-adjust'
)
LANGUAGE plpgsql
AS $$
DECLARE
v_qty numeric;
v_avail numeric;
v_new_avail numeric;
BEGIN
SELECT quantity, available_quantity
INTO v_qty, v_avail
FROM ticketing.ticket_types
WHERE id = p_ticket_type_id
FOR UPDATE;

IF NOT FOUND THEN
RAISE EXCEPTION 'ticket_type % not found', p_ticket_type_id;
END IF;

v_new_avail := v_avail + p_delta;

IF v_new_avail < 0 THEN
RAISE EXCEPTION 'Cannot reduce below zero';
END IF;

IF v_new_avail > v_qty THEN
RAISE EXCEPTION 'Cannot exceed quantity';
END IF;

UPDATE ticketing.ticket_types
SET available_quantity = v_new_avail
WHERE id = p_ticket_type_id;
END;
$$;

Thủ tục này làm một số điều quan trọng:

  • Khóa hàng với FOR UPDATE để không giao dịch nào khác có thể sửa đổi nó cho đến khi chúng ta hoàn thành
  • Xác thực quy tắc nghiệp vụ trước khi thực hiện thay đổi
  • Cung cấp thông báo lỗi rõ ràng khi có điều gì đó sai sót
  • Giữ mọi thứ nguyên tử trong một lượt truy vấn cơ sở dữ liệu duy nhất

Bạn có thể làm tất cả điều này trong C# với quản lý giao dịch thủ công và khóa rõ ràng, nhưng nó phức tạp hơn và dễ xảy ra lỗi. Hãy để cơ sở dữ liệu xử lý những gì nó làm tốt.

Đây là cách bạn gọi nó từ EF Core:


app.MapPut("ticket-types/{ticketTypeId}/available-quantity", async (
Guid ticketTypeId,
int quantity,
EventManagementContext dbContext) =>
{
try
{
await dbContext.Database.ExecuteSqlAsync(
$"""
CALL ticketing.adjust_available_quantity({ticketTypeId},{quantity})
""");

return Results.Ok(result);
}
catch (Exception e)
{
return Results.BadRequest(e.Message);
}
});

Thủ tục không trả về giá trị, nhưng nếu nó tạo ra một ngoại lệ (với RAISE EXCEPTION), PostgreSQL sẽ truyền điều đó đến code C# của bạn. Bạn có thể bắt nó và trả về phản hồi lỗi phù hợp.

Về SQL Injection (Đừng Hoảng Sợ)

Bạn có thể nhìn vào những chuỗi nội suy đó và nghĩ “khoan đã, đây không phải là SQL injection sắp xảy ra sao?”

Không phải vậy.

Khi bạn viết:


$"SELECT * FROM users WHERE id = {userId}"

EF Core không nối chuỗi. Nó chuyển đổi điều này thành:


SELECT * FROM users WHERE id = @p0

Giá trị thực tế được gửi dưới dạng tham số, hoàn toàn tách biệt với văn bản SQL. Điều này hoạt động cho tất cả các ví dụ trong bài viết này.

Cú pháp nội suy chỉ là một cách thuận tiện để viết các truy vấn có tham số.

Một Lời Nhanh về Views

Database views giống như các hàm không có tham số. Chúng là các truy vấn đã lưu mà bạn có thể tham chiếu bằng tên.

Bạn có thể truy vấn chúng bằng SqlQuery<T> giống như các hàm:


var results = await dbContext.Database
.SqlQuery<ActiveCustomerDto>(
$"SELECT * FROM ticketing.active_customers")
.ToListAsync();

Hoặc bạn có thể ánh xạ chúng đến các kiểu thực thể trong DbContext của bạn để có hỗ trợ LINQ đầy đủ.

Views tuyệt vời cho các truy vấn thường xuyên sử dụng không cần tham số. Functions cung cấp cho bạn sự linh hoạt của tham số hóa.

Kết Luận

Chúng ta đã đề cập đến cách sử dụng PostgreSQL functions và procedures với EF Core, từ các hàm scalar đơn giản đến các thủ tục phức tạp với xác thực và khóa.

Bạn đã học khi nào nên sử dụng functions (khi bạn cần dữ liệu trả về) so với procedures (khi bạn cần sửa đổi dữ liệu). Bạn đã thấy cách SqlQuery<T> và ExecuteSqlAsync của EF Core cung cấp cho bạn tính an toàn kiểu trong khi cho phép bạn viết SQL bạn cần. Và bạn đã học khi nào SQL thô có ý nghĩa: các phép tổng hợp phức tạp, các tính năng đặc thù cơ sở dữ liệu, các thao tác nguyên tử với khóa và giảm số lượt truy vấn.

EF Core không buộc bạn phải lựa chọn giữa LINQ và SQL thô. Bạn có thể sử dụng cả hai.

Sử dụng functions khi bạn cần trả về dữ liệu, procedures khi bạn cần sửa đổi dữ liệu với logic phức tạp và các truy vấn SQL thô khi LINQ không nắm bắt các yêu cầu của bạn một cách hiệu quả. Sự kết hợp tiện lợi của EF Core và sức mạnh của cơ sở dữ liệu cung cấp cho bạn sự linh hoạt để chọn đúng công cụ cho mỗi tình huống.

Recent News Articles

Fresh job related news content posted each day.

Mục lục

→ Chỉ mục