I have a series of classes that look like this:

public class SignUpList {
    public int? SignUpListId { get; set; }
    public IEnumerable<SignUpItem> Items { get; set; }
}

public class SignUpItem {
    public int? SignUpItemId { get; set; }
    public int? SignUpListId { get; set; }
    public IEnumerable<ItemPerson> Persons { get; set; }
}

public class ItemPerson {
    public int? ItemPersonId { get; set; }
    public int? SignUpItemId { get; set; }
    public int PersonId { get; set; } //fk reference to another table
}

(Think of a scenario like Sign Up Genius: I have a list created called “Classroom Party”. On the list I have “Plates”, “Forks”, and “Napkins”. Then 4 people can sign up for each).

Now, when my API endpoint that uses the SignUpList is hit, I’m happy to use some UDT’s as parameters and merge all my records into my existing tables.

I’m having an issue with the logic for how to insert a record for the first time when I don’t already have an ItemPersonId on the ItemPerson.

For example:

I can save my SignUpList and get back the SignUpListId using something like this with Dapper:

    var listId = ExecuteScalar<int>("dbo.SaveList", parameters, commandType: StoredProcedure) 

And then I can populate that into a UDT for inserting the SignUpLists (or even insert those in the same query). My issue comes from the next layer of nesting:

How do I get a SignUpItemId to put onto my persons? I can’t just shove them in a UDT because I’ll have no way to know which SignUpItem the person ties back to.

I could do some foreach looping shenanigans –

    foreach (var item in SignUpList.Item) {
        var itemId = ExecuteScalar("[dbo].[SaveItem]", parameters); //assume I've created appropriate parameters
        //Insert into my DataTable based off the UDT for ItemPerson
    }
    _ = ExecuteAsync("dbo.SavePersons", params) //(params being UDT table populated in the foreach above)

But that approach is costly as far as database operations go.

Another option I’ve considered is a mass insert of ALL the SignUpItems in one go, but then how do I tie those ID’s back to my existing list? (I don’t think I can make a new list of SignUpItem based on the return because it won’t have the Person tied to the items, which is the whole issue).

I also considered using a “row number” and using that as a pseudoId to link them. It would require adding a new field to the parent item but may be the appropriate play if I’m trying to minimize db connections?

Is there something really simple I’m missing in Dapper that can handle this more effectively?

Unfortunately, it’s not a viable option to require saving the List vs Items separately due to the nature of the application, otherwise that would be my preferred approach.

If a different data structure would be better, I’m also willing to consider it, but I struggle to envision a way to make this logic work without triple nesting. I also considered doing ItemPerson as a serialized JSON field on the SignUpItem but I need to be able to query from it as well.