How do I insert multiple records using Dapper while also including other dynamic parameters?

Question

Here is a truncated example of what I'm trying to do:

var stuffTOSave = new List<SomeObject> {
    public int OtherTableId { get; set; }
    public List<Guid> ComponentIds { get; set; }
};

var sql = @"CREATE TABLE Components( ComponentId uniqueidentifier PRIMARY KEY )
INSERT INTO Components VALUES (@WhatGoesHere?)

SELECT * FROM OtherTable ot
JOIN Components c on c.ComponentId = ot.ComponentId
WHERE Id = @OtherTableId

DROP TABLE Components"

Connection.Execute(sql, stuffToSave);

I know from other SO questions that you can pass a list into an insert statement with Dapper, but I can't find any examples that pass a list as well as another parameter (in my example, OtherTableId), or that have a non-object list (List<Guid> as opposed to a List<SomeObject> that has properties with names to reference).

For the second issue, I could select the ComponentIds into a list to give them a name like:

stuffToSave.ComponentIds.Select(c => new { ComponentId = c })

but then I'm not sure what to put in my sql query so that dapper understands to get the ComponentId property from my list of ComponentIds (Line 7)

score 1 · Accepted Answer · answered Jan 24 '20 at 18:47

I would still like to know the real way of accomplishing this, but I have this workaround that uses string interpolation:

var sql = $@"CREATE TABLE Components( ComponentId uniqueidentifier PRIMARY KEY )
    INSERT INTO Components VALUES ('{string.Join($"'),{Environment.NewLine}('", request.ComponentIds)}')

    SELECT * FROM OtherTable ot
    JOIN Components c on c.ComponentId = ot.ComponentId
    WHERE Id = @OtherTableId

    DROP TABLE Components"

I'm not worried about SQL Injection since this is just interpolating a list of Guids, but I'd rather avoid this method if possible.

How do I insert multiple records using Dapper while also including other dynamic parameters?

1 Answers1