Xamarin and SQLite: an item with the same key has already been added. key: 3

Question

I'd like to make some preliminary remarks: I have a lot of project that it's working with the same code but previous version of SQLite (usually 1.2) and I don't have .NET Core.

I've created a new Xamarin project and I added the latest version of sqlite-net-pcl 1.3.3. I noticed in my project now there is .NET Core. I defined an entity

public interface ITableEntityMyExpenses {
    int Id { get; set; }
    bool IsDeleted { get; set; }
    DateTime UpdatedDate { get; set; }
}

Then a BaseTable

public class BaseTableMyExpenses : ITableEntityMyExpenses {
    [PrimaryKey, AutoIncrement]
    [Indexed]
    public int Id { get; set; } = 0;
}

And then a table

public class Expense : BaseTableMyExpenses {
    public DateTime ExpenseDate { get; set; }
    public int Cost { get; set; }
}

At the first time the app creates correctly the database. The database is empty.

If I try to add a new record in the database I receive this error:

An item with the same key has already been added. Key: 3

(TKey key, TValue value, Boolean add) at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer) at SQLite.EnumCacheInfo..ctor(Type type) at SQLite.EnumCache.GetInfo(Type type) at SQLite.SQLiteCommand.BindParameter(sqlite3_stmt stmt, Int32 index, Object value, Boolean storeDateTimeAsTicks) at SQLite.PreparedSqlLiteInsertCommand.ExecuteNonQuery(Object[] source)
at SQLite.SQLiteConnection.Insert(Object obj, String extra, Type objType) at SQLite.SQLiteConnection.Insert(Object obj) at MyExpenses.Repository.MyExpensesDatabase.SaveItem[T](T item) at MyExpenses.Repository.MyExpensesRepository.SaveExpense(Expense item)
at MyExpenses.ViewModels.ExpenseItemViewModel.SaveExpenseOnDB() at MyExpenses.ViewModels.ExpenseItemViewModel.d__42.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at MyExpenses.ViewModels.ExpenseItemViewModel.<b__41_0>d.MoveNext()

What is wrong?

I also tried to update an old project to the new version on SQLite. In this case apparently everything is working fine but if I try to execute this code

public List<T> GetItems<T>() where T : ITableEntity, new()
{
    lock (locker)
    {
        return (from i in database.Table<T>()
                select i).ToList();
    }
}

public T GetItem<T>(int id) where T : ITableEntity, new()
{
    lock (locker)
    {
        return database.Table<T>().FirstOrDefault(x => x.Id == id);
    }
}

I receive another kind of error

Cannot compile: Parameter

at SQLite.TableQuery1.CompileExpr(Expression expr, List1 queryArgs) at SQLite.TableQuery1.CompileExpr(Expression expr, List1 queryArgs) at SQLite.TableQuery1.CompileExpr(Expression expr, List1 queryArgs) at SQLite.TableQuery1.CompileExpr(Expression expr, List1 queryArgs) at SQLite.TableQuery1.GenerateCommand(String selectionList) at SQLite.TableQuery1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at SQLite.TableQuery1.FirstOrDefault() at SQLite.TableQuery1.FirstOrDefault(Expression1 predExpr) at MyExpenses.Repository.MyExpensesDatabase.GetItem[T](Int32 id) at MyExpenses.Repository.MyExpensesRepository.GetExpense(Int32 id) at MyExpenses.ViewModels.ExpenseItemViewModel.LoadData() at MyExpenses.ViewModels.ExpenseItemViewModel..ctor(Int32 expenseId, Boolean SaveOnDatabase) at MyExpenses.Views.ExpenseItem.LoadViewModel(Int32 expenseId) at MyExpenses.Views.ExpenseItem..ctor(Int32 expenseId) at MyExpenses.Views.ExpenseList.OnEdit(Object sender, EventArgs e) at Xamarin.Forms.MenuItem.OnClicked() at Xamarin.Forms.MenuItem.Xamarin.Forms.IMenuItemController.Activate() at Xamarin.Forms.Platform.UWP.MenuItemCommand.Execute(Object parameter) at System.Runtime.InteropServices.WindowsRuntime.ICommandToWinRTAdapter.Execute(Object parameter)

Update

I added a new record in the database with a common function:

public int SaveItem<T>(T item) where T : ITableEntityMyExpenses
{
    lock (locker)
    {
        if (item.Id != 0)
        {
            database.Update(item);
            return item.Id;
        }
        else
        {
            return database.Insert(item);
        }
    }
}

If I change

public int Id { get; set; } = 0;

with

public int Id { get; set; }

the result is exactly the same.

Can you explain/show how you're adding a new record to the database? You've marked that Id field with AutoIncrement, so telling the database to use a specific value for the id column might cause problems. — Glubus

Enrico Enrico · Accepted Answer · 2017-07-25T08:02:25

After the update of Xamarin Forms and SQLite-net-pcl, something definitely changed. Most of the functions are working fine but not that

public T GetItem<T>(int id) where T : ITableEntity, new()
{
    lock (locker)
    {
        return database.Table<T>().FirstOrDefault(x => x.Id == id);
    }
}

You have to change your code like that:

public T GetItem<T>(int id) where T : ITableEntity, new()
{
    lock (locker)
    {
        return database.Table<T>().Where(x => x.Id == id).FirstOrDefault();
    }
}

Xamarin and SQLite: an item with the same key has already been added. key: 3

1 Answers