c# - EF Core 获取树层次结构直到最后一个孩子

Question

我正在使用带有 SQL Server 的 EF Core 3.1，并且我有一个有问题的应用程序。每个问题可能有一个或多个选项。这可以扩展到无限的级别。例如

What is your Country?
|
|--France
|--United Kingdom
|--United States
   |---What is your state?
       |---New York
       |---Alaska
       |---California
           |---What is your City?
               |--- Los Angeles
               |--- San Diego
               |--- San Francisco       

我希望能够检索整个问题路径。

我的代码如下：

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions options)
        : base(options)
    {
        Database.Migrate();
    }

    public virtual DbSet<Question> Question { get; set; }
    public virtual DbSet<Option> Option { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Question>(entity =>
        {
            entity.HasMany(x => x.Options)
                .WithOne(x => x.ParentQuestion)
                .IsRequired();
        });

        modelBuilder.Entity<Option>(entity =>
        {
            entity.HasMany(x => x.SubQuestions)
                .WithOne(x => x.ParentOption);
        });
    }

    public class Question
    {
        public Question()
        {
            Options = new HashSet<Option>();
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }

        public string QuestionText { get; set; }

        // A Question may have one or more than one options
        public virtual ICollection<Option> Options { get; set; }

        // An Option may have one or more Questions 
        public long? ParentOptionId { get; set; }
        public virtual Option ParentOption { get; set; }
    }

    public class Option
    {
        public Option()
        {
            SubQuestions = new HashSet<Question>();
        }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }

        public string OptionText { get; set; }

        // A Question may have one or more than one options
        public long ParentQuestionid { get; set; }
        public virtual Question ParentQuestion { get; set; }

        // An Option may have one or more Questions 
        public virtual ICollection<Question> SubQuestions { get; set; }
    }
}

我正在执行：

var questionTree = (from question in context.Question
                                            .Include(x => x.Options)
                                            .ThenInclude(x => x.SubQuestions)
                    select question).FirstOrDefault();

但它只返回第一级。我怎样才能让整棵树升到最低层？

一旦这个数据库堆积了数十万个问题，它会影响性能吗？

标签： c#.netsql-server.net-coreentity-framework-core