How to work with nested classes and lists in my table?

Question

I'm using SQLite as a database and I have the following table, which works, when I create it:

public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Notes { get; set; }
    public bool Done { get; set; }
    public Gender Gender { get; set; }
    public DateTime? DateOfBirth { get; set; }
}

On creation of the table

database.CreateTableAsync<TodoItem>().Wait();

now I get some errors, if I want to include a nested object:

public class Test
{
    [PrimaryKey, AutoIncrement]
    public int AnotherID { get; set; }
    public string Name { get; set; }
}
public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Notes { get; set; }
    public bool Done { get; set; }
    public Gender Gender { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public Test Test { get; set; }
}

I get the following error

UNHANDLED EXCEPTION:
 System.AggregateException: One or more errors occurred. ---> System.NotSupportedException: Don't know about Todo.Test
   at SQLite.Orm.SqlType (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x001ad] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2080 
   at SQLite.Orm.SqlDecl (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2027 
   at SQLite.SQLiteConnection.<CreateTable>m__0 (SQLite.TableMapping+Column p) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:417 
   at System.Linq.Enumerable+SelectArrayIterator`2[TSource,TResult].ToArray () [0x00012] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/Select.cs:251 
   at System.Linq.Enumerable.ToArray[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00015] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/ToCollection.cs:19 
   at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x00133] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:418 
   at SQLite.SQLiteAsyncConnection+<CreateTablesAsync>c__AnonStorey0.<>m__0 () [0x0002f] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLiteAsync.cs:108 
   at System.Threading.Tasks.Task`1[TResult].InnerInvoke () [0x0000f] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Future.cs:680 
   at System.Threading.Tasks.Task.Execute () [0x00010] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:2502 
    --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional (System.Boolean includeTaskCanceledExceptions) [0x00011] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:2157 
   at System.Threading.Tasks.Task.Wait (System.Int32 millisecondsTimeout, System.Threading.CancellationToken cancellationToken) [0x00043] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:3189 
   at System.Threading.Tasks.Task.Wait () [0x00000] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:3054 
   at Todo.TodoItemDatabase..ctor (System.String dbPath) [0x00015] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\Todo\Todo\Data\TodoItemDatabase.cs:14 
   at Todo.App.get_Database () [0x0000e] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\Todo\Todo\App.cs:32 
   at Todo.TodoListPage+<OnAppearing>d__1.MoveNext () [0x0002c] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\Todo\Todo\Views\TodoListPage.xaml.cs:20 
 --- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:151 
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore+<>c.<ThrowAsync>b__6_0 (System.Object state) [0x00000] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/AsyncMethodBuilder.cs:1018 
   at Android.App.SyncContext+<>c__DisplayClass2_0.<Post>b__0 () [0x00000] in /Users/builder/data/lanes/4695/448f54fd/source/xamarin-android/src/Mono.Android/Android.App/SyncContext.cs:35 
   at Java.Lang.Thread+RunnableImplementor.Run () [0x00008] in /Users/builder/data/lanes/4695/448f54fd/source/xamarin-android/src/Mono.Android/Java.Lang/Thread.cs:36 
   at Java.Lang.IRunnableInvoker.n_Run (System.IntPtr jnienv, System.IntPtr native__this) [0x00008] in /Users/builder/data/lanes/4695/448f54fd/source/xamarin-android/src/Mono.Android/obj/Release/android-23/mcw/Java.Lang.IRunnable.cs:81 
   at (wrapper dynamic-method) System.Object:4ad81135-c1dd-4bba-bca3-4e991f58da69 (intptr,intptr)
 ---> (Inner Exception #0) System.NotSupportedException: Don't know about Todo.Test
   at SQLite.Orm.SqlType (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x001ad] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2080 
   at SQLite.Orm.SqlDecl (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2027 
   at SQLite.SQLiteConnection.<CreateTable>m__0 (SQLite.TableMapping+Column p) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:417 
   at System.Linq.Enumerable+SelectArrayIterator`2[TSource,TResult].ToArray () [0x00012] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/Select.cs:251 
   at System.Linq.Enumerable.ToArray[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00015] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/ToCollection.cs:19 
   at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x00133] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:418 
   at SQLite.SQLiteAsyncConnection+<CreateTablesAsync>c__AnonStorey0.<>m__0 () [0x0002f] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLiteAsync.cs:108 
   at System.Threading.Tasks.Task`1[TResult].InnerInvoke () [0x0000f] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Future.cs:680 
   at System.Threading.Tasks.Task.Execute () [0x00010] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:2502 <---

If I use a List

public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Notes { get; set; }
    public bool Done { get; set; }
    public Gender Gender { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public List<string> Strings { get; set; }
}

I get

 UNHANDLED EXCEPTION:
 System.AggregateException: One or more errors occurred. ---> System.NotSupportedException: Don't know about System.Collections.Generic.List`1[System.String]
   at SQLite.Orm.SqlType (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x001ad] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2080 
   at SQLite.Orm.SqlDecl (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2027 
   at SQLite.SQLiteConnection.<CreateTable>m__0 (SQLite.TableMapping+Column p) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:417 
   at System.Linq.Enumerable+SelectArrayIterator`2[TSource,TResult].ToArray () [0x00012] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/Select.cs:251 
   at System.Linq.Enumerable.ToArray[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00015] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/ToCollection.cs:19 
   at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x00133] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:418 
   at SQLite.SQLiteAsyncConnection+<CreateTablesAsync>c__AnonStorey0.<>m__0 () [0x0002f] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLiteAsync.cs:108 
   at System.Threading.Tasks.Task`1[TResult].InnerInvoke () [0x0000f] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Future.cs:680 
   at System.Threading.Tasks.Task.Execute () [0x00010] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:2502 
    --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional (System.Boolean includeTaskCanceledExceptions) [0x00011] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:2157 
   at System.Threading.Tasks.Task.Wait (System.Int32 millisecondsTimeout, System.Threading.CancellationToken cancellationToken) [0x00043] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:3189 
   at System.Threading.Tasks.Task.Wait () [0x00000] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:3054 
   at Todo.TodoItemDatabase..ctor (System.String dbPath) [0x00015] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\Todo\Todo\Data\TodoItemDatabase.cs:14 
   at Todo.App.get_Database () [0x0000e] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\Todo\Todo\App.cs:32 
   at Todo.TodoListPage+<OnAppearing>d__1.MoveNext () [0x0002c] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\Todo\Todo\Views\TodoListPage.xaml.cs:20 
 --- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:151 
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore+<>c.<ThrowAsync>b__6_0 (System.Object state) [0x00000] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/AsyncMethodBuilder.cs:1018 
   at Android.App.SyncContext+<>c__DisplayClass2_0.<Post>b__0 () [0x00000] in /Users/builder/data/lanes/4695/448f54fd/source/xamarin-android/src/Mono.Android/Android.App/SyncContext.cs:35 
   at Java.Lang.Thread+RunnableImplementor.Run () [0x00008] in /Users/builder/data/lanes/4695/448f54fd/source/xamarin-android/src/Mono.Android/Java.Lang/Thread.cs:36 
   at Java.Lang.IRunnableInvoker.n_Run (System.IntPtr jnienv, System.IntPtr native__this) [0x00008] in /Users/builder/data/lanes/4695/448f54fd/source/xamarin-android/src/Mono.Android/obj/Release/android-23/mcw/Java.Lang.IRunnable.cs:81 
   at (wrapper dynamic-method) System.Object:3ff99a1f-7842-4c41-b229-386570e8d19f (intptr,intptr)
 ---> (Inner Exception #0) System.NotSupportedException: Don't know about System.Collections.Generic.List`1[System.String]
   at SQLite.Orm.SqlType (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x001ad] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2080 
   at SQLite.Orm.SqlDecl (SQLite.TableMapping+Column p, System.Boolean storeDateTimeAsTicks) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2027 
   at SQLite.SQLiteConnection.<CreateTable>m__0 (SQLite.TableMapping+Column p) [0x00000] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:417 
   at System.Linq.Enumerable+SelectArrayIterator`2[TSource,TResult].ToArray () [0x00012] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/Select.cs:251 
   at System.Linq.Enumerable.ToArray[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00015] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/external/corefx/src/System.Linq/src/System/Linq/ToCollection.cs:19 
   at SQLite.SQLiteConnection.CreateTable (System.Type ty, SQLite.CreateFlags createFlags) [0x00133] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:418 
   at SQLite.SQLiteAsyncConnection+<CreateTablesAsync>c__AnonStorey0.<>m__0 () [0x0002f] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLiteAsync.cs:108 
   at System.Threading.Tasks.Task`1[TResult].InnerInvoke () [0x0000f] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Future.cs:680 
   at System.Threading.Tasks.Task.Execute () [0x00010] in /Users/builder/jenkins/workspace/xamarin-android/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/threading/Tasks/Task.cs:2502 <---

I'm using SQLite-net Official Portable Library 1.3.3 . Do I have to use attributes like PrimaryKey, ForeignKey and so on? How? Or is the library only able to handle primitive data types?

The sample can be downloaded from here.

This issue states, that it should be possible to work with List, but not in the latest version. I tried to downgrade to 1.2.0 and 1.3.1 without success.

Edit

I installed SQLiteNetExtensions and changed the sample code to

using SQLite;
using SQLiteNetExtensions.Attributes;
using System.Collections.Generic;

namespace Todo
{
    public class Test
    {
        [PrimaryKey, AutoIncrement]
        public int AnotherID { get; set; }
        public string Name { get; set; }
    }

    public class TodoItem
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string Name { get; set; }
        public string Notes { get; set; }
        public bool Done { get; set; }
        [TextBlob("StringsBlobbed")]
        public List<string> Strings { get; set; }
        public string StringsBlobbed { get; set; }
    }
}

and I still get the same error.

Edit 2

I think there is a confusion between libraries. SQLite-Net Extensions depends on SQLite.Net-PCL v. 3.1.1 (Nuget). But Xamarin linked to the library sqlite-net-pcl v. 1.3.3 (Nuget).

SQLite.Net-PCL (oysteinkrog) is a fork of sqlite-net-pcl (praeclarum). It seems that the latter one is not compatible with SQLite-Net Extensions, which is what I need here. But does SQLite.Net-PCL support UWP? It hasn't been updated since 4 Jun 2016. And the most funny thing is that SQLite-Net Extensions links to praeclarum, which is wrong. I checked this via uninstalling all SQL Nuget packages and only installing SQLite-Net Extensions, which should trigger the required dependencies automatically. What can I do?

Don't discuss votes in your question. Anyway I'd guess you should read [ask] and include the actual error. "One or more errors occurred" is an AggregateException, which contains the actual exceptions that occurred in its `InnerExceptions` property. Those excepctions contain messages that you can further research, and don't forget to show your research in your question. — CodeCaster, Jun 21 '17 at 10:31
See for example [Can I use a List of String in a class intended for SQLite?](https://stackoverflow.com/questions/14663984/can-i-use-a-list-of-string-in-a-class-intended-for-sqlite). — CodeCaster, Jun 21 '17 at 10:34
I tried to use `SQLiteNetExtensions`, but I get the same error for the list (see edited question). Shouldn't it be possible to work with `ForeignKey` to nest an object into an object? — testing, Jun 21 '17 at 11:36

testing · Accepted Answer · 2021-02-15T15:26:41.807

To be able to work with a nested List or object I need SQLiteNetExtensions. Because I'm using another SQLite package than the default dependency of SQLiteNetExtensions you have two options:

Manually add the project to your solution
1.1. Download the source for SQLiteNetExtensions
1.2. Add as project "SQLiteNetExtensions-PCL" to your solution
1.3. Add this project as reference to all of your projects (portable, iOS, Droid, UWP)
Install SQLiteNetExtensions 2.0.0-alpha2

Be aware if you have already installed some sql NuGet packages. Uninstall all of them first and then only install SQLiteNetExtensions. This will add all required dependencies for you.

For List<string> you can do:

public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Notes { get; set; }
    public bool Done { get; set; }

    [TextBlob("StringsBlobbed")]
    public List<string> Strings { get; set; }
    public string StringsBlobbed { get; set; }
}

If you have other types of list you need a [OneToMany] relationship.

To work with objects you should be able to do this:

public class Test
{
    [PrimaryKey, AutoIncrement]
    public string AnotherID { get; set; }
    public string Name { get; set; }
}

public class TodoItem
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Notes { get; set; }
    public bool Done { get; set; }

    [ForeignKey(typeof(Test))]
    public string TestId { get; set; }

    [OneToOne]
    public Test Test { get; set; }
}

A good guide how to work with [OneToOne] relationships (e.g. for objects) is this here. For [OneToMany] look here.

There are some more steps like creating the table, and retrieving and storing the data in a special way (GetWithChildren, UpdateWithChildren, ...). Also be aware of the CascadeOperations, which you will need if you have a multiple level hirarchy.

If you still get the error, read the debug log carefully. One has to make sure that [ForeignKey] and [OneToOne] is used on all classes, which use the object.

How to work with nested classes and lists in my table?

1 Answers1