Dapper multi insert returning inserted objects

Question

Using Dapper I would like to implement a method that takes an IEnumberable of objects of type User. Now, User looks as follows:

public class User
{
  public int UserId { get; internal set; }
  public DateTime DateCreated { get; internal set; }
  public DateTime DateChanged { get; internal set; }
  public string Username { get; set; }
}

The point here is that UserId, DateCreated and DateChanged shall never be set via object, hence the internal keyword. Instead the database will populate these values.

Because the objects are therefore modified as part of the insert operation I want to return another IEnumerable of objects of type User but this time with the corresponding properties populated.

Recently I realized that I can let Dapper loop through the User objects in the IEnumerable as follows:

public int Insert(IEnumerable<User> users)
{
  string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username)");
  return GetOpenConnection().Execute<User>(sql, users);
}

This is neat because I do not have to write the foreach myself. Now, the problem here is that Execute will only return the number of actually inserted rows.

So I tried it using Query as follows:

public IEnumerable<User> Insert(IEnumerable<User> users)
{
  string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username) SELECT * FROM [User] WHERE UserId = scope_identity()");
  return GetOpenConnection().Query<User>(sql, users);
}

However, this simply throws an InvalidOperationException exception with message "An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context".

I'm stuck with this. How can I make this work?

Do I have to loop through my input IEnumerable executing Query for each object inside loop body? This way the IDbTransaction parameter of the Query method would be useless if I want to insert all User objects in the same transaction so I'd have to wrap the entire loop in a transaction instead of passing the transaction to Query.

What is the "proper" way to insert multiple objects using Dapper and return the fully populated objects back to the caller?

Answer 1

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