Dapper multi insert returning inserted objects
to insert or update List of object with Dapper.Net you can't use Query
connection.Query<Object>("your_query",your_list)
//connection.Query<Object>: use to select IEnumrable<object> from db
//connection.QueryMultiple: use to execut multiple query at once then read result one by one
var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";
using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
var customer = multi.Read<Customer>().Single();
var orders = multi.Read<Order>().ToList();
var returns = multi.Read<Return>().ToList();
...
}
you should use only Execute for multi insert or update
Execute("your_query",your_list, your_transaction);
so if you need to multi insert and return IDs for inserted records
// **using transaction depend on your needs**
//Example to multi insert and return full record
string query = @"Insert Into _TableName ( _columns)
OUTPUT INSERTED.*
values ( _parameters )"; //parameters should be same as object properties name to let dapper do correct mapping
[OUTPUT INSERTED.*] will return full insert row with id and you are free to return any property by replace asterisk with propertyname [OUTPUT INSERTED.Id] will return only id
// will be good for small list
for (int i = 0; i < youList.Count-1; i++)
{
youList[i] = DbConnection.Query<object>(query, youList[i]).FirstOrDefault();
} // for loop is better for preformance
//for big List you can use SqlBulkCopy review this link here