Effective way to enter nested data

Question

I need to select several "master" rows from a table, and also return for each result several rows of details from another table. Which is a good way to achieve this without multiple queries (one for the main lines and one for each result to get the details rows).

For example, with a database as shown below:

MasterTable: - MasterId BIGINT - Name NVARCHAR(100) DetailTable: - DetailId BIGINT - MasterId BIGINT - Amount MONEY 

How can I most efficiently populate the data object below?

 IList<MasterDetail> data; public class Master { private readonly List<Detail> _details = new List<Detail>(); public long MasterId { get; set; } public string Name { get; set; } public IList<Detail> Details { get { return _details; } } } public class Detail { public long DetailId { get; set; } public decimal Amount { get; set; } } 

Answer 1

As a rule, I would use a two-grid approach, however you can also look at FOR XML - it is quite simple (in SQL Server 2005 and above) to form the parent / child data as xml and load it from there.

 SELECT parent.*, (SELECT * FROM child WHERE child.parentid = parent.id FOR XML PATH('child'), TYPE) FROM parent FOR XML PATH('parent') 

Also - LINQ-to-SQL supports this type of model, but you need to say what data you want ahead of time. Via DataLoadOptions.LoadWith :

 // sample from MSDN Northwnd db = new Northwnd(@"c:\northwnd.mdf"); DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<Customer>(c => c.Orders); db.LoadOptions = dlo; var londonCustomers = from cust in db.Customers where cust.City == "London" select cust; foreach (var custObj in londonCustomers) { Console.WriteLine(custObj.CustomerID); } 

If you are not using LoadWith , you will get n + 1 queries - one master and one child list on each line.

Answer 2

This can be done with a single request:

 select MasterTable.MasterId, MasterTable.Name, DetailTable.DetailId, DetailTable.Amount from MasterTable inner join DetailTable on MasterTable.MasterId = DetailTable.MasterId order by MasterTable.MasterId 

Then in psuedo code

 foreach(row in result) { if (row.MasterId != currentMaster.MasterId) { list.Add(currentMaster); currentMaster = new Master { MasterId = row.MasterId, Name = row.Name }; } currentMaster.Details.Add(new Detail { DetailId = row.DetailId, Amount = row.Amount}); } list.Add(currentMaster); 

There are a few ribs to bring this down, but this should give you a general idea.

Answer 3

select <columns> from the wizard

select <columns> from master M join Child C on M.Id = C.MasterID

Answer 4

You can do this with two queries and one pass on each result set:

A request for all the masters ordered by MasterId, then a request for all parts also ordered by MasterId. Then, with two nested loops, iterate through the main data and create a new row for the main foreach object in the main loop and iterate over the details as long as they have the same MasterId as the current main object and populate its _details collection in the nested loop.

Answer 5

Depending on the size of your data set, you can pull all the data into your application in memory with two requests (one for all masters and one for all attached data), and then use it to programmatically create your subscribers for each of your objects, giving that something like:

 List<Master> allMasters = GetAllMasters(); List<Detail> allDetail = getAllDetail(); foreach (Master m in allMasters) m.Details.Add(allDetail.FindAll(delegate (Detail d) { return d.MasterId==m.MasterId }); 

You essentially trade memory for speed with this approach. You can easily adapt this so that GetAllMasters and GetAllDetail return only the basic and detailed elements that you are interested in. Also note that for this you need to add MasterId to the part class

Answer 6

This is an alternative that you may consider. It costs $ 150 per developer, but time is also money ...

We use an object storage layer called Entity Spaces , which generates code for you exactly the way you want, and you can regenerate whenever your circuit changes. Filling objects with data is transparent. Using the objects described above will look like this (sorry my VB, but it works in C # too):

 Dim master as New BusinessObjects.Master master.LoadByPrimaryKey(43) Console.PrintLine(master.Name) For Each detail as BusinessObjects.Detail in master.DetailCollectionByMasterId Console.PrintLine(detail.Amount) detail.Amount *= 1.15 End For With master.DetailCollectionByMasterId.AddNew .Amount = 13 End With master.Save()