如何使用SQLite实现基于非主键（PresentationId）的带关联子项查询以替代低效本地过滤？

The core issue here is that your current approach loads all records first before filtering locally, which wastes memory and processing time. Instead, we can directly query SQLite for only the PresentationSequence entries where PresentationId = 166, then load their associated children—all without pulling unnecessary data into your application.

Step 1: Add a Custom Filtered Load Method #

Extend your persistence service with a method that performs the database-level filter, then loads children only for the matching records. Here's how to implement it, matching your existing code style:

public virtual List<PresentationSequence> LoadByPresentationIdWithChildren(int presentationId)
{
    lock(Connection)
    {
        List<PresentationSequence> results = new List<PresentationSequence>();
        try
        {
            // Directly query SQLite for entries with the target PresentationId
            var query = "SELECT * FROM PresentationSequence WHERE PresentationId = @PresentationId";
            results = Connection.Query<PresentationSequence>(query, new { PresentationId = presentationId }).ToList();
            
            // Load children only for the filtered records
            foreach (var item in results)
            {
                try
                {
                    Connection.GetChildren(item);
                }
                catch (Exception e)
                {
                    DebugTraceListener.Instance.WriteLine(GetType().Name, 
                        $"Can not load Child for itemType: {item.GetType().Name} reason: {e}");
                }
            }
        }
        catch (Exception ex)
        {
            DebugTraceListener.Instance.WriteLine(GetType().Name, 
                $"Failed to load PresentationSequence entries with PresentationId: {presentationId}\n\n{ex}");
        }
        return results;
    }
}

Step 2: Use the New Method in Your Code #

Replace your inefficient temporary code with this direct call:

var resSequence = await Task.Run(() => 
    PersistenceService.PresentationSequence.LoadByPresentationIdWithChildren(166));

Why This Works Better #

• Database-level filtering: SQLite does the heavy lifting of finding only the records you need, so your app receives far less data over the connection.
• Reduced memory usage: You don't load the entire PresentationSequence table into memory, just the subset you care about.
• Faster processing: The loop to load children runs only on the filtered records, not every entry in the table.

Notes for Your ORM/Connection Setup #

If your Connection object uses an ORM like SQLite-net, you could also use LINQ for a more type-safe query (instead of raw SQL):

results = Connection.Table<PresentationSequence>()
                   .Where(ps => ps.PresentationId == presentationId)
                   .ToList();

This achieves the same filtering effect while keeping your code more maintainable.

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