Creating a foreign key in Entity Framework using SQLite

I'm using Entity Framework Core 7 in a .NET Maui app with SQLite to store relational data using two data models.

CheckList:

using SQLite;
using SQLiteTest.DataModels;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace SQLiteTest.DataModels
{
    public class CheckList
    {
        [PrimaryKey, AutoIncrement]
        public int CheckListID { get; set; }

        [Required]
        [SQLite.MaxLength(50)]
        public string name { get; set; }

        public ICollection<CheckListItem> CheckListItems { get; set; }
    }
}

CheckListItem:

using SQLite;
using SQLiteTest.DataModels;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace SQLiteTest.DataModels
{
    public class CheckListItem
    {
        [PrimaryKey, AutoIncrement]
        public int CheckListItemID { get; set; }

        public string ItemText { get; set; }
        public bool isChecked { get; set; }
        public int ItemOrder { get; set; }

        // foreign key
        [ForeignKey("CheckList")]
        public int CheckListID { get; set; }
        public CheckList CheckList { get; set; }
    }
}

MainPage.xml:

using SQLite;
using SQLiteTest.DataModels;

namespace SQLiteTest;

public partial class MainPage : ContentPage
{
    private SQLiteConnection _database;

    public MainPage()
    {
	    InitializeComponent();

        var dbPath = Path.Combine(@"D:\TESTDATA", "dbtest.db");
        _database = new SQLiteConnection(dbPath);

        _database.CreateTable<CheckList>();
        _database.CreateTable<CheckListItem>();
    }
}

I get an exception on:

_database.CreateTable<CheckList>();

> Exception: System.NotSupportedException: 'Don't know about system.Collections.Generic.ICollection`1 [SQLiteTest.DataModels.CheckListItem]'

If I remove:

public ICollection<CheckListItem> CheckListItems { get; set; }

Then it creates the table, but not the foreign key. I believe the error has to do with CheckList not knowing what a type CheckListItem is. If I create CheckListItem first:

_database.CreateTable<CheckListItem>();

I get the exception on that line:

> System.NotSupportedException: 'Don't know about SQLiteTest.DataModels.CheckList'

However, it does resolve in code and compiles without warning or error. Is this error thrown because table CheckListItems doesn't exist in database yet?

Updated after running migrations (looks like the foreign key relationships is built?)

public partial class InitialCreate : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "List",
            columns: table => new
            {
                listID = table.Column<long>(type: "INTEGER", nullable: false),
                name = table.Column<string>(type: "TEXT", nullable: false),
                CheckList = table.Column<long>(type: "INTEGER", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_List", x => x.ListID);
            });

        migrationBuilder.CreateTable(
            name: "CheckListItem",
            columns: table => new
            {
                CheckListItemID = table.Column<long>(type: "INTEGER", nullable: false),
                question = table.Column<string>(type: "TEXT", nullable: false),
                order = table.Column<long>(type: "INTEGER", nullable: false),
                isChecked = table.Column<long>(type: "INTEGER", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_CheckListItem", x => x.CheckListItemID);
            });

        migrationBuilder.CreateTable(
            name: "CheckList",
            columns: table => new
            {
                CheckListID = table.Column<long>(type: "INTEGER", nullable: false),
                name = table.Column<string>(type: "TEXT", nullable: false),
                CheckListItem = table.Column<long>(type: "INTEGER", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_CheckList", x => x.CheckListID);
                table.ForeignKey(
                    name: "FK_CheckList_CheckListItem_CheckListItem",
                    column: x => x.CheckListItem,
                    principalTable: "CheckListItem",
                    principalColumn: "CheckListItemID");
            });

        migrationBuilder.CreateIndex(
            name: "IX_CheckList_CheckListItem",
            table: "CheckList",
            column: "CheckListItem");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "List");

        migrationBuilder.DropTable(
            name: "CheckList");

        migrationBuilder.DropTable(
            name: "CheckListItem");
    }
}

SQLite do not support foreign key in that fashion, hence the NotSupportedException.
See documentation What you need to do is rely on the Key to the Checklist in the ChecklistsItems.

public class CheckList
{
    [PrimaryKey, AutoIncrement]
    public int CheckListID { get; set; }

    [Required]
    [SQLite.MaxLength(50)]
    public string name { get; set; }
}
public class CheckListItem
{
    [PrimaryKey, AutoIncrement]
    public int CheckListItemID { get; set; }

    public string ItemText { get; set; }
    public bool isChecked { get; set; }
    public int ItemOrder { get; set; }

    public int CheckListID { get; set; }
}

As a side note, using EF is making your life much harder. It is better to follow Microsoft Recomendations and use sqlite-net-pcl per Maui documentation