Using IQueryable in join causes StackOverflow Exception

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 parameter开发者_Go百科s (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.

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.

Apologies, after posting I discovered swapping the order seems to fix the problem. Got no idea why though:

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

Why create a variable and then re-assign the variable straight after, using the variable in the assignment? Your query is probably doing some sort of infinite recursion, causing a StackOverFlowException. Try something like:

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

Update:

Try something like the below. I think you need to avoid using res in the assignment of res.

IQueryable<Category> res;

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