Inserting one-to-many relationships in a SQL database with C# or F#

• After a long affair with nosql databases, including a particularly long infatuation with the document database CouchDB, I’m finally coming to the realization that you just can’t beat a good old fashioned SQL database.
• I’ll write a post in the future about why I’m migrating my projects back to SQL Server, but I wanted to write this quick article to detail some of the issues I’ve had recently that stalled my nosql exodus.
• Additionally, for reasons I won’t get in to in this post, I’m not using Entity Framework or any other ORM to manage my database. I prefer to write all of my SQL by hand, so that I can validate and make sure the operations are executing the way I want them to. If you’ve ever used an ORM, you may have already run into situations where the performance of the database implodes due to a sufficiently “weird” ORM operation generating sufficiently wacky SQL behind the scenes.
• So if Entity Framework and other ORMs are not an option, either because of preference or other limitations, what’s the best way to insert a new object with multiple children all at once? That is, how do you do it without making multiple insert calls — one for the parent, and one more for every child? What if you want to validate things based on other data in the database, again without making a bunch of round trips to the SQL server?
• First, let’s start off by defining our SQL table schemas and backing .NET classes/records. For the sake of simplicity, let’s say we’re working with a database that tracks members of a household; there will be one household with many members. Here’s what the SQL table schema looks like:

CREATE TABLE Household (
    Id int identity(1,1) not null,
    StreetAddress nvarchar(500) not null,
    HasGarage bit not null,
    HasFence bit not null
)

CREATE TABLE Members (
    Id int identity(1,1) not null primary key,
    HouseholdId int not null foreign key references Household(Id),
    FirstName nvarchar(500) not null,
    LastName nvarchar(500) not null,
    DateJoined datetime(2) not null
)

And we want to map rows of those tables into the following .NET models:

namespace Models 
{
    public class Household
    {
        public int Id { get; set; }

        public string StreetAddress { get; set; }

        public bool HasGarage { get; set; }

        public bool HasFence { get; set; }

        public IEnumerable<Member> Members { get; set; }
    }

    public class Member
    {
        public int Id { get set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public DateTime DateJoined { get; set; }
    }
}

So that’s what the tables and models look like. Now let’s devise a function that will create a new household by inserting it and its members into the database. A naive implementation might look something like this:

public async Task<void> CreateHouseholdAsync(Household newHousehold)
{
    int newHouseholdId;

    using (var sqlCmd = new SqlCommand("CONNECTION_STRING"))
    {
        sqlCmd.CommandText = @"
            INSERT INTO Households (
                StreetAddress,
                HasGarage,
                HasFence
            ) 
            OUTPUT INSERTED.Id 
            VALUES (
                @streetAddress,
                @hasGarage,
                @hasFence
            )
        ";

        sqlCmd.AddParameter("@streetAddress", newHousehold.StreetAddress);
        sqlCmd.AddParameter("@hasGarage", newHousehold.HasGarage);
        sqlCmd.AddParameter("@hasFence", newHousehold.HasFence);

        var result = await sqlCmd.Execute();

        // TODO: read result into newHouseholdId
    }

    // Insert the household members
    foreach (var member in newHousehold.Members)
    {
        using (var sqlCmd = new SqlCommand("CONNECTION_STRING"))
        {
            sqlCmd.CommandText = @"
                INSERT INTO Members (
                    HouseholdId,
                    FirstName,
                    LastName,
                    DateJoined
                ) VALUES (

                )
            ";
        }
    }
}

• this function is naive and requires many trips to the database

• The answer is to use SQL’s user-defined types and stored procedures.