1

I wanted to use a List<MyItem> in my Model within my .NET MAUI App and store it in my SQLite 3 database.

When I use the following model and try to insert it with InsertAsync(…) I get an Error that the Table does not exist.

[Table("MyItemStorageTable")]
public class MyItemStorage
{
    [PrimaryKey, AutoIncrement]     // Primary Key already indexed in Table
    public int Id { get; set; }

    [Unique]                        // Primary Key combined with ID
    public string Name { get; set; }

    public string Brand { get; set;}
    
    public List<MyItem> MyItemList { get; set; }

}

Well, okay it wants to store these List-Items in a separate table.

What is your recommendation to do this? I don’t want to do a lot of manual stuff to keep these two tables in sync and have a lot of Code in the background that does this stuff.

Was expecting that the OR Mapper can handle it on it’s own?

What is the preferred way to handle this?

========== Edit: following models and no entry for List ==========

Sorry, but it did not work in my .NET-MAUI App. I installed the following NuGet SQLite.Net.Extensions.Async (3.0.1) and changed my models as follows:

[Table("MyItemStorageTable")]
public class MyItemStorage
{
    [PrimaryKey, AutoIncrement]     // Primary Key already indexed in Table
    public int Id { get; set; }

    [Unique]                        // Primary Key combined with ID
    public string Name { get; set; }

    public string Brand { get; set;}
    
    [OneToMany]    // One MyItemStorage can have many MyItem
    public List<MyItem> MyItemList { get; set; }

}
[Table("MyItemTable")]
public class MyItem
{
    [PrimaryKey, AutoIncrement]     // Primary Key already indexed in Table
    public int Id { get; set; }

    [Unique]                        // Primary Key combined with ID
    public string Name { get; set; }
}
//[Table("MyItemStorageMyItems")]
public class MyItemStorageMyItems
{
    //[PrimaryKey, AutoIncrement]
    //public int Id { get; set; }

    [ForeignKey(typeof(MyItemStorage))]
    public int MyItemStorageId { get; set; }

    [ForeignKey(typeof(MyItem))]
    public int MyItemId { get; set; }
}

Init() of my DatabaseService

static async Task Init()
{
    if (Database is not null)
    {
        return;
    }

    Database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);

    await Database.CreateTableAsync<MyItemStorageMyItems>();
    await Database.CreateTableAsync<MyItemStorage>();
    await Database.CreateTableAsync<MyItem>();
}

public static async Task AddMyItemStorage(MyItemStorage myItemStorage)
{
    await Init();

    await Database.InsertAsync(myItemStorage);
}

First of all, when I drop all tables and start from scratch, I get an exception that the Table MyItemStorage does not exist which is strange as the InsertAsync(...) should create it. When I restart my App it does not generate this error anymore.

Unfortunately, the Table MyItemStorageMyItems does not show any values, but MyItemStorage shows entries. So still, there is no List stored.

What am I doing wrong here? And before asking, I checked in the DatabaseService.AddMyItemStorage(...)-Method, the list items are all available.

OXO
  • 391
  • 1
  • 8
  • you need to use SQLite Extensions for FK relations – Jason Apr 26 '23 at 15:57
  • Do I have to manage my relations and create Tables on my own then, or is this done in background and I can use something like a ```List```? – OXO Apr 26 '23 at 17:12
  • please read the [docs](https://bitbucket.org/twincoders/sqlite-net-extensions/src/master/) – Jason Apr 26 '23 at 18:51
  • I added my code to my question. Unfortunately, without success. My List is not stored – OXO Apr 26 '23 at 21:57
  • Read the docs again. I believe you need to use the “WithChildren” methods – Jason Apr 26 '23 at 22:12
  • I am sorry, obviously it was late for me and I did not recognize my problem :( What I still struggle with is the Error "SQLite.SQLiteException: 'no such table: MyItemStorage'" when I save items the first time. Then it crashes - when I relaunch my App and save again I can do it without having issues. – OXO Apr 27 '23 at 06:47

0 Answers0