How do I get back the key for a newly inserted record using Dapper.Contrib?

Question

I have code like this:

db.Insert(myObject);

The key for the type myObject is of type System.Guid which is auto generated by RDBMS.

I then want to insert child objects that will use that key to relate to their parents.

foreach(var child in myObject.ChildObjects) {
   child.ParentId = parentIdThatISomehowSaved; //this is what I don't know how to do
   db.Insert(child);
}

I am assigning a new Guid to the record via SQL Server when the insert occurs.

What is the best way to get the Id of the new record so I can populate it as a foreign key on child objects? The Dapper.Contrib.Extensions.Insert method returns a long, so is there a way to make this work with a Guid?

Answer 1

UPDATE: 02-May-2022

From source code, it appears that this feature is now implemented. I have not tested it though.
It appears that Insert<T> method will return (it will NOT map to entity property) newly generated numeric ID (return type of method is long) if only single entity is being inserted. If list of entities are being inserted, it will return number of rows inserted.

I am not sure how will this work with GUID IDs.

Following is the code-comment:

/// Inserts an entity into table "Ts" and returns identity id or number of inserted rows if inserting a list.  
...  
...  
/// <returns>Identity of inserted entity, or number of inserted rows if inserting a list</returns>

Also, observe the code that inserts single entity and should return newly generated ID as per code-comment above:

if (!isList)    //single entity
{
    returnVal = adapter.Insert(connection, transaction, commandTimeout, name, sbColumnList.ToString(),
    sbParameterList.ToString(), keyProperties, entityToInsert);
}
else
{
    //insert list of entities
    var cmd = $"insert into {name} ({sbColumnList}) values ({sbParameterList})";
    returnVal = connection.Execute(cmd, entityToInsert, transaction, commandTimeout);
}

---

Original Answer:

The Insert method of Dapper Contrib does not return the newly generated ID. This is because Contrib is built over Dapper which neither map the newly generated ID to the entity by default nor does it returns it. But there are ways to return the newly generated ID using Dapper. Those are discussed here, here and here.

_{Dapper Extensions (other query generator for Dapper) support this by default. Please refer to this answer for more details.}

One way is to bypass the Contrib and use Dapper; use any of the way explained in links to get the newly generated ID (OUTPUT parameter or @@IDENTITY etc.).

Other way to do this with Contrib is to get the newly generated ID by separately calling Dapper (as mentioned in links; @@IDENTITY) immediately after a call to Insert.

Third way is to explicitly assign the ID instead of automatically generating it. You can use [ExplicitKey] attribute to achieve this. You also need to change your database accordingly.

[Key] should be used for database-generated keys (e.g. autoincrement columns), while [ExplicitKey] should be used for explicit keys generated in code.

More aggressive solution is to modify the Contrib code to make Insert return newly generated ID.

By the way, I found an issue similar to your problem; see if that helps you.