PetaPoco - How to turn off auto increment?

Question

I ran into this error when trying to do an insert:

Cannot insert the value NULL into column 'Id'

It turns out PetaPoco by default assumes the Id column is auto increment, so even if you provide a value it will attempt to insert null instead. I found a bug ticket for the problem here: https://dnntracker.atlassian.net/browse/DNN-23217.

I'm using PetaPoco's T4 template to generate my database classes. I created a partial class an applied the data annotation to disable auto increment:

[PrimaryKey("Id", autoIncrement = false)]
public partial class Blah : DatabaseDB.Record<Database.Blah>
{
}

However it seems to have no effect. PetaPoco is still trying to insert null for the Id column when I am specifying an integer.

PetaPoco maintainer here. What version are you using? Are you using Insert or the Save method? If it's a bug, can you raise an issue on the GH repo so we can fix it? — Plebsori, Jan 14 '16 at 07:00
[Github report link](https://github.com/CollaboratingPlatypus/PetaPoco) in case you want to raise an issue — Plebsori, Jan 14 '16 at 07:35

Faris Zacina · Accepted Answer · 2014-11-23T22:48:02.247

I agree It is a really strange and confusing behavior because their API is not always using that attribute.

There are two ways to make it work.

One is by not using the attribute in your example and using the overloaded method with the autoIncrement parameter, and setting it to false. Here is a full example:

// Create a PetaPoco database object
var db = new PetaPoco.Database("ConnectionSt");

// Create an article
var a = new Article
{
    article_id = 152,
    title = "My new article",
    content = "PetaPoco was here",
    date_created = DateTime.UtcNow
};

// Insert it by turning off auto-increment
db.Insert("articles", "article_id", false, a);

The other is to use the insert method that takes an object as a parameter:

// Create an article
var a = new Articles
{
    article_id = 1111,
    title = "My new article",
    content = "PetaPoco was here",
    date_created = DateTime.UtcNow
};

// Insert it by using the insert method that will use the attribute!
db.Insert(a);

The overload that takes an object as a parameter is the only one that uses the PrimaryKey attribute internally, and it should work like a charm in your scenario.

Thanks, both of these methods work. It seems like it would be trivial to check if a column is set to Identity or not to handle this automatically, but maybe there is more to it than that. — Sgraffite, Nov 24 '14 at 00:49
Yeah it's an interesting one. We've got integration tests for both types of IDs. — Plebsori, Jan 14 '16 at 07:38

PetaPoco - How to turn off auto increment?

1 Answers1