Dữ liệu đệ quy với Entity Framework Core và SQL Server

Một trong những tính năng mạnh mẽ nhất của công cụ cơ sở dữ liệu quan hệ là khả năng cho các bảng có thể tự tham chiếu. Việc có các dòng tham chiếu các dòng khác trong cùng một bảng cho phép chúng ta tạo cấu trúc cây và cấu trúc phân cấp phức tạp với nỗ lực tối thiểu.

Trong bài viết này, chúng ta sẽ khám phá cách lập mô hình một thực thể tự tham chiếu với Entity Framework Core (EF Core) và sau đó tạo một phương thức trợ giúp cho phép chúng ta lấy tất cả dữ liệu con cháu bắt đầu từ một cấp cụ thể.

Để làm được điều này, các nhà phát triển sẽ cần một phiên bản đang chạy của Microsoft SQL Server.

Mô hình cơ sở dữ liệu đệ quy

Chúng ta sẽ bắt đầu với mô hình đệ quy quen thuộc nhất mà mọi người đều biết và đó là sơ đồ tổ chức của một công ty. Với Entity Framework Core, tất cả những gì chúng ta cần làm là xác định các thuộc tính điều hướng của chúng ta.

Nếu bạn cần hướng dẫn về cách xác định mối quan hệ, bạn đọc bài viết trên blog trước đây của tôi về mô hình hóa tất cả các mối quan hệ với EF Core.

Chúng ta biết rằng nhân viên có người quản lý và người quản lý có nhân viên báo cáo.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Title { get; set; }
    public Employee Manager { get; set; }
    public int? ManagerId { get; set; }
    public List<Employee> Reports { get; set; }
}

Hãy tạo một số dữ liệu trong EF Core DbContext của chúng ta bằng cách sử dụng sơ đồ tổ chức của Nhà máy điện hạt nhân Springfield.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
        .HasData(new List<Employee>()
        {
            // org chart
            // https://simpsons.fandom.com/wiki/Springfield_Nuclear_Power_Plant
            new() {Id = 1, Name = "Charles Montgomery Burns", Title = "Owner"},
            new() {Id = 2, Name = "Waylon Smithers, Jr.", Title = "Assistant", ManagerId = 1},
            new() {Id = 3, Name = "Lenny Leonard", Title = "Technical Supervisor", ManagerId = 2},
            new() {Id = 4, Name = "Carl Carlson", Title = "Safety Operations Supervisor", ManagerId = 2},
            new() {Id = 5, Name = "Inanimate Carbon Rod", Title = "Rod", ManagerId = 4},
            new() {Id = 6, Name = "Homer Simpson", Title = "Safety Inspector", ManagerId = 5}
        });
}

Bây giờ chúng ta đã sẵn sàng, hãy nói về truy vấn mà chúng ta muốn thực thi đối với tập dữ liệu của chúng ta.

Với mã định danh của nhân viên (id), chúng ta muốn xem tất cả các cấp nhân viên báo cáo bên dưới, từ bất kể vị trí quản lý nào.

Nếu chúng ta truy vấn bằng cách sử dụng DbContext, chúng ta sẽ chỉ nhận được một cấp báo cáo. Cách tiếp cận mặc định sẽ tốn kém và lãng phí.

var employee = db.Employees
    .Include(e => e.Reports)
    .Where(e => e.Id = id)
    .ToList();

Phải có một cách tốt hơn, phải không ? Tất nhiên là có. Giải pháp là sử dụng Biểu thức bảng chung (Common Table Expressions).

Biểu thức bảng chung (CTE)

Common Table Expressions (CTE) là một cơ chế có sẵn trong hầu hết các cơ sở dữ liệu quan hệ, nơi các nhà phát triển có thể định nghĩa tập kết quả tạm thời và tham khảo chúng trong phạm vi thực hiện một câu lệnh Select, Insert, Update hoặc Delete.

Chúng được sử dụng phổ biến bởi các nhà phát triển cơ sở dữ liệu vì chúng có thể cho phép thực hiện các truy vấn đệ quy phức tạp, mà chúng ta sẽ sử dụng trong bài viết này.

Hầu hết các công cụ cơ sở dữ liệu hiện đại đều hỗ trợ CTE, với các biến thể trong cú pháp SQL: Oracle, SQL Server, MySQL, PostgreSQL, SQLiteMariaDB.

Các nhà phát triển có thể tìm thấy một bài viết tuyệt vời về CTE với nhiều ví dụ ở đây.

