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

Question

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. Any better ideas would be appreciated.

Thank You.

Answer 1

I suggest doing this either as a stored procedure that returns to result sets or as a query that gets all the master data and another query that gets all the child data. This way you have it on hand. Then as you iterate through the master data you already have ALL the child data on hand. You can then do a server side filter on all the child data by the current master ID...and iterate through all the child data.

This concept would work for paging too if you ever need to do that as you are only getting the amount master and child data that you need to work with in a given iteration.

Answer 2

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.