Return inserted item's UUID back from Dapper/PostgreSQL

Question

I have a model object like so in my ASP.Net Core 2.1 Web API Project:

public class Tenant
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

And am executing an insert into postgres like this:

public async Task<int> CreateItem(Tenant item)
{
    return await db.ExecuteAsync($@"INSERT INTO tenants (Name) VALUES (@Name)", item);
}

I need the Guid back, to drive the subsequent logic. It returns the affectedrows (1). My research shows a few tricks being used for getting back the autoincrement ID (like using 'MAX' and so forth).

Is there a way to do this, via dapper preferably? Or do I need to drop back to ADO.Net?

Answer 1

You should be able to use the RETURNING clause of the INSERT statement to have Postgres return the inserted id.

public async Task<Guid> CreateItem(Tenant item)
{
    return await db.ExecuteScalarAsync<Guid>($@"INSERT INTO tenants (Name) VALUES (@Name) RETURNING Id", item);
}