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.

Answer 1

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.