C#中从SQLite关联双表高效检索含子列表的发票对象集合的最优方法

该方案完全平衡数据库负载和代码组装效率，不需要循环查库，也不需要全量加载结果到内存做复杂分组。

核心实现逻辑 #

单次数据库往返拿到所有关联数据，借助结果集的预排序规则，在读取流的过程中直接完成嵌套对象组装，同时避免额外的数据库扫描开销。

一、优化查询语句减少数据库负载 #

原参考查询存在三个会拉高负载、导致结果不准的问题，调整后语句如下：

SELECT
    T1.ID, T1.Date, T1.ReceiptNumber, T1.TotalValueOfSale,
    T2.Description, T2.SalePrice, T2.Quantity
FROM
    Invoices T1
LEFT JOIN
    Items T2
ON
    T1.ID = T2.RelatedInvoiceID
WHERE
    -- 范围匹配可直接命中Date字段索引，避免函数包裹列导致的全表扫描
    T1.Date >= @StartDate 
    AND T1.Date < @EndDate
ORDER BY
    T1.ID, T2.Description

调整点说明：

• 将原INNER JOIN改为LEFT JOIN，避免漏掉没有任何关联条目的发票记录
• 用日期范围匹配替代Date = 'YYYY-MM-DD'的等值写法，既适配DATETIME字段存储时分秒的场景，也不会因为对列做函数处理（比如DATE(T1.Date) = 'xxx'）导致索引失效
• 增加固定排序规则，保证同一张发票的所有条目在结果集中连续排列，为后续流式分组做准备

二、C#端轻量分组组装代码 #

不需要把全量查询结果加载到内存再用LINQ分组，只需在数据库读取过程中逐行判断，边读边组装对象：

var invoiceResultList = new List<Invoice>();
Invoice currentTrackingInvoice = null;

// 初始化SQLite连接、命令，传入查询参数
using var connection = new SqliteConnection("你的SQLite数据库连接字符串");
connection.Open();
using var queryCommand = connection.CreateCommand();
queryCommand.CommandText = "上述优化后的联表查询语句";
// 按目标日期生成范围参数
var targetQueryDate = new DateTime(2024, 5, 20); // 替换为实际业务传入的日期
queryCommand.Parameters.AddWithValue("@StartDate", targetQueryDate.ToString("yyyy-MM-dd"));
queryCommand.Parameters.AddWithValue("@EndDate", targetQueryDate.AddDays(1).ToString("yyyy-MM-dd"));

// 逐行读取结果
using var dataReader = queryCommand.ExecuteReader();
while (dataReader.Read())
{
    var currentInvoiceId = dataReader.GetString("ID");
    // 检测到新的发票ID时，把之前跟踪的发票存入结果，开启新的跟踪对象
    if (currentTrackingInvoice == null || currentTrackingInvoice.ID != currentInvoiceId)
    {
        if (currentTrackingInvoice != null)
        {
            invoiceResultList.Add(currentTrackingInvoice);
        }
        currentTrackingInvoice = new Invoice
        {
            ID = currentInvoiceId,
            Date = dataReader.GetString("Date"),
            ReceiptNumber = dataReader.GetInt32("ReceiptNumber"),
            TotalValueOfSale = dataReader.GetString("TotalValueOfSale"),
            Items = new List<Item>()
        };
    }
    // 处理当前行的条目（LEFT JOIN会出现无条目的空值场景）
    if (!dataReader.IsDBNull("Description"))
    {
        currentTrackingInvoice.Items.Add(new Item
        {
            Description = dataReader.GetString("Description"),
            SalePrice = dataReader.GetString("SalePrice"),
            Quantity = dataReader.GetString("Quantity")
        });
    }
}
// 循环结束后把最后一张跟踪的发票存入结果
if (currentTrackingInvoice != null)
{
    invoiceResultList.Add(currentTrackingInvoice);
}

三、进一步降低数据库负载的配置建议 #

• 给Items表的RelatedInvoiceID字段建立普通索引，联表匹配时可快速定位关联条目，避免逐行扫描
• 不要在WHERE条件、JOIN条件中对数据库字段做无必要的函数计算，这类写法会直接导致索引失效，将查询拉成全表扫描
• 整个组装逻辑不需要引入第三方ORM，代码总长度不到40行，比循环逐张查发票的实现简单得多，同时把数据库往返次数从几百次压缩到1次，负载降低几个数量级

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