Insert with AutoIncrement

Question

I am trying to insert some data into my SQLite database which works perfectly when I do this with one record. But inside a loop I get an Error. First, here is the code

string dataSource = "Data Source=";
Connection = new SQLiteConnection(dataSource + this.DatabasePath);

var context = new DataContext(Connection);

var users = context.GetTable<User>();


for (int i = 0; i < 2; i++) {
    User tempUser = new User() {
        ID = null,
        EMail = i + "@" + i + ".de",
        Password = "Test1234",
        JoinedDate = DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss")
    };

    users.InsertOnSubmit(tempUser);
    context.SubmitChanges();
}

And the User itself

[Table(Name = "User")]
public class User {

    [Column(Name = "UserID", IsPrimaryKey = true, CanBeNull = false)]
    public int? ID { get; set; }

    [Column(Name = "EMail", CanBeNull = false)]
    public string EMail { get; set; }

    [Column(Name = "Password", CanBeNull = false)]
    public string Password { get; set; }

    [Column(Name = "JoinedDate", CanBeNull = false)]
    public String JoinedDate { get; set; }

    [Column(Name = "PaymentMethodID")]
    public int PaymentMethodID { get; set; }
}

The Table is created like this

CREATE TABLE "User" (
`UserID`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`EMail` TEXT NOT NULL,
`Password`  TEXT NOT NULL,
`JoinedDate`    TEXT NOT NULL,
`Licenses`  INTEGER,
`PaymentMethodID`   INTEGER
)

And finally the Error I get:

An exception of type 'System.Data.Linq.DuplicateKeyException' occurred in System.Data.Linq.dll but was not handled in user code

Additional information: Eine Entität, deren Schlüssel bereits verwendet wird, kann nicht hinzugefügt werden.

I could bet, that this is happening because of the Field ID, which is set to AutoIncrement.

Possible duplicate of [Error using System.Data.Linq.Mapping and auto incrementing the primary key in a sqlite db](http://stackoverflow.com/questions/31665788/error-using-system-data-linq-mapping-and-auto-incrementing-the-primary-key-in-a) — Salah Akbari, May 11 '16 at 10:20
You should have `public int ID { get; set; }` (not nullable) and mark the property as `DatabaseGenerated` = `Identity`. — Gert Arnold, May 11 '16 at 12:23

Nitin Chotaliya · Answer 1 · 2016-05-11T12:12:50.120

0

You just remove ID = null, Because this is Auto-increment and not need to write this. Alter table "User" remove NOT NULL check in Auto-increment column ID and also remove in class "User". ID column is Auto-increment then not need check with NOT NULL constraint.

for (int i = 0; i < 2; i++) {
    User tempUser = new User() {
        EMail = i + "@" + i + ".de",
        Password = "Test1234",
        JoinedDate = DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss")
    };

edited May 11 '16 at 12:12

answered May 11 '16 at 11:50

Nitin Chotaliya

11
2

I did, but still the same Error – DirtyNative May 11 '16 at 11:56
Please remove NOT NULL check in UserID => `UserID` INTEGER PRIMARY KEY AUTOINCREMENT, – Nitin Chotaliya May 11 '16 at 12:01
You need to remove from both table "User" and class "User" – Nitin Chotaliya May 11 '16 at 12:14
Sure, i did. I also added `[Column(Name = "UserID", IsPrimaryKey = true, CanBeNull = true)]` above `UserID` – DirtyNative May 11 '16 at 12:16
Please use like this [Column(Name = "UserID", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY")] – Nitin Chotaliya May 11 '16 at 12:32
with @NitinChotaliya piece of code I am getting the error `An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.Linq.dll but was not handled in user code Additional information: SQL logic error or missing database near "SELECT": syntax error` – DirtyNative May 11 '16 at 12:38
Please edit the code and use like this [Column(Name = "UserID", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)] – Nitin Chotaliya May 12 '16 at 03:43

score 0 · Accepted Answer · answered May 11 '16 at 12:24

Assuming that your code works fine with one insert, I would try one of these solutions

Solution 1

call context.SubmitChanges(); outside of the loop

so

for (int i = 0; i < 2; i++) {
    User tempUser = new User() {
        ID = null,
        EMail = i + "@" + i + ".de",
        Password = "Test1234",
        JoinedDate = DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss")
    };

    users.InsertOnSubmit(tempUser);

}
context.SubmitChanges();

Solution 2

use InsertAllOnSubmit

var tempUsers = Enumerable.Range(0, 2)
                      .Select(i => new User{
                                   ID = null,
                                   EMail = i + "@" + i + ".de",
                                   Password = "Test1234",
                                   JoinedDate = DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss")
                      });

users.InsertAllOnSubmit(tempUsers);
context.SubmitChanges();

Solution 3

Dispose and recreate context in each loop (seems a rather bad idea)

for (int i = 0; i < 2; i++) {
   using (var context = new DataContext(Connection)) {
     var users = context.GetTable<User>();

     User tempUser = new User() {

        ID = null,
        EMail = i + "@" + i + ".de",
        Password = "Test1234",
        JoinedDate = DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss")
    };

     users.InsertOnSubmit(tempUser);
     context.SubmitChanges();
   }
}

Only Solution 3 worked for me. But is this a good solution? Especially when keeping speed in mind — DirtyNative, May 11 '16 at 12:34
@DanielDirtyNativeMartin No, it's not... Another way, maybe : http://stackoverflow.com/questions/31145955/cannot-insert-multiple-items-into-sqlite-database — Raphaël Althaus, May 11 '16 at 12:48

Insert with AutoIncrement

2 Answers2