Populating Childnode list: Linq confusion and separation of concerns

Question

I am trying to implement a tree view in my application. I am using MVC2 Preview 1, and SubSonic 3 SimpleRepository. I am new to both MVC and Linq.

My problem is that I am not sure how to add a list of child nodes to the model record that I am passing back to the View. So I have added a IEnumerable called Children to my model class that I populate in the controller action:

public class Category
{
    public Category()
    {

    }

    public int ID { get; set; }
    public int? ParentId { get; set; }
    [Required(ErrorMessage="Name is required")]
    public string Name { get; set; }
    [SubSonicIgnore]
    public IEnumerable<Category> Children { get; set; }

}

Then in the controller action, I pull all of the records and iterate through updating the Children member:

    public ActionResult Index()
    {
        var categories = _repo.All<Category>();

        foreach (var c in categories)
        {
            c.Children = from p in _repo.All<Category>()
                                               where p.ParentId == c.ID
                                               orderby p.Name
                                               select p;
        }   
        return View(categories);     
     }

My 2 questions are #1 Why does this not work? Outside of the scope of the loop my changes are lost. #2 In a general sense, is this the right approach? Putting this code in the controller feels like a hack.

Answer 1

As for why it doesn't work, I suspect deferred execution is getting you. If you wrap the Linq query like so( from ... select p).ToList() it will cause the query to be evaluated.

Regarding the approach, it is data access in the presentation tier, so generally speaking, that would be something to avoid.