SQLite Key Property Issue Using Mvvm Windows Universal

Question

This is my Model called "ChallengeItem" for the SQLite table:

   [AutoIncrement]
    private int _key { get; set; }

    public int Key
    {
        get { return _key; }
        set { _key = value; OnPropertyChanged("Key");}
    }

    private bool _done;

    public bool Done
    {
        get { return _done; }
        set { _done = value; OnPropertyChanged("Done"); }
    }

I created a class called "DatabaseManager" which includes this line of Code:

  conn.CreateTable<Models.ChallengeItem>();

• After checking the Key property it turns out that it doesn't increment, the key value is staying 0 for every Challengeitem in the table. How can I fix this?
• How can I get the last Item in the table where the Done Property of the challengeitem equals true?

Answer 1

Assuming, you are using SQLite-Net PCL (your api's look like).

First question: There is a special case with InsertOrReplace: It considers 0 a valid Key and doe's not auto increment it. You might check for that case and use a regular Insert instead. Find various examples in this question.

Also, the SQLite FAQ suggests, that Auto Increment only works on primary keys.

Second Question: SQL Tables do not guarantee any order, so you should use an additional property like date created, to be sure. As you have an AutoIncrement ID, you might use that in a simple linq query:

conn.Table<ChallengeItem>().Where(i => i.Done).OrderByDescending(i => i.Key);