Sqlite-net: Am I crazy or is the Documentation for PrimaryKey AutoIncrement wrong?

[pha3z]

The wiki example says this:

public class Stock { [PrimaryKey, AutoIncrement] public int Id { get; set; } public string Symbol { get; set; } }

From what I can tell, there's no way that code will even work. It should be this:

public class Stock { [PrimaryKey, AutoIncrement] public int? Id { get; set; } public string Symbol { get; set; } }

If the integer is not nullable, then sqlite-net has no way to know that you didn't intentionally set Id to a default value of 0. To get AutoIncrement to work, it has to be null.

Am I wrong on this? If not, I'm going to fix the wiki.

[pha3z]

One more thing, wouldn't it also be better to use "long" than "int"? Under-the-hood, the sqlite table is using a 64-bit value if its following the ROWID pattern described in sqlite docs. Is this what's going on also with sqlite.net ?

[pha3z]

Ok apparently Insert and InsertOrReplace() have different functionality. And I guess this has been discussed on other posts. This is very weird. InsertOrReplace() requires Id to be nullable but Insert does not. Very confusing.

[praeclarum]

Yeah, it's a little weird. You're welcome to use long (or ulong). I just don't bother in the example.

You can think of it as Insert always inserts and therefore gets a new PK while InsertOrReplace depends on you knowing how things work. Obviously that's not ideal.

I'll keep this open and let others chime in once again. I would like to support non-nullable integer PKs with InsertOrReplace so could special case the value 0? Use that as a placeholder indicating it should always insert?

As far as ROWID is concerned, this library doesn't bubble it up at the ORM level. You're welcome to use it in queries. I did always mean to add an attribute so you can map a property to it...

[pha3z]

Well, I like InsertOrReplace() very much. But I tend to write my own wrappers around everything I use (including sqlite.net), so I was happy to just rely on InsertOrReplace() and never use Insert() once I understood how it worked.

If the current functionality stands, then I think the explanation of InsertOrReplace() should be added to the starting documentation and the wiki. I feel like my main issues as a beginner with this library were just the lack of documentation culminating in a few surprises because I didn't know what to expect from the methods.

Your idea of having a special case value that always causes an insert is interesting. Doesn't sound like it would produce readable code, though. I try really hard to avoid code that isn't immediately apparent about its intent.