EF Core Automapper递归查询

Question

我有一个自引用类

public class Project {
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Guid? ParentId { get; set; }    
}

我已经设置了一个psotgres数据库与ef核心和自动映射程序都在工作。

我的Dto如下所示：

public class ProjectDto {
    public Guid Id { get; set; }
    public string Name { get; set; }
    public ICollection<ProjectPathEntry> Path { get; set; } = new HashSet<ProjectPathEntry>();
}

使用helper-dto

public class ProjectPathEntry {
    public Guid Id { get; set; }
    public string Name { get; set; }
}

现在的问题是自动映射配置:) Id和名称根据约定自动映射，但路径显然不能直接映射。

我可以用一个单独的查询来填充路径：

var projectPath = await dbContext.Projects.FromSqlInterpolated(
    $@"WITH recursive project(search_id, id, name) AS (
        SELECT p.""Id"", p.""Id"", p.""Name""
        FROM public.""Projects"" p

        UNION ALL

        SELECT p.""Id"", p2.id, p2.name
        FROM public.""Projects"" p, project p2
        WHERE p.""ParentProjectId"" = p2.search_id
    )
    SELECT proj.*
    FROM project
    JOIN public.""Projects"" proj on proj.""Id"" = project.id
    WHERE search_id = {project.Id}
        AND id != {project.Id}"
).ProjectTo<ProjectPathEntry>(mapper.ConfigurationProvider)
.ToListAsync(cancellationToken);

projectPath.Reverse();
project.Path = projectPath;

我可以以某种方式将此查询嵌入到我的自动映射程序配置中吗？或者有没有可能将SQL-View添加到我的pg-database中，并通过ef实体配置映射它？

Answer 1

我通过在数据库中添加一个视图解决了这个问题：

CREATE VIEW "ProjectPathView"("ProjectId", "SortId", "Id", "Name") AS
    WITH RECURSIVE project(search_id, sort_id, id, name) AS (
        SELECT p."Id",
            1,
            p."Id",
            p."Name"
FROM "Projects" p
    UNION ALL
    SELECT p."Id",
        p2.sort_id + 1,
        p2.id,
        p2.name
    FROM "Projects" p,
        project p2
    WHERE p."ParentProjectId" = p2.search_id
)
SELECT project.search_id AS "ProjectId",
    project.sort_id AS "SortId",
    proj."Id",
    proj."Name"    
FROM project
JOIN "Projects" proj ON proj."Id" = project.id;

然后我创建了一个模型

public class ProjectPathEntry
{
    public Guid ProjectId { get; set; }
    public long SortId { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
}

这代表了我的观点。在EF配置中，我设置了ToView("ProjectPathView")设置。然后，我将关系添加到我的基本模型中

public class Project {
    ...
        public virtual ICollection<ProjectPathEntry> Path { get; set; } = new HashSet<ProjectPathEntry>();
    ...
}

并设置HasMany配置

builder.HasMany(p => p.Path)
       .WithOne()
       .HasForeignKey(p => p.ProjectId);

然后，我的ProjectDto上的Path属性可以通过Automapper自动映射。