C# 和 Entity Framework - 为具有不确定家谱的自引用实体优化数据库查询

Question

我想改进我所做的现有查询。从技术上讲，它现在可以正常工作，但当我看到它时似乎效率不高，但目前我想不出更好的方法。

首先，我有以下设置:

public class Folder
{
    public int FolderID { get; set; }
    public string Name { get; set; }
    public int? ParentFolderID { get; set; }
    public Folder? ParentFolder { get; set; }

    //Folders belonging to folder
    public ICollection<Folder>? ChildFolders { get; set; }

    //Users belonging to folder
    public ICollection<User>? Users { get; set; }

    //Groups belonging to folder
    public ICollection<Group>? Groups { get; set; }

    // Determines which users and groups can see this folder
    public ICollection<UserFolder>? UserFolders { get; set; } //Composite Entity between users and folders
    public ICollection<GroupFolder>? GroupFolders { get; set; } //Composite Entity between groups and folders
}

如图所示:一个文件夹可以有一个父文件夹和一个子文件夹列表。

我为实体使用以下 DTO:

public class FolderAdminSidebarRightDTO : IFolderGetCollectionDTO
{
    public int FolderId { get; set; }
    public string Name { get; set; }
    public int? ParentFolderId { get; set; }

    // Id and username of users
    public List<Tuple<int, string>> Users { get; set; }

    // List of itself
    public List<FolderAdminSidebarRightDTO> SubFolders { get; set; }
}

我们的想法是在右侧显示一个导航栏，其中包含他们有权访问的文件夹列表，可以通过单击单独展开导航栏以显示该文件夹内的用户和子文件夹列表。这可以无限期地重复。

例子:

-folder 1
  -user 1
  -user 2
  -folder 2
    -user 3
    -user 4
    -folder 3
      -unknown steps down...
-folder 4
  -user 5
  -user 6
  -folder 5

我编写了以下代码来满足这些需求:

/// <inheritdoc/>
public IEnumerable<IFolderGetCollectionDTO> GetFolders(NetworkManagementContext context, int userId, IFolderDTOOptions options)
{
    //query to get all folders the current user has access to
    List<FolderAdminSidebarRightDTO> query = context.UserFolders
        .AsNoTracking()
        .Where(uf => uf.UserID == userId)
        .Select(uf => new FolderAdminSidebarRightDTO
        {
            FolderId = uf.Folder.FolderID,
            Name = uf.Folder.Name,
            ParentFolderId = uf.Folder.ParentFolderID,
            Users = uf.Folder.Users.Select(u => new Tuple<int, string>(u.UserID, u.Name)).ToList()
        }).ToList();

    //List of all subfolder Ids 
    List<int> subfolderIds = new List<int>();

    //iterates through all folders found in inital query to get subfolders
    foreach (FolderAdminSidebarRightDTO item in query)
    {
        GetSubFolders(item, subfolderIds, context);
    }

    //removes all duplicates of folder ids
    subfolderIds = subfolderIds.Distinct().ToList();

    //removes folders from initial query that the user has access to, but already exists 
    //somewhere within another folder from initial query or it's subfolders to avoid duplicate UI elements
    query.RemoveAll(f => subfolderIds.Contains(f.FolderId));

    return query;
}

/// <summary>
/// Projects a list of folders from DB related to id of <see cref="FolderAdminSidebarRightDTO"/> object argument and binds the list of projected folders to the object
/// </summary>
/// <param name="folder">DTO of folder entity</param>
/// <param name="folderIds">List of folder ids used for filtering out duplicate folders found in DB</param>
/// <param name="context">Context for entity framework</param>
private void GetSubFolders(FolderAdminSidebarRightDTO folder, List<int> folderIds, NetworkManagementContext context)
{
    //query to get all subfolders inside folder argument
    folder.SubFolders = context.Folders
        .AsNoTracking()
        .Where(f => f.ParentFolderID == folder.FolderId)
        .Select(f => new FolderAdminSidebarRightDTO
        {
            FolderId = f.FolderID,
            Name = f.Name,
            ParentFolderId = f.ParentFolderID,
            Users = f.Users.Select(u => new Tuple<int, string>(u.UserID, u.Name)).ToList()
        }).ToList();

    //adds id of all found subfolders in current query to list of subfolder ids
    folder.SubFolders.ForEach(f => folderIds.Add(f.FolderId)); 

    //iterates through all subfolders found in current query to get subfolders for the next level
    foreach (FolderAdminSidebarRightDTO item in folder.SubFolders)
    {
        //calls itself
        GetSubFolders(item, folderIds, context);
    }
}

我主要关心的是我通过这个过程调用上下文的次数，但我个人无法弄清楚如何在不首先检索上面的级别的情况下动态获取未知数量的文件夹级别，因为我需要知道获取其子项的文件夹 ID。

如果我的代码中还有其他可以更高效地制作的东西，我会非常乐意听到。

提前致谢

Answer 1

AFAIK，不支持通过 LINQ 进行此类递归调用。

因此，您有以下选择:

1. 使用纯 SQL 编写查询，这将使您能够编写 Common Table Expression那calls itself recursively .然后，构建结果的递归逻辑将立即在数据库服务器中完成，而不是像现在这样从客户端应用程序进行多次往返。
2. 如果您出于某种原因不想在您的代码中使用原始 SQL，您始终可以根据您在选项 1 中构建的相同查询创建一个数据库 View ，然后直接从您的应用程序查询该 View 。<
3. 最后，还有一个完全不使用递归 CTE 的替代方案:您可以在数据库中维护您的树结构，称为 Nested Set Model。 .这样做时，您必须向每个节点添加两个新属性，名称类似于 Left。和 Right (或 TreeMin 和 TreeMax )，表示在树遍历期间第一次/最后一次访问每个节点时的“位置”。用 Left 标记每个节点的想法和 Right这样一来，您就可以轻松查询一个节点的所有子节点，检查是否 child.Left > parent.Left && child.Right < parent.Right .在数据库中维护此类数据结构可能比以前的解决方案稍微复杂一些，只有在您的树不经常更改的情况下才建议这样做。