C# SQL parent child table select query help

Question

Hello,

I have two tables that I want to fetch data from.

Lets call them "Parent" and "Children". The "Parent" table has many records in the "Children" table. (One to Many)

My problem is I want to find an efficient method for displaying the data the two tables contain to my ASP.NET MVC application. My approach is to select all records from "Parent" then loop through each one selecting the data from "Children".

So I need help designing a query for SQL Server 05 that can get the data out for C# more efficiently, I was planning of creating type classes and the "Parent" class would have a "List" that in my View I can loop through outputting the values.

public class Parent_Type
{
    public string Name { get; set; }
    public List<Children_Type> Children { get; set; }
}


public class Children_Type
{
    public string Name { get; set; }
}

The data needs to be displayed like: (The name of the "Parent" record then a list of "Children" records")

"Parent 123"

• "Child 1"
• "Child 2"
• "Child 3"

"Parent 124"

• "Child 1"
• "Child 2"
• "Child 3"

Answer 1

Hi, If you are looking to store and retrieve heirarchical data, you can do this in a single table, have a look at SQL Server CTE Expressions (CTE). an example here http://msdn.microsoft.com/en-us/library/ms190766.aspx

Answer 2

You didn't really clearly asked a question you just mentioned some thoughts and I think that's why someone downvoted but a comment would have been nice. Any ways if you want to know what SQL to use checkout http://stackoverflow.com/questions/480742/select-parent-record-with-all-children-in-sql

the other way (used by some ORM's) is to do two selects. one for all parents and one for all childeren and to then join them in the applications memory instead of on the database server.

Answer 3

Well, you can always write a query that will return a JOIN across both tables:

  SELECT Parent.ID, Parent.Field2, Parent.Field3,
         Child.ID, Child.Value2, Child.Value3
  FROM
     Parent
  INNER JOIN (or: LEFT OUTER JOIN)
     Child ON Parent.ID = Child.ParentID

but in this case, you'll get a "flattened" rowset back, in which the values for the parents are repeated for each of their child elements, obviously (standard SQL join behavior).

As has been pointed out in the comments, if you use the INNER JOIN, of course, you'll only get back parents and their child records - parents without children will be left out. If you want those, too (with their child columns set to NULL), use LEFT OUTER JOIN instead of INNER JOIN.

You're second option would be to have an ADO.NET query that returns two results, basically - something like

 SELECT Parent.ID, Parent.FIeld2, Parent.Field3 ;
 SELECT Child.ID, Child.ParentID, Child.Value2, Child.Value3;

but then you'll need to parse two result sets from the response, associate the child records with their respective parents, and do some more work.

Your third option would be to create a "FOR XML" query in SQL Server that would return an XML document representation the hierarchy of Parents and Child records in a single XML document.

SELECT
    Parent.ID, Parent.Field2, Parent.Field3,
    (SELECT 
       Child.ID, Child.Value2, Child.Value3
     FROM Child
     WHERE ParentID = Parent.ID FOR XML AUTO, TYPE, ELEMENTS)
FROM
    Parent
FOR XML 
    AUTO, ROOT('Root'), ELEMENTS

Whichever works best for you - take your pick!

Marc

Answer 4

I'd pull all of the data at once and then run through it programmatically.

SELECT Parent.Id, Parent.Name FROM Parent 
LEFT OUTER JOIN
SELECT Child.Id AS ChildId, Child.Name AS ChildName FROM Child
ON
Child.ParentId = Parent.Id
ORDER BY Parent.Name

When you get the data back, the parents will be repeated so you'll need to filter it down. You could do this via LINQ by providing a parent comparison or via a for loop. Itereate through the list collapsing on unique parent as each record will, in fact, represent a child or a childless parent.

Information about LINQ's "distinct" is available here.