title: select from multiple tables with linq to sql
body: <p>I have a class <code>Category</code> like this:</p>
  
  <pre><code>public int id { get; set; }
  public string catName { get; set; }
  public List&lt;string&gt; subCat { get; set; }
  </code></pre>
  
  <p>And I have 2 tables, <code>tblCategory</code> and <code>tblSubCategory</code>. </p>
  
  <p>I want to select all categories and the subcategories under each category. I am trying the code below, but I get the same result for all the categories, even if some categories doesn't have any subCategories they still get the hole list of subCategories. </p>
  
  <p>Any help would be much appreciated.</p>
  
  <pre><code>public static List&lt;Category&gt; getCategoryList()
  {
      try
      {
          using (var db = new LinqToDBAwkoDataContext())
          {
              var list = from c in db.tblCategories
                         select new Category
                              {
                                 id = c.CatId,
                                 catName = c.CatName,
                                 subCat = subCategoriesByCatId(db)
                             };
              return list.ToList();
          }
      }
      catch (Exception ex)
      {
          throw new Exception("An error has occurred");
      }            
  }
  
  private static List&lt;string&gt; subCategoriesByCatId(LinqToDBAwkoDataContext db)
  {
      var list = from s in db.tblSubCategories
                 join c in db.tblCategories on s.CatId equals c.CatId
                 where s.CatId == c.CatId
                 select s.SubCatName;
      return list.ToList();
  }
  </code></pre>