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?

Why do you want the server to generate it? Why not generate it in C#? — mjwills, Jul 24 '19 at 13:53
https://github.com/ericdc1/Dapper.SimpleCRUD/ mentions `Insert(entity) - Inserts a record and returns the new guid primary key` Have you tried that? — mjwills, Jul 24 '19 at 13:55
@mjwills I was under the impression that performance will be better if the Db creates the PK by itself? Also, it feels like the PK generation is outside the scope of the DAL; this is the concern of the Db itself — WynDiesel, Jul 24 '19 at 13:55
@mjwills Haven't looked at that. This is a green-fields project, so if I can't manage to resolve this, I'll look at using SimpleCRUD. Thanks! — WynDiesel, Jul 24 '19 at 13:58
`Also, it feels like the PK generation is outside the scope of the DAL` One of the benefits of a GUID is that the client **can** take care of it, it doesn't need to rely on the DB doing it (unlike say an IDENTITY). — mjwills, Jul 24 '19 at 14:00
@mjwills. On the point of being able to generate client side PK's, fair enough. Is my assumption about the performance of the PK/index incorrect though? — WynDiesel, Jul 24 '19 at 14:03
I don't know enough about Postgres to comment on that, alas. — mjwills, Jul 24 '19 at 14:06
Generating a GUID is fairly quick, however, if you really NEED to micro-optimize, then it is likely faster to have the application generate it than the database. Depending on your infrastructure, it is usually much easier/cheaper to scale out the application than to scale up the database server. — Robert McKee, Jul 24 '19 at 14:13
@RobertMcKee I doubt the _generation_ is the issue - more an issue of whether the DB generates GUIDs in a way to make indexing more performant etc. — mjwills, Jul 24 '19 at 14:15
@mjwills The answer is likely not since he's using version 4 uuids which are (mostly) random. But that bring me back to my point which is that uuid_generate_v4 is a measurably slow method (approx .3ms depending on hardware of course). Moving that to the client would be a micro-optimization, but something I would just do because there is very little reason not to. — Robert McKee, Jul 24 '19 at 17:14
I'm going to advocate generating the PK in the database, whether serial or UUID as I think there is some very good reasons for not moving it to the client. On the database the appropriate type is no matter how the row in created. This is true whether the row is generated by the application, by a later application (and data structured tend to be longer lived than applications), or application, a quick fix directly on the database. For performance how long does it take the client to generate a UUID. As for a indexing that will be the same either way, always in DB and the type selected. Just MHO — Belayer, Jul 24 '19 at 19:02
I wasn't suggesting removing the default from the database, just as an optimization, you can have the client do the actual UUID generation. Later applications can either let the database do the generation, or generate their own as well. All depends on scale. If you let the database generate them, then you have a pretty hard limit of only being able to insert 3000 records/s per core in your database server assuming your db is doing nothing else. Letting your client(s) do the generation, you can scale out a lot easier, but huge scale, huge data is the norm for my projects so I'm biased. — Robert McKee, Aug 09 '19 at 20:28

Amit Joshi · Accepted Answer · 2019-07-24T14:44:53.140

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" });

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

1 Answers1