LINQ To SQL - What is the most efficient way to loop through master/detail?

The only thing I can find when dealing with master/detail and LINQ is through databinding. However, I am using LINQ to query my data and will need to manually loop through results without the need for databinding. My final intent is to loop through a master result set, get a grouped list of children, and output the master/detail data as a hierchial structure in XML.

My illustration: I have a table called my_master_tbl and and it will be queried by a date. I have a child table called my_child_tbl which will be grouped by date, by the master id, and use sum() to total some fields. I'd like to link the grouped data to the master and loop through the master/detail in the most efficient way possible to export the results as xml. What is that way? The only way I can think of is to loop through the master, then query the grouped result set of the children based on master_id.

Solution

I would implement this using a combination of LinqToSql and LinqToXml, similar to the following:

var query = new XDocument(
    new XElement("Results", from i in context.my_master_tbl
                            where i.ItemDate > DateTime.Now.AddDays(-7)
                            select new XElement("MasterItem",
                                new XAttribute("MasterName", i.Name),
                                 from c in i.my_child_tbl
                                 group c by c.Date into g
                                 select new XElement("Child",
                                     new XAttribute("Date", g.Key),
                                     new XAttribute("SumField", g.Sum(d => d.FieldToSum))
                                 )
                            )));

Here is some resulting Xml this will generate:

<Results>
 <MasterItem MasterName="A">
  <Child Date="2009-01-15T00:00:00-06:00" SumField="491470" />
 </MasterItem>
 <MasterItem MasterName="B">
  <Child Date="2009-01-29T00:00:00-06:00" SumField="491526" />
 </MasterItem>
 <MasterItem MasterName="C">
  <Child Date="2009-01-15T00:00:00-06:00" SumField="1965960" />
  <Child Date="2009-07-14T00:00:00-05:00" SumField="-27" />
 </MasterItem>
 <MasterItem MasterName="D" />
 <MasterItem MasterName="E" />
</Results>

I'm sure you'll need to make a few changes to this based on the layout you were planning on using, but hopefully it will give you some ideas.

This also assumes your relationships are setup between your master and child tables. If they aren't, a few other changes will be necessary.