Truy vấn SQL thô với Entity Framework Core 5

Entity Framework Core 5 (EF Core) và LINQ cho phép các nhà phát triển viết các biểu thức mạnh mẽ để dịch sang SQL. Cuối cùng, ORM là cân bằng giữa sự tiện lợi và sức mạnh.

Nói chung, các tính năng mặc định của EF Core đủ để thực hiện hầu hết các tác vụ liên quan đến truy cập dữ liệu, nhưng hãy lưu ý rằng chúng tôi đã sử dụng từ “nói chung”.

Trong bài viết này, chúng ta sẽ khám phá cách chúng ta có thể làm việc với các tính năng của EF Core 5 để giao tiếp trực tiếp với lược đồ cơ sở dữ liệu (schema) của chúng ta.

Tại sao tôi muốn viết truy vấn SQL thô?

Nhiều người dùng EF Core có thể không muốn viết truy vấn SQL, và đó có thể là lý do tại sao họ chọn ORM ngay từ đầu.

Việc chuyển đổi ngữ cảnh giữa C# và SQL có thể đòi hỏi năng lượng tinh thần mà các nhà phát triển có thể muốn dành cho các nhiệm vụ phát triển khác.

Sự trừu tượng hóa LINQ của EF Core có nhiều ưu điểm và một số nhược điểm. Khi các ứng dụng của chúng ta bắt đầu ổn định và phát triển trong việc sử dụng, chúng ta có thể thấy rằng tính linh hoạt do LINQ cung cấp không còn cần thiết nữa và chi phí theo dõi đối tượng và tạo truy vấn SQL từ EF Core là ít hơn mức tối ưu.

Sau khi hoàn thành tất cả các tối ưu hóa ở các lớp LINQ và EF Core, các nhóm phát triển sẽ cân nhắc việc viết truy vấn SQL thô.

Một lý do quan trọng khác để viết truy vấn SQL thô là sử dụng chức năng chưa được hỗ trợ trong phần trừu tượng của EF Core. Khi viết bài này, EF Core không hỗ trợ JSON cho các cơ sở dữ liệu như SQL Server và PostgreSQL.

Nhóm EF Core ưu tiên các tính năng dựa trên tác động tối đa đến cộng đồng. Nói chung, các tính năng cơ sở dữ liệu thích hợp được để cho cộng đồng thực hiện hoặc hoàn toàn không được thực hiện.

Viết truy vấn SQL thô cung cấp cho các nhóm một tùy chọn để tăng hiệu suất khi họ đã sử dụng hết tất cả các tối ưu hóa EF Core 5 hoặc cách giải quyết khác mà EF Core 5 không triển khai.

Sử dụng các thực thể

EF Core 5 giúp dễ dàng sử dụng các thực thể (entity) hiện có để viết truy vấn SQL thô bằng cách sử dụng các phương thức FromSqlRaw FromSqlRawInterpolated.

var results = db.Movies
    .FromSqlRaw("select * from movies order by id desc")
    .Take(100)
    .ToList();

Các yêu cầu để viết truy vấn SQL thô bao gồm:

  • Thực thể phải có thể truy cập được thông qua một thuộc tính DbSet.
  • Thực thể phải có hình dạng cố định.
  • Tên cột phải khớp với thuộc tính thực thể.
  • Truy vấn không thể trả về dữ liệu bổ sung không phải là một phần của thực thể ban đầu.

Ưu điểm của cách tiếp cận này bao gồm các hành vi sau:

  • Khả năng theo dõi các thực thể được trả về từ một truy vấn SQL thô.
  • Thành phần của truy vấn sử dụng cú pháp LINQ.
  • Sử dụng phương thức Include để tải các dữ liệu liên quan.
  • Cách mà EF Core xử lý truy vấn SQL thô của chúng ta như một truy vấn con (sub-query).

Hãy xem ví dụ trên và những gì EF Core tạo ra trong kết quả ghi nhật ký của chúng ta.

