LINQ Query to Produce Non-Empty Nested Menu?

To see my problem in action, visit www.apoads.com and hover over the "Local Businesses" menu item. It's a series of nested unordered lists generated from a db call. Go ahead and click on a few of the items underneath "Local Businesses", you'll see that most of the categories are empty (the site is very new).

Problem: I only want to show the categories that actually contain a local business. Here's what my category schema looks like:

int BizCatID - PK,Identity (used in FK relation to the table named Biz)
int? ParentID - BizCatID of this rows parentID, null means no parent
nvarchar Name - name of the category
nvarchar Caption - quick description of the category

What I've tried: I've tried to update my LINQ query like so:

from c in db.BizCategories where c.ParentID != null && c.Bizs.Count() > 0 select c;

That obviously won't work, cause I'll need the parent category to show if the child category contains a business. So I tried this:

from c in db.BizCategories where c.Bizs.Count() > 0 select c;

This doesn't work either, as parent categories will never have any businesses under them. So it seems like I'll need to do some sort of inverse recursion, but I'm not sure how to do that.

Or, perhaps I'm making things to hard for myself and I need to change my db schemas?

From stackoverflow

• I'm not sure about the recursion bit but this might get you started. Is a two-level query. I'm guessing you want to support any number of levels.

  var allcats = (from c in db. BizCategories
                 select c).ToList(); // This will retrieve them all from the database.
  
  var twoLevels = from c in allcats
                  where c.ParentID == null
                  select new {
                               Name = c.Name, Caption = c.Caption,
                               Children = 
                               from d in allcats 
                               where d.ParentID == c.BizCatID
                               select d
                             };
  

  Chad : That's got me thinking... off to fireup LINQPad and run some queries... Thanks!

• A query like this is getting close to what I need:

  from c in BizCategories
      where c.ParentID == null
       select new {
        Name = c.Name, Caption = c.Caption,
        Children = 
        from d in BizCategories
        where d.ParentID == c.BizCatID && d.Bizs.Count() > 0
        select d
       }
  

  I should be able run another LINQ query over these results and pull out exactly what I need. Not at a computer where I can test that now, but will reply once I can. Thanks!

• create classes to hold your information like so

  public class BusinessCat
  {
      public string Name{get;set;}
      public string Caption{get;set;}
      public List<'dunno datatype'> Children{get;set;};
  }
  
  var results = from c in BizCategories
      where c.ParentID == null
       select new BusinessCat{
        Name = c.Name,
        Caption = c.Caption,
        Children =  (from d in BizCategories
          where d.ParentID == c.BizCatID && d.Bizs.Count() > 0 select d).ToList()
       }
  

  and with this you have a list full of Children and you can simply iterate through it with a foreach.

  You can also do .Join() extension method for

  where d.ParentID == c.BizCatID && d.Bizs.Count()
  

  Chad : It already is a class, from LINQ to Sql. I suppose I could add the Children property through a partial class addition. Your solution looks practical, I'll report on how it works out for me. Thank you!

  Chad : After giving it a go, you're right. I was WAAAAY over-thinking things. So simple, should have seen it sooner. Thank you!