How to create a recursive association in SQL Server and loop through it in C# with Entity Framework?

I am trying to design a database strcuture for a questionnarie, and then an application that can display that questionnarie.

In my database, i have an entity table called Category. Each Category can have multiple subcategories, which each can have multiple subcategories etc. This is a recursive association.

Currently i have designed it as follows:

[Table: Category]
category_id
category_number
category_name
parrent_id

[Table: Question]
question_id
question_number
question_wording
question_category

A concrete example would be:

- Categories

category_id = 1
category_number = 1
category_name = Milk Products
parret_id = null

category_id = 2
category_number = 1.1
category_name = Cheese
parent_id = 1

category_id = 3
category_number = 1.1.1
category_name = Soft
parrent_id = 2

- Questions

question_id = 1
question_number = 1
question_wording = "From 1-5, how much do you like soft cheese?"
category_id = 3

question_id = 2
question_number = 1
question_wording = "How much do you like cheese?"
category_id = 2

How would i loop through the categories and display each category and question like this:

Questionnarie
- Milk products
-- Cheese
-- "How much do you like cheese?"
--- Soft-Cheese
--- "From 1-5, how much do you like soft cheese?"
开发者_运维问答

I am using the entity framework to load my database into c#

Thanks in advance!

You would do this the same as you would do with normal C# code. You can make recursive function calls:

public IEnumerable<Category> GetSubCategoriesFor(int catId)
{
    var subs = db.Categories.Where(c => c.ParentId == catId);

    foreach (var sub in subs)
    {
        yield return sub;

        // Recursive call
        foreach (var subsub in GetSubCategoriesFor(sub.Id))
        {
            yield return subsub;
        }
    }
}