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

score 2 · Accepted Answer · answered Oct 04 '12 at 18:32

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

score 0 · Answer 2 · edited May 23 '17 at 10:24

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.

score 0 · Answer 3 · edited Oct 04 '12 at 19:08

0

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.)

edited Oct 04 '12 at 19:08

Konrad Viltersten

36,151
76
250
438

answered Oct 04 '12 at 18:49

control

131
3

I still need to read up on things and not just to fill gaps. Thank you for your explanation and steering me to the right topics. – Wozilla Oct 04 '12 at 22:25

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

3 Answers3