How should I handle one to many relationships to fill a data model

Question

I'm trying to determine how I should handle a one to many relationship in my DB, when using the data to build a model in C#. Ideally, I'd like to make a single call to the DB. However, it seems that two (or more) calls might be required.

For simplicity, assume my tables look like this...

CREATE TABLE [dbo].[Users]
(
    [userId] INT PRIMARY KEY IDENTITY(0,1),
    [userName] NVARCHAR(500) NOT NULL
)

CREATE TABLE [dbo].[Tasks]
(
    [taskId] INT PRIMARY KEY IDENTITY(0,1),
    [description] NVARCHAR(1000) NOT NULL,
    [userId] INT FOREIGN KEY REFERENCES [dbo].[Users](userId)
)

So each user can have many tasks. I have a stored procedure that will return the details of a user, that looks like this...

CREATE PROCEDURE [dbo].[sp_GetUserDetail]
    @userId INT
AS
BEGIN
    SELECT
        [dbo].[Users].[userName] AS 'User',
        [dbo].[Tasks].[description] AS 'Task Description'
    FROM
        [dbo].[Users]
    INNER JOIN
        [dbo].[Tasks]
    ON
        [dbo].[Tasks].[userId] = [dbo].[Users].[userId]
    WHERE
        [dbo].[Users].[userId] = @userId
END

This procedure returns as many rows, as tasks that are assigned to a user. The model I'm trying to fill, would look something like this.

public interface User
{
    string Name { get; set; }
    List<string> Tasks { get; set; }
}

I see my options as follows:

1. Use this code, and loop through the rows that are returned from the DB to build the Tasks list.
2. Call one stored procedure to return the data from the Users table, then another to get the data from the Tasks table.
3. Some (unknown to me magic) way to have a single stored procedure return all the data in a single row.
4. Some other option I don't even know about.

How is this problem typically handled by experienced Developers?

Answer 1

There are some language/framework specific answers which I won't cover (because C# is not my forte), but it's worth looking at "data binding", which is one of the features of the .Net framework. You could also look at ORM tools for C#.

The example you give - "how do I load child information for my parent" - is common, and you have to trade the number of database queries against the amount of data each query returns, and the complexity of your user interface code. For instance, if tasks have foreign keys to sub tasks (i.e. a self join), and task_type, and project_id, you have either:

• 1 query per table (your option 1): simplest to implement in the UI layer, simplest to implement in the database layer, but could easily cause dozens of database calls per screen.
• 1 query to retrieve all data for the screen (your option 2): single database hit, so should be faster, but complex UI and database logic; could potentially load the entire database into memory if you keep following foreign key relationships. Not all data may be necessary for the screen.

There is no "right" answer to this - it really depends on your application design.

However, there is an option you haven't mentioned (this is SQL Server-specific): a stored procedure can return multiple result sets. So, you could have one result set to provide the "header" data (user information), and one to provide task information.