Executed DbCommand (24ms) [Parameters=[@__p_1='100' (DbType = String)], CommandType='Text', CommandTimeout='30']
SELECT "m"."Id", "m"."Name", "m"."StreamingService"
FROM (
  select * from movies order by id desc
) AS "m"
LIMIT @__p_1

Bằng cách biến truy vấn SQL thô của chúng ta thành một truy vấn con, EF Core 5 có thể tận dụng các view SQL, các stored procedure và các hàm giá trị bảng (table value functions) trong khi cung cấp cho chúng ta khả năng sử dụng các tính năng của EF Core mà chúng ta thường sử dụng trên các thuộc tính DbSet.

Từ câu lệnh Select trên cùng, chúng ta cũng có thể thấy rằng truy vấn con của chúng ta cần trả về một hình dạng tương tự cho thực thể của chúng ta.

Mặc dù là một cách tiếp cận hấp dẫn, nhưng đây có thể không phải là trường hợp sử dụng tốt nhất cho truy vấn SQL thô.

Các thực thể không có lược đồ schema

Có các tính năng trong EF Core cho phép chúng ta ánh xạ các thực thể tới DbContext của chúng ta, không ánh xạ tới một bảng mà thay vào đó ánh xạ trực tiếp tới một truy vấn SQL thô.

Ví dụ: chúng ta có một câu lệnh SQL thô vẫn sử dụng thực thể Movies của chúng ta nhưng với một thuộc tính bổ sung là Number.

public class MovieWithNumber
{
    public int Id { get; set; }
    public int Number { get; set; }
}

Bên trong phương thức OnModelCreating được tìm thấy trong định nghĩa DbContext của chúng ta giờ đây có thể ánh xạ thực thể một cách thích hợp.

// DbSet
public DbSet<MovieWithNumber> MovieWithNumber { get; set; }

// within OnModelCreating
modelBuilder
    .Entity<MovieWithNumber>()
    .ToSqlQuery("select Id, 1 as Number from Movies order by id desc")
    .HasKey(m => m.Id);

Bây giờ vấn đề là sử dụng nó giống như chúng ta làm với bất kỳ thuộc tính DbSet nào khác .

var results = db.MovieWithNumber
    .Take(100)
    .ToList();

EF Core sẽ tạo ra truy vấn SQL thô sau.

Executed DbCommand (25ms) [Parameters=[@__p_0='100' (DbType = String)], CommandType='Text', CommandTimeout='30']
SELECT "m"."Id", "m"."Number"
FROM (
  select Id, 1 as Number from Movies order by id desc
) AS "m"
LIMIT @__p_0

Điều quan trọng cần nhớ là cách tiếp cận này có thể nguy hiểm vì đây là một Truy vấn SQL. Bất kỳ nỗ lực nào để thêm một thực thể MoviesWithNumber vào cơ sở dữ liệu của chúng ta sẽ kết thúc trong một ngoại lệ vì bảng không tồn tại.

Thực thi truy vấn SQL thô với Dapper

Dapper là một Micro-ORM dựa nhiều vào khả năng viết các truy vấn SQL của người dùng. Những gì Dapper làm tốt là lấy kết quả của một truy vấn và ánh xạ chúng tới các thực thể dựa trên một tập hợp các quy ước tên.

Hãy bắt đầu bằng cách cài đặt Dapper vào dự án của chúng ta.

dotnet add package Dapper

Chúng ta có thể sử dụng Dapper bên ngoài DbContext của chúng ta, nhưng điều đó dẫn đến một số mối quan tâm mà chúng ta có thể muốn tránh.

Đó là phải kết nối tới SQL ở bên ngoài DbContext, điều này làm giảm khả năng tái sử dụng trên ứng dụng của chúng ta.

Cách tốt nhất tôi đã tìm thấy là hiển thị kết quả truy vấn SQL của chúng ta bằng cách sử dụng một phương thức trên DbContext của chúng tôi.

