Linq to SQL Grouping Child Relationships

Question

I'm trying to run a LINQ to SQL query that returns a result in a grid view in a search engine style listing.

In the simplified example below, is it possible to populate the collection with a comma-separated list of any children that the parent has (NAMESOFCHILDREN) in a single query?

var family = from p in db.Parents
             where p.ParentId == Convert.ToInt32(Request.QueryString["parentId"])
             join pcl in db.ParentChildLookup on p.ParentId equals pcl.ParentId
             join c in db.Children on pcl.ChildId equals c.ChildId
             select new
             {
                 Family = "Name: " + p.ParentName + "<br />" + 
                          "Children: " + NAMESOFCHILDREN? + "<br />"
             };

Thanks in advance.

Answer 1

Your joins are going to screw up your cardinality! You don't have a list of Parents!

Here's some untested free-hand code. Adding the relationships in the Linq designer gives you relationship properties. String.Join will put the list together.

I've added two optional method calls.

Where ... Any will filter the parents to only those parents that have children. I'm unsure of string.Join's behavior on an empty array.

ToList will yank Parents into memory, the children will be accessed by further database calls. This may be necessary if you get a runtime string.Join is not supported by SQL translator exception. This exception would mean that LINQ tried to translate the method call into something that SQL Server can understand - and failed.

int parentID = Convert.ToInt32(Request.QueryString["parentId"]);

List<string> result =
  db.Parents
  .Where(p => p.ParentId == parentID)
  //.Where(p => p.ParentChildLookup.Children.Any())
  //.ToList()
  .Select(p => 
    "Name: " + p.ParentName + "<br />" + 
    "Children: " + String.Join(", ", p.ParentChildLookup.Children.Select(c => c.Name).ToArray() + "<br />"
)).ToList();

Also note: generally you do not want to mix data and markup until the data is properly escaped for markup.

Answer 2

you could try as follow:

var family = from p in db.Parents            
     where p.ParentId == Convert.ToInt32(Request.QueryString["parentId"])             
    join pcl in db.ParentChildLookup on p.ParentId equals pcl.ParentId             
     select new             {                 
        Family = "Name: " + p.ParentName + "<br />" + string.Join(",",(from c in db.Children where c.ChildId equals pcl.ChildId  select c.ChildId.ToString()).ToArray());
    };