title: Dapper multi insert returning inserted objects
body: <p>Using <a href="https://github.com/StackExchange/dapper-dot-net" rel="noreferrer">Dapper</a> I would like to implement a method that takes an <code>IEnumberable</code> of objects of type <code>User</code>. Now, <code>User</code> looks as follows:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>The point here is that <code>UserId</code>, <code>DateCreated</code> and <code>DateChanged</code> shall never be set via object, hence the <code>internal</code> keyword. Instead the database will populate these values.</p>
  
  <p>Because the objects are therefore modified as part of the insert operation I want to return another <code>IEnumerable</code> of objects of type <code>User</code> but this time with the corresponding properties populated.</p>
  
  <p>Recently I realized that I can let Dapper loop through the <code>User</code> objects in the <code>IEnumerable</code> as follows:</p>
  
  <pre><code>public int Insert(IEnumerable&lt;User&gt; users)
  {
    string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username)");
    return GetOpenConnection().Execute&lt;User&gt;(sql, users);
  }
  </code></pre>
  
  <p>This is neat because I do not have to write the <code>foreach</code> myself. Now, the problem here is that <code>Execute</code> will only return the number of actually inserted rows.</p>
  
  <p>So I tried it using <code>Query</code> as follows:</p>
  
  <pre><code>public IEnumerable&lt;User&gt; Insert(IEnumerable&lt;User&gt; users)
  {
    string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username) SELECT * FROM [User] WHERE UserId = scope_identity()");
    return GetOpenConnection().Query&lt;User&gt;(sql, users);
  }
  </code></pre>
  
  <p>However, this simply throws an <code>InvalidOperationException</code> exception with message "An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context".</p>
  
  <p>I'm stuck with this. How can I make this work?</p>
  
  <p>Do I have to loop through my input <code>IEnumerable</code> executing <code>Query</code> for each object inside loop body? This way the <code>IDbTransaction</code> parameter of the <code>Query</code> method would be useless if I want to insert all <code>User</code> objects in the same transaction so I'd have to wrap the entire loop in a transaction instead of passing the transaction to <code>Query</code>.</p>
  
  <p>What is the "proper" way to insert multiple objects using Dapper and return the fully populated objects back to the caller?</p>