Dapper how to insert multiple rows with same foreign key

I have a child table that rerferences a parent table with a 1 to many relationship.
PK -> multiple foreign keys. The reason is the FK table are a list of objects related to an ID on the parent table.

How can I use dapper to insert multiple rows with the same FK?

assessmentModel.locationInformationModels = new List<LocationInformationModel>();

string sqlStatement = @"INSERT INTO LocationInformation
                                ([LocationInformationId]                          
                                ,[Address1]
                                ,[Address2]
                                ,[City]
                                ,[State]
                                ,[Zip])
                                VALUES(
                                @LocationInformationId,@Address1,@Address2,@City,@State,@Zip)";            
         
using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var ts = connection.BeginTransaction())
                {
                    var retVal = await connection.ExecuteAsync(sqlStatement, assessmentModel.locationInformationModels, transaction:ts);
                    ts.Commit();
                    return retVal;
                }        
            }

The question is a bit unclear. The code shows inserting a list of items without anything that looks like a foreign key. In any case, Dapper is a microORM, it doesn't handle relations between entities, or auto-update keys. It won't change the supplied SQL statement to handle multiple objects either.

I suspect the real question is how to return a generated value from the database, eg an IDENTITY key, although the code shows that LocationInformationId is supplied by the application. To do this, the query will have to be modified to return any generated value, eg with the OUTPUT clause.

Returning generated values

For example, this will return the auto-generated ID :

var sql = """
    INSERT INTO LocationInformation
        ([Address1]
        ,[Address2]
        ,[City]
        ,[State]
        ,[Zip])
    OUTPUT inserted.LocationInformationId
    VALUES(@Address1,@Address2,@City,@State,@Zip)
    """;

The question's code already uses ExecuteAsync(sql, IEnumerable<T>,...) to insert multiple items. That executes the query once for every item but doesn't return any results. There's no equivalent for QueryAsync, so the application will have to execute the INSERTs explicitly :

foreach(var item in items)
{
    var newId=await connection.QueryAsync(sql,item);
    item.Id=newId;
}

Row constructors

It's possible to insert multiple rows with a single INSERT using row constructors, but that requires generating the SQL query dynamically :

    INSERT INTO LocationInformation
        ([Address1]
        ,[Address2]
        ,[City]
        ,[State]
        ,[Zip])
    VALUES
    OUTPUT inserted.LocationInformationId
    (@Address1_01,@Address2_01,@City_01,@State_01,@Zip_01),
    (@Address1_02,@Address2_02,@City_02,@State_02,@Zip_02),
    (@Address1_03,@Address2_03,@City_03,@State_03,@Zip_03),
    ...

All item values will have to be flattened by the application to a list of parameters too. Not very helpful.

Table valued parameters

Another option is to use a table-valued parameter type parameter to insert multiple values passed as a DataTable at once. This requires creating a table valued parameter type in the database and converting the data into a DataTable, eg :

CREATE TYPE dbo.LocationTableType AS TABLE  
    ( Address1 nvarchar(50), ...  ) 

The query should change to an INSERT ... SELECT using the table parameter :

var sql = """
    INSERT INTO LocationInformation
        ([Address1]
        ,[Address2]
        ,[City]
        ,[State]
        ,[Zip])
    OUTPUT inserted.LocationInformationId
    SELECT Address1,Address2,City,State,Zip 
    FROM @table
    """;

MoreLinq's ToDataTable can be used to convert the input items to a DataTable. After that, a QueryAsync call will execute the INSERT and return the list of outputs :

var table=items.ToDataTable();
var newIds=await connection.QueryAsync(sql,table);

If I understand you correctly you want to do inserts on the table in batches. Pure Dapper does not have any built in way to do bulk inserts, so your options would be either using Dapper-Plus or SqlBulkCopy for all bulk actions you want to perform.

With SqlBulkCopy you could set up a dynamic columnmapping using reflection to avoid having to define all columns explicitly.