How to save multiple linq queries from a loop in a single variable?

Question

I thought its possible to save multiple linq queries from a loop in a single variable ?

public ActionResult Index()
    {
        string ActiveUserId = (string)Session["ActiveUserGuid"];
        Guid MyGuid = new Guid(ActiveUserId);

        var queryRoots = from r in db.Roots
                         where r.UserId == MyGuid
                         select r.TaskId;

//Here is the questionable Part
        foreach (var i in queryRoots)
        {             
            var queryAllTasks = from t in db.Tasks
                                join b in db.Trees on t.TaskId equals b.ChildId
                                where b.TaskId == i
                                select t;
            tasksForView.Add(queryAllTasks); <---????? obviously doesnt work
        };

        return View(queryAllTasks);
    }

public class Root
{
    public int RootId { get; set; }

    public Guid UserId { get; set; }
    public User User { get; set; }  //navigation Property

    public int TaskId { get; set; } // composite key
    public int ChildId { get; set; }
    public Tree Tree { get; set; } //navigation Property
}

public class Tree
{
    public int TaskId { get; set; }
    public int ChildId { get; set; }
    public int Length { get; set; } //Path length

    public virtual ICollection<Root> Roots { get; set; }

}

In case you are wondering what its supposed to do. I want to query several subtrees from a hierarchical transitiv closure table and return them to a view.

I know my code is not close to working and I just tried very simple solutions! I guess a different way to do the query would get rid of the problem all together

Answer 1

Use Contains on your integer list:

var tasks = 
    from t in db.Tasks
    join b in db.Trees on t.TaskId equals b.ChildId
    where queryRoots.Contains(b.TaskId)
    select t;

return View(tasks);

or do it all in one query:

var tasks = 
    from t in db.Tasks
    join b in db.Trees on t.TaskId equals b.ChildId
    join r in db.Roots on r.TaskId equals b.TaskId
    where r.UserId == MyGuid
    select t;

return View(tasks);

Answer 2

The problem here is that you're closing over the loop variable. LINQ queries use deferred execution whenever possible, so you're just defining what the query is and storing that, not calculating the results and storing them.

You have a few choices here. You could just add a ToList to the end of the query; it would eagerly evaluate it, and storing that list won't lead to problems.

What's likely breaking is that your query is using i, which is changed in the loop. Since the query doesn't actually evaluate what i is until after the end of the loop, i will always have whatever the last value was. This is easily fixed by just adding a new local variable inside of the loop and assigning i to it. Use that local variable in your query.

Answer 3

As others have said, queryAllTasks has deferred execution in each iteration of the loop. So all you're storing is an IQueryable.

What you want is

tasksForView.Add(queryAllTasks.ToList());

Also, I noticed you're trying to return queryAllTasks as your view model, but that variable is scoped to the for loop. I assume you want to return tasksForView as your model (which I further assume is declared elsewhere, as I don't see a declaration for it in the code you provided.)