EF Core getting tree hierarchy until last child

Question

I am using EF Core 3.1 with SQL Server and I have an app that has questions. Each question may have one or more options. This can extend to unlimited levels. e.g

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

I want to be able to retrieve the entire question path.

My code is the following:

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; }
    }
}

I am executing:

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

But it only returns the first level. How can I get the entire tree up to the lowest level?

And as soon as this database piles up hundreds of thousands of questions will it affect performance?

You need to do three queries 1) First Country 2) Then State 3) And then City. If you had a double linked tree (children and parent) you could work your way up the tree. — jdweng, Mar 23 '20 at 09:46
@jdweng This is just a simple example. If you check the post I said there might be unlimited levels. Double linked? can you explain? — pantonis, Mar 23 '20 at 09:56
Your table in the database current has two columns 1) Name and 2) Child Name. You could add a third column Parent Name. — jdweng, Mar 23 '20 at 10:13
Retrieving the whole tree is easy - see https://stackoverflow.com/questions/46160780/map-category-parent-id-self-referencing-table-structure-to-ef-core-entity/46161259#46161259. Basically replace `.FirstOrDefault()` in your query with `.AsEnumerable().Where(x => x.ParentOptionId == null).ToList()` — Ivan Stoev, Mar 23 '20 at 12:27
Thank you Ivan. That did the trick actually. What about the other part of my post. As soon as this database piles up hundreds of thousands of questions will it affect performance? — pantonis, Mar 23 '20 at 12:53

EF Core getting tree hierarchy until last child

0 Answers0