Sử dụng CTE với EF Core

Cách đơn giản nhất để sử dụng CTE với EF Core là sử dụng phương thức mở rộng FromSqlRaw. Phương thức mở rộng FromSqlRaw có sẵn trên tất cả các thuộc tính DbSet, kết quả truy vấn của chúng ta sẽ thỏa mãn tất cả các thuộc tính của entity của chúng ta.

Trong phần trước, chúng ta đã thấy định nghĩa cho entity Employee. Hãy viết một CTE mà khi truyền một mã nhân viên (Id), truy vấn sẽ trả về thông tin của cá nhân đó và những người báo cáo cấp dưới.

DECLARE @id int;
SET @id = 1;
WITH organization (id, name, title, managerid, below) AS (
    SELECT id, name, title, managerid, 0
    FROM dbo.Employees    
    WHERE Employees.Id = @id         
    UNION ALL
    SELECT e.id, e.name, e.title, e.managerid, o.below + 1
    FROM dbo.Employees e    
    INNER JOIN organization o 
        ON o.Id = e.ManagerId
)
SELECT * FROM organization

Có một vài yếu tố quan trọng trong truy vấn SQL:

  • organization là CTE của chúng ta.
  • Chúng ta định nghĩa các cột của CTE trong cặp dấu ngoặc đơn: (id, name, title, manageridbelow)
  • Giá trị cột below là một bộ tích lũy được sử dụng để biểu thị level mà chúng ta hiện đang ở trong lệnh gọi đệ quy.
  • Tham số @id được sử dụng để bắt đầu cuộc gọi đệ quy của chúng ta tại một dòng cụ thể để giới hạn tập kết quả của chúng ta.

Bây giờ, hãy chuyển truy vấn này vào DbContext của chúng ta bằng cách sử dụng một hàm trợ giúp.

public Task<List<Employee>> AllReports(int id) =>
    Employees.FromSqlRaw(
    @"WITH organization (id, name, title, managerid, below) AS (
        SELECT id, name, title, managerid, 0
        FROM dbo.Employees    
        WHERE Employees.Id = {0}         
        UNION ALL
        SELECT e.id, e.name, e.title, e.managerid, o.below + 1
        FROM dbo.Employees e    
        INNER JOIN organization o 
            ON o.Id = e.ManagerId
    )
    SELECT * FROM organization", id)
    .AsNoTrackingWithIdentityResolution()
    .ToListAsync();

Chúng ta truyền mã định danh của nhân viên id mà chúng ta cần lấy danh sách nhân viên báo cáo cấp dưới và sau đó trả kết quả. Để sử dụng phương thức này ngay bây giờ, chúng ta có thể gọi nó giống như bất kỳ phương thức nào khác.

var org = await db.AllReports(id);

Hãy xem đoạn mã này hoạt động trong một ứng dụng console đơn giản.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Spectre.Console;

namespace EntityFrameworkCoreRecursiveCTE
{
    static class Program
    {
        static async Task Main(string[] args)
        {
            var db = new Database();
            
            void AddBranch(Tree tree, TreeNode node, Employee employee)
            {
                var current = node == null
                    ? tree.AddNode($"[yellow]{employee.Name} ({employee.Title})[/]")
                    : node.AddNode($"[blue]{employee.Name}({employee.Title})[/]");

                if (employee.Reports == null)
                    return;

                foreach (var report in employee.Reports)
                {
                    AddBranch(tree, current, report);
                }
            }

            while (true)
            {
                AnsiConsole.Write("Please enter Employee #: ");
                var value = Console.ReadLine();

                if (!int.TryParse(value, out var id)) {
                    Console.WriteLine($"Employee {value} not found.");
                    continue;
                }

                var org = await db.AllReports(id);
                var tree = new Tree("Springfield Nuclear Power Plant");

                if (org.Any())
                {
                    AddBranch(tree, null, org.First());
                    AnsiConsole.Render(tree);
                }
                else
                {
                    Console.WriteLine($"Employee {value} not found.");
                }

            }
        }
    }

