New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
InsertOrReplace ignores PrimaryKey AutoIncrement #327
Comments
Hi There Please have a look at #312 |
Hi, I don't believe they are related. #312 says it always returns 1 where I am not worried about what it is returning, but more on what is happening. Just had a look at the source code, and yes it is using the Insert method, but the issues are still unrelated. |
Hack the SQLite.cs code. Comment line 1930: // decl += "autoincrement "; in the class for your tables, declare your primary key as... [PrimaryKey, AutoIncrement]
public int Id { get; set; } ...after INSERT, because the AutoIncrement attribute, sqlite-net copy the id value to the field Id of the instance, where you can read it. SQLite 3.8.x automatically generates a new key for fields of type INTEGER PRIMARY KEY (ascending) if the value is not given in the INSERT, no need "AUTOINCREMENT". With this hack, sqlite-net sends only INTEGER PRIMARY KEY with CREATE TABLE command for fields [PrimaryKey, AutoIncrement]. (sorry by my "english"). |
If I understand your question correctly, the behavior you describe is a feature of SQLite. SQLite allows you to specify values for the primary key column. Because |
Sorry to dig that post but it appears that the problem is still there. I didn't find any answer to that on Google or here.
|
Yes, the using (var dbConn = new SQLiteConnection(_dbPath))
{
var item = dbConn.Table<DbType>().FirstOrDefault(r => r.Id == id);
if (item != null)
{
item.Property = newValue;
dbConn.Update(item, typeof(DbType));
}
else
{
item = new DbType() { Property = newValue, };
dbConn.Insert(item, typeof(DbType));
}
} |
Thanks @ColonelDuddits making PK nullable made InsertOrReplace work correctly. |
Making the ID nullable did work for me too. I've changed something like this:
to something like this:
But I think it is really "ugly" to use nullable IDs in my code (always using |
Thanks @ColonelDuddits, @Kwoth making PK nullable worked for me. |
Well it is not working as is should, the null id get always incremented by 1, whats the point, it should work like insert or update, if it is the some record, don't update the id. |
This is looking like a bug to me. I'll take a look... |
I confirmed this bug is still present in v1.6.258-beta. Following @UweKeim's work-around and using a nullable int, e.g. [PrimaryKey, AutoIncrement]
public int? ID { get; set; } |
It's 2021 and this is still a problem and a barrier to new users. I would still be pulling my hair out had I not found this issue. Requiring the use of a nullable integer for an autoincremented primary key violates the concept of a primary key. |
I've resorted to always using nullable int for primary key. |
No need for InsertOrReplace. Wait for InsertAsync.... var T = DatabaseAsyncConnection.InsertAsync(object); Guaranteed to work. |
+1 from me that this bug is very annoying.
|
+1 from me. Spent a whole day breaking my head to find out this was actually a very annoying bug. |
This fixes the issue for me |
If I use InsertOrReplace to insert a new record, which has a primary key of 0, it inserts the record with 0 instead of using the next available id.
The text was updated successfully, but these errors were encountered: