title: How to assign to a List<string> in LinqToEntities C#
body: <p>Hoping that I'm just missing something obvious but here's my query</p>
  
  <pre><code>        var data = (from project in _db.Projects 
                      where project.Id == id
                      let primaryCategory = (from c in _db.Categories
                                             where c.CategoryID == project.PrimaryCategory
                                             select c.Name)
                      let categories = (from c in _db.ProjectCategories
                                        join pc in _db.Projects_ProjectCategories on c.ProjectCategoryID equals pc.ProjectCategoryID
                                        where pc.ProjectID == project.ProjectID
                                        select c.Name)
                      let owner = (from o in _db.Owners
                                   join po in _db.App_Projects_Owners on o.OwnerID equals po.OwnerID
                                                 where po.ProjectID == project.ProjectID
                                                 select new OwnerModel
                                                 {
                                                     Owner = o,
                                                     Project = project,
                                                     PrimaryCategory = primaryCategory.FirstOrDefault(),
                                                     Categories = categories.ToList()
                                                 })
  
                      select new
                      {
                          owner,
                          project
                      }).FirstOrDefault();
  </code></pre>
  
  <p>In there OwnerModel.Categories is a List of strings.  I can't use ToList() in the query because it gives a materialization error.  I've added a custom setter that takes the IQueryable, but that still makes another round trip to the database for every owner that the query returns. </p>
  
  <p>So how are you supposed to assign basic lists in a subquery?</p>
  
  <p><strong>EDIT AND ANSWER</strong> (since Robert McKee lead me to the answer in his comment).</p>
  
  <p>The answer is to use the group by clause like so</p>
  
  <pre><code>var data = (from project in _db.Projects 
              where project.Id == id
              let primaryCategory = (from c in _db.Categories
                                             where c.CategoryID == project.PrimaryCategory
                                             select c.Name)
                      let categories = (from c in _db.ProjectCategories
                                        join pc in _db.Projects_ProjectCategories on c.ProjectCategoryID equals pc.ProjectCategoryID
                                        where pc.ProjectID == project.ProjectID
                                        group c.Name by pc.ProjectCategoryID into x
                                        select x.ToList())
                      let owner = (from o in _db.Owners
                                   join po in _db.App_Projects_Owners on o.OwnerID equals po.OwnerID
                                                 where po.ProjectID == project.ProjectID
                                                 select new OwnerModel
                                                 {
                                                     Owner = o,
                                                     Project = project,
                                                     PrimaryCategory = primaryCategory.FirstOrDefault(),
                                                     Categories = categories
                                                 })
                      select new
                      {
                          owner,
                          project
                      }).FirstOrDefault();
  </code></pre>
  
  <p>Specifically note the bits involving                          </p>
  
  <pre><code>group c.Name by pc.ProjectCategoryID into x 
  select x.ToList()
  </code></pre>
  
  <p>It may seem counter-intuitive that it works like this until you dig into exactly what's going on.  The method that I was calling above with categories.ToList() was trying to use the System.Collection.Generics ToList function, and that list didn't have any way to convert the expression to sql.  However by using the group by clause I was creating a specialized Enumerable IGrouping and calling the ToList function on that.  This function is able to be translated into a sql statement and thus doesn't throw the exception.</p>
  
  <p>Learn something new every day.</p>