I apologise for the badly-worded title, but I'm struggling to describe the problem more succinctly.
I have a SQL Server database with a table, lets call it People, which has an integer primary key, and various fields like Forename, Surname, Date of Birth, etc. One record in this table represents one real-world person.
But there are also a series of related tables which hold "child" data where there can be multiple values for a single Person record. These are things like telephone numbers, home addresses, e-mail addresses, etc. A Person may have none of these, or they might have many of each. (Realistically it's very unusual for anybody to have more than two or three of any of these things, but the point is there can easily be more than one.) Each of these child records has a foreign key back to the parent record in the People table.
If there was only one child table, that would just be a simple left join, but I've got several.
I'm querying the database using the classes in the Microsoft.Data.SqlClient namespace. The only way I have been able to think of to query the "complete" record for each person is to query the parent table, and then iterate through the resulting data set, and for each record, query each of the child tables for that PersonId.
That works, but it feels really inefficient. Like it's something that the database server could do in a highly optimised way, and I'm clunking about going backwards and forwards to the server dozens of times doing it manually.
Have I missed something really obvious, or is this just an unusual set of circumstances and my clunky way of doing it as efficient as it's going to get?