基于SQLiteNet-Extensions的外键表CRUD操作实现问询

Hey there! Let's work through your SQLite-Net Extensions issues step by step. I see you've got a few key problems to fix—from model definitions to missing namespaces—and I'll walk you through each part with clear examples.

First off, your Agenda and Tasks classes have incorrect relationship annotations that are preventing the foreign key setup from working:

Corrected Agenda.cs #

The [OneToMany] attribute needs to be attached to a collection property that holds the related Tasks. You also should initialize the collection to avoid null reference errors:

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

[Table("Agenda")]
public class Agenda
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Topic { get; set; }
    public string Duration { get; set; }
    public DateTime Date { get; set; }

    // One Agenda has many Tasks - attach the attribute to the collection
    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public ObservableCollection<Tasks> Tasks { get; set; } = new ObservableCollection<Tasks>();
}

Corrected Tasks.cs #

Your ForeignKey was applied to the wrong property! The foreign key should be an int matching the Agenda's primary key type, not the task name. You also need to link it to the Agenda navigation property:

using System;
using SQLite;
using SQLiteNetExtensions.Attributes;

[Table("Task")]
public class Tasks
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }
    public string Name { get; set; } // This is your task name, separate from the foreign key
    public string Time { get; set; }

    // Foreign key linking to Agenda's ID
    [ForeignKey(typeof(Agenda))]
    public int AgendaId { get; set; }

    // Many Tasks belong to one Agenda
    [ManyToOne]
    public Agenda Agenda { get; set; }
}

The WithChildren methods for async connections live in the SQLiteNetExtensionsAsync.Extensions namespace, not the core SQLiteNetExtensions one. Add this using directive to your AgendaDatabase.cs:

using SQLiteNetExtensionsAsync.Extensions;

Also, SQLite by default disables foreign key constraints—you need to enable them explicitly when initializing your database. Update your AgendaDatabase constructor:

public AgendaDatabase(string dbPath)
{
    database = new SQLiteAsyncConnection(dbPath);
    // Enable foreign key constraints
    database.ExecuteAsync("PRAGMA foreign_keys = ON;").Wait();
    database.CreateTableAsync<Agenda>().Wait();
    database.CreateTableAsync<Tasks>().Wait();
}

Let's clarify how relationships work here:

• Cascade Operations: Your [OneToMany(CascadeOperations = CascadeOperation.All)] means:
  • When you save an Agenda, all its associated Tasks will also be saved/updated
  • When you delete an Agenda, all its associated Tasks will be deleted automatically
• Loading Related Data: The default Table<T> queries won't load associated Tasks—you need to use the SQLite-Net Extensions async methods like GetWithChildrenAsync or GetAllWithChildrenAsync to fetch related data.

Your existing GetAgendaAsync and GetAgendasAsync won't load Tasks by default. Update them to use the extension methods:

// Get all agendas WITH their associated tasks
public Task<List<Agenda>> GetAgendasAsync()
{
    return database.GetAllWithChildrenAsync<Agenda>();
}

// Get specific agenda WITH its associated tasks
public Task<Agenda> GetAgendaAsync(int id)
{
    return database.GetWithChildrenAsync<Agenda>(id);
}

Now let's add the missing Tasks operations to your AgendaDatabase class:

// Get all tasks for a specific agenda
public Task<List<Tasks>> GetTasksByAgendaIdAsync(int agendaId)
{
    return database.Table<Tasks>().Where(t => t.AgendaId == agendaId).ToListAsync();
}

// Get a single task by ID
public Task<Tasks> GetTaskAsync(int id)
{
    return database.Table<Tasks>().Where(t => t.ID == id).FirstOrDefaultAsync();
}

// Save a task (insert or update)
public Task<int> SaveTaskAsync(Tasks task)
{
    if (task.ID != 0)
    {
        // Update existing task
        return database.UpdateAsync(task);
    }
    else
    {
        // Insert new task (ensure AgendaId is set to link it to an Agenda)
        return database.InsertAsync(task);
    }
}

// Delete a task
public Task<int> DeleteTaskAsync(Tasks task)
{
    return database.DeleteAsync(task);
}

Example Usage #

When adding a new task to an agenda:

// Get an existing agenda
var myAgenda = await agendaDatabase.GetAgendaAsync(1);
// Create new task linked to the agenda
var newTask = new Tasks
{
    Name = "Finish presentation",
    Time = "14:00",
    AgendaId = myAgenda.ID // Link via foreign key
};
// Save the task
await agendaDatabase.SaveTaskAsync(newTask);

// If you want to save the task AND update the agenda's task collection:
myAgenda.Tasks.Add(newTask);
await agendaDatabase.UpdateWithChildrenAsync(myAgenda);

• Always initialize collection properties in your models to avoid nulls
• Enable foreign key constraints with PRAGMA foreign_keys = ON;
• Use SQLiteNetExtensionsAsync.Extensions for async relationship methods
• Use GetWithChildrenAsync/GetAllWithChildrenAsync to load related data
• Cascade operations handle automatic saving/deleting of related entities when configured

内容的提问来源于stack exchange，提问作者codejourney