public async Task<IEnumerable<int>> GetMovieIds()
{
    await using var connection = Database.GetDbConnection();
    await Database.OpenConnectionAsync();

    var results =
        await connection
            .QueryAsync<int>("select id from movies order by id desc");

    return results;
}

Ưu điểm của phương pháp tiếp cận này là chúng ta có thể sử dụng thuộc tính Database để truy xuất và mở cùng một kết nối mà ngữ cảnh EF Core của chúng ta đang sử dụng, do đó giảm nhu cầu về cơ chế tạo DbConnection.

Bây giờ chúng ta có thể gọi phương thức từ cơ sở mã của chúng ta trực tiếp từ thể hiện của chúng ta.

var db = new Database();
var results = await db.GetMovieIds();

Đối với những người không muốn nhận thêm “một sự phụ thuộc”, cũng có tùy chọn sử dụng ADO.NET trực tiếp.

await using var connection = Database.GetDbConnection().CreateCommand();

Về mặt cá nhân, việc ánh xạ các kết quả SQL tới một thực thể là đủ khó để so với việc phụ thuộc vào Dapper.

Phần kết luận

EF Core là một ORM mạnh mẽ, nhưng cũng giống như nhiều ORM khác, nó không hoàn hảo. EF Core 5 không hỗ trợ tất cả các tính năng của công cụ lưu trữ cơ bản của chúng ta.

SQL là một công cụ mạnh mẽ mà chúng ta không nên bỏ qua chỉ vì chúng ta thích viết C# hơn. Chúng ta đã xem qua một số cách sử dụng truy vấn SQL thô và nhận thấy rằng EF Core 5 đã đáp ứng được nhu cầu này thông qua các phương pháp cấu hình và mở rộng.

Chúng ta có thể kết hợp các cơ chế truy cập dữ liệu khác với EF Core để cung cấp cho người dùng trải nghiệm hiệu quả nhất. Chúng ta đã thấy việc tích hợp Dapper với quyền truy cập dữ liệu EF Core của chúng ta đơn giản như thế nào.

Các nhà phát triển có thể đọc thêm về các truy vấn SQL thô tại trang tài liệu chính thức của EF Core.

Cảm ơn bạn đã đọc, và hãy cho tôi biết suy nghĩ của bạn trong phần bình luận.

Nếu Comdy hữu ích và giúp bạn tiết kiệm thời gian làm việc

Bạn có thể vui lòng đưa Comdy vào whitelist của trình chặn quảng cáo ❤️ để hỗ trợ chúng tôi trong việc trả tiền cho dịch vụ lưu trữ web để duy trì hoạt động của trang web.

Entity Framework CoreLINQLập Trình C#
Bài Viết Liên Quan:
Bộ chuyển đổi giá trị của Entity Framework Core 5
Trung Nguyen 12/11/2021
Bộ chuyển đổi giá trị của Entity Framework Core 5

Bài viết này sẽ giúp bạn tìm hiểu về bộ chuyển đổi giá trị - một trong những cải tiến mạnh mẽ của Entity Framework Core 5.

Cách thêm view vào DbContext trong Entity Framework Core
Trung Nguyen 07/11/2021
Cách thêm view vào DbContext trong Entity Framework Core

Với EF Core 5, chúng ta có thể thêm view vào trong DbContext và tạo view trong database bằng cách sử dụng cơ chế chuyển đổi cơ sở dữ liệu tích hợp sẵn.

Mô hình hóa các mối quan hệ SQL trong EF Core
Trung Nguyen 03/11/2021
Mô hình hóa các mối quan hệ SQL trong EF Core

Bài viết này sẽ khám phá các mối quan hệ trong cơ sở dữ liệu quan hệ và cách mô hình hóa các mối quan hệ đó bằng cách tiếp cận Code First trong EF Core.

Dữ liệu đệ quy với Entity Framework Core và SQL Server
Trung Nguyen 01/11/2021
Dữ liệu đệ quy với Entity Framework Core và SQL Server

Trong bài viết này, chúng ta sẽ khám phá cách lập mô hình dữ liệu đệ quy với Entity Framework Core và SQL Server