Using IQueryable in join causes StackOverflow Exception

Question

Does anyone know why this causes a stackoverflow exception:

public IQueryable<Category> LoadCategories(bool onlyCatsWithProducts, ...)
{
    var db = new DbDataContext();
    var res = db.Categories.AsQueryable();

    if (onlyCatsWithProducts)
        res = from p in db.Products
              from c in res
              where p.CategoryID == c.ID
              select c;
    ...
    return res;
}

Update

Changed the example code to make clear why I'm assigning to a variable then reassign it later. Basically it's because I'm writing a function to return categories from the database and I take in several parameters (e.g. onlyCatsWithProducts) where each filter the result set only if they have a value. Note this is still not my actual code because my queries are more complicated and I'm just wanting to show the simplest query needed to reproduce the error.

Answer 1

Michael, in answering his own question, said he had no idea why swapping the order of his from's fixed his problem.

Like this caused the stack overflow:

var res = db.Categories.AsQueryable();
res = from p in db.Products
      from c in res
      where p.CategoryID == c.ID
      select c;

Like this didn't:

var res = db.Categories.AsQueryable();
res = from c in res
      from p in db.Products
      where p.CategoryID == c.ID
      select c;

Here's why. These two above queries get translated, by the compiler, to this code:

var res = db.Categories.AsQueryable();
var q = db.Products
    .SelectMany(p => res, (p, c) => new { p, c })
    .Where(x => x.p.CategoryID == x.c.ID)
    .Select(x => x.c);

and this respectively:

var res = db.Categories.AsQueryable();
var q = res
    .SelectMany(c => db.Products, (c, p) => new { c, p })
    .Where(x => x.p.CategoryID == x.c.ID)
    .Select(x => x.c);

The first one contains the lambda p => res which is essentially capturing a reference to the res variable. Since res gets reassigned every time the query is run in references the reassigned version of itself and bang - stack overflow!

In the second one the res is outside of any lamdbas, so the reference isn't captured and only the original reference is used - i.e. res = db.Categories.AsQueryable() and this doesn't change when the query is executed.

It would probably be just as easy to use:

var res = from c in db.Categories
          from p in db.Products
          where p.CategoryID == c.ID
          select c;

I hope this helps clear up what is happening.