How to insert an object into PostGreSql using Dapper Extensions without specifying primary key explicitly?

Question

When I want to insert an object into my database, I receive an error:

object reference not set to an instance of an object

when the primary key field for the object is null. When I set some value to the field, it works fine.

My table creation :

create table Appointment
(
    AppointmentUUID uuid DEFAULT uuid_generate_v4 () primary key,
};

My class :

public class Appointment 
{
    public Guid? AppointmentUUID { get; set; }
    public void Insert(NpgsqlConnection conn)
    {
        var i = conn.Insert(this);            
    }
}

My calling code :

var lConnection = new NpgsqlConnection(connstring);
lConnection.Open();

var a = Appointment.Get2(allAppoints.FirstOrDefault().AppointmentUUID.Value, lConnection);
var newApp = new Appointment();
newApp.Insert(lConnection);

Surely there must be a way I can insert an object and let the database create it's own Guid. What am I missing?

Answer 1

Two corrections:

First, you do not need to make AppointmentUUID nullable. So just remove that like:

public Guid AppointmentUUID { get; set; }

Second, you may need to map the AppointmentUUID as Key:

Map(x => x.AppointmentUUID).Key(KeyType.Guid);

This will tell Dapper Extensions to generate the key (Guid) on itself.

Dapper Extensions comes with default mappings out of the box which is based on conventions. But, obviously, you have to extend it a little to handle some cases. Mapping is simple, you do not need to map each column; just the special columns.

Also note that your key property ends with "ID" and type is Guid. Dapper Extensions should automatically take it as Key without mappings. Just in case you have other property in your model that also ends with "ID", that may be the problem. Personally, I always prefer to map explicitly; just in case I add something in future; existing stuff should not start behaving oddly.

In your case, mapping can be done something like below:

internal sealed class AppointmentMapper : ClassMapper<Appointment>
{
    public AppointmentMapper()
    {
        Table("Appointment");
        Map(x => x.AppointmentUUID).Key(KeyType.Guid);//<--Just map this
        AutoMap();//<-- This will take care about all other columns you do not map explicitly; ofcouse based on conversions
    }
}

Then, call following at project startup:

DapperExtensions.DapperExtensions.SetMappingAssemblies(new[] { "Your Assempby Here" });