Dealing with SQLite Autoincrement column mapping in LINQtoSQL

Question

I have folowing entity

    [Table(Name = "Users")]
    public sealed class UserDB
    {
        private Int64 _id = -1;
        private string _username = string.Empty;

        public UserDB() { }

        public UserDB(RepositoryInfo repoInfo)
        {
            UserName = repoInfo.Account;
        }

        [Column(Name = "ID", Storage = "_id",  IsDbGenerated = true, IsPrimaryKey = true, UpdateCheck = UpdateCheck.Never)]
        public Int64 ID { get { return _id; } set { _id = value; } }

        [Column(Name = "UserName", DbType="nvarchar(50)", Storage = "_username")]
        public string UserName { get { return _username; } set { _username = value; } }
    }

ID is mapped to Autoincrement INTEGER type column (actually the only type possible with autoincrement in SQLite)

When I try to add a new user to DB like this, I get an error:

public static Int64 AddUser(DataContext context, RepositoryInfo repoInfo)
{            
    UserDB udb = new UserDB(repoInfo);

    //an ID of udb is -1, but I tried different values too, doesn't change result
    var userstable = context.GetTable<UserDB>();
    userstable.InsertOnSubmit( udb );


    context.SubmitChanges(); // here I get a error, see on screen shot

    return udb.ID;

}

Screen shot of the error:

EDIT After a googling for and checking, seems that SQLite simply doesn't provide any SCOPE_IDENTITY() function. But Linq To SQL injects it!

How can I change this?

score 3 · Accepted Answer · edited Jul 30 '15 at 04:35

3

Actually there is no solution found by me to resolve this issue, if not that one architectural one => Change linq query to change outputted SQL.

edited Jul 30 '15 at 04:35

Tharif

13,794
9
55
77

answered Aug 25 '11 at 20:47

Tigran

61,654
8
86
123

score 0 · Answer 2 · edited May 23 '17 at 12:30

This seems a bit hacky but according to this answer you simply need to pass "null" to your id column for sqlite to perform the autoincrement. This worked for me:

[Table(Name = "yourtable")]
class yourclass
{
    [Column(Name = "id", IsPrimaryKey=true )]
    public int? Id { get; set; }

...

Then when you create a new object for InsertOnSubmit you can just omit the id column and it will default to null.

To fetch the new id that was created using autoincrement see here

Dealing with SQLite Autoincrement column mapping in LINQtoSQL

2 Answers2

Linked