    public class Database : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        public Task<List<Employee>> AllReports(int id) =>
            Employees.FromSqlRaw(
            @"WITH organization (id, name, title, managerid, below) AS (
                SELECT id, name, title, managerid, 0
                FROM dbo.Employees    
                WHERE Employees.Id = {0}         
                UNION ALL
                SELECT e.id, e.name, e.title, e.managerid, o.below + 1
                FROM dbo.Employees e    
                INNER JOIN organization o 
                    ON o.Id = e.ManagerId
            )
            SELECT * FROM organization", id)
            .AsNoTrackingWithIdentityResolution()
            .ToListAsync()
            ;

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee>()
                .HasData(new List<Employee>()
                {
                    // org chart
                    // https://simpsons.fandom.com/wiki/Springfield_Nuclear_Power_Plant
                    new() {Id = 1, Name = "Charles Montgomery Burns", Title = "Owner"},
                    new() {Id = 2, Name = "Waylon Smithers, Jr.", Title = "Assistant", ManagerId = 1},
                    new() {Id = 3, Name = "Lenny Leonard", Title = "Technical Supervisor", ManagerId = 2},
                    new() {Id = 4, Name = "Carl Carlson", Title = "Safety Operations Supervisor", ManagerId = 2},
                    new() {Id = 5, Name = "Inanimate Carbon Rod", Title = "Rod", ManagerId = 4},
                    new() {Id = 6, Name = "Homer Simpson", Title = "Safety Inspector", ManagerId = 5}
                });
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer("server=localhost,11433;database=efcore_cte;user=sa;password=Pass123!;");
        }
    }

    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Title { get; set; }
        public Employee Manager { get; set; }
        public int? ManagerId { get; set; }
        public List<Employee> Reports { get; set; }
    }
}

Chạy chương trình này và truyền một mã định danh nhân viên Id = 1 sẽ cho kết quả đầu ra sau đây.

Please enter Employee #: 1
Springfield Nuclear Power Plant
└── Charles Montgomery Burns (Owner)
    └── Waylon Smithers, Jr.(Assistant)
        ├── Lenny Leonard(Technical Supervisor)
        └── Carl Carlson(Safety Operations Supervisor)
            └── Inanimate Carbon Rod(Rod)
                └── Homer Simpson(Safety Inspector)

Chúng ta có thể chạy lại ứng dụng của chúng ta với Id = 5, kết quả ở đầu ra sau đây.

Please enter Employee #: 5
Springfield Nuclear Power Plant
└── Inanimate Carbon Rod (Rod)
    └── Homer Simpson(Safety Inspector)

Sự cố với CTE và FromSqlRaw

Nói chung, FromSqlRaw sẽ cho phép các nhà phát triển thêm các điều kiện bổ sung xung quanh truy vấn ban đầu. Nhưng đó không phải là trường hợp sử dụng CTE. Cố gắng thêm bất kỳ điều kiện LINQ nào sẽ dẫn đến exception InvalidOperationException sau.

System.InvalidOperationException: 'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

Giải pháp cho vấn đề này là đặt CTE SQL của chúng ta trong một stored procedure hoặc view trong SQL. Sau khi định nghĩa stored procedure, chúng ta có thể gọi cùng một phương thức trợ giúp mà chúng ta đã định nghĩa lại để hỗ trợ thay đổi này.

public IQueryable<Employee> AllReports(int id) =>
    Employees.FromSqlRaw("Exec dbo.AllReports {0}", id);

Với thay đổi này, chúng ta có thể bắt đầu thêm lại các điều kiện LINQ vào mã C# của mình.

var result = await db
    .AllReports(1)
    .Where(e => e.Name.Contains("Homer"))
    .ToListAsync();

Phần kết luận

CTE là một cách mạnh mẽ để khám phá dữ liệu đệ quy trong công cụ cơ sở dữ liệu quan hệ dựa trên SQL Server. EF Core hỗ trợ lưu các thực thể đệ quy và như chúng ta đã thấy trong bài viết này, nó tương đối đơn giản để mô hình hóa.

Có những lưu ý đối với những gì mà phương thức FromSqlRaw có thể thực hiện, vì vậy nếu các nhà phát triển cần thêm điều kiện LINQ, thì tốt hơn là nên đưa bất kỳ triển khai CTE nào vào view hoặc stored procedure trong SQL Server.

Hy vọng rằng bạn thấy bài viết này hữu ích. Như mọi khi, cảm ơn vì đã đọc và hãy chia sẻ bài viết của tôi nếu bạn thấy nó hữu ích.

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 CoreEntity FrameworkLậ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.

Truy vấn SQL thô với Entity Framework Core 5
Trung Nguyen 11/11/2021
Truy vấn SQL thô với Entity Framework Core 5

Trong bài viết này, chúng ta sẽ khám phá cách làm việc với các tính năng của Entity Framework Core 5 (EF Core) để thực thi truy vấn SQL thô.

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.