EF Core+SQLite多JOIN查询极慢，求排查问题原因

场景与表结构 #

使用EF Core搭配SQLite，现有表：DomesticContainers、DomesticRecords、InternationalContainers、InternationalRecords、Clients，其中：

• XXXXContainers 与 XXXXRecords 为一对多关系
• XXXXContainers 与 Clients 为一对多关系

表定义SQL #

CREATE TABLE "DomesticContainers" (
    "Id"    INTEGER NOT NULL,
    "Name"  TEXT,
    CONSTRAINT "PK_DomesticContainers" PRIMARY KEY("Id" AUTOINCREMENT)
);

CREATE TABLE "DomesticRecords" (
    "Id"    INTEGER NOT NULL,
    "Zone"  TEXT,
    "ContainerId"   INTEGER NOT NULL,
    CONSTRAINT "PK_DomesticRecords" PRIMARY KEY("Id" AUTOINCREMENT),
    CONSTRAINT "FK_DomesticRecords_DomesticContainers_ContainerId" FOREIGN KEY("ContainerId") REFERENCES "DomesticContainers"("Id") ON DELETE CASCADE
);

CREATE TABLE "InternationalContainers" (
    "Id"    INTEGER NOT NULL,
    "Name"  TEXT,
    CONSTRAINT "PK_InternationalContainers" PRIMARY KEY("Id" AUTOINCREMENT)
);

CREATE TABLE "InternationalRecords" (
    "Id"    INTEGER NOT NULL,
    "CountryCode"   TEXT,
    "ContainerId"   INTEGER NOT NULL,
    CONSTRAINT "PK_InternationalRecords" PRIMARY KEY("Id" AUTOINCREMENT),
    CONSTRAINT "FK_InternationalRecords_InternationalContainers_ContainerId" FOREIGN KEY("ContainerId") REFERENCES "InternationalContainers"("Id") ON DELETE CASCADE
);

CREATE TABLE "Clients" (
    "Id"    INTEGER NOT NULL,
    "Name"  TEXT,
    "DomesticContainerId"   INTEGER,
    "InternationalContainerId"  INTEGER,
    CONSTRAINT "PK_Clients" PRIMARY KEY("Id" AUTOINCREMENT),
    CONSTRAINT "FK_Clients_InternationalContainers_InternationalContainerId" FOREIGN KEY("InternationalContainerId") REFERENCES "InternationalContainers"("Id"),
    CONSTRAINT "FK_Clients_DomesticContainers_DomesticContainerId" FOREIGN KEY("DomesticContainerId") REFERENCES "DomesticContainers"("Id")
);

问题代码与现象 #

尝试使用多组Include+ThenInclude查询客户关联的容器及记录：

var query = context.Clients
   .Include(c => c.InternationalContainer).ThenInclude(ic => ic.LinkedRecords)
   .Include(c => c.DomesticContainer).ThenInclude(dc => dc.LinkedRecords);

该查询极慢，手动执行生成的SQL同样卡顿：

SELECT "c"."Id", "c"."Name", "d"."Id", "d"."Name", "i"."Id", "d0"."Id", "d0"."ContainerId", "d0"."Zone", "i"."Name", "i0"."Id", "i0"."ContainerId", "i0"."CountryCode"
FROM "Clients" AS "c"
LEFT JOIN "DomesticContainers" AS "d" ON "c"."DomesticContainerId" = "d"."Id"
LEFT JOIN "InternationalContainers" AS "i" ON "c"."InternationalContainerId" = "i"."Id"
LEFT JOIN "DomesticRecords" AS "d0" ON "d"."Id" = "d0"."ContainerId"
LEFT JOIN "InternationalRecords" AS "i0" ON "i"."Id" = "i0"."ContainerId"
ORDER BY "c"."Id", "d"."Id", "i"."Id", "d0"."Id"

但仅使用一组Include-ThenInclude（2个JOIN）时，查询瞬间完成。单独查询国内/国际记录均正常，同时查询则卡顿。

数据量：DomesticRecords不足4000条，InternationalRecords不足65000条，测试时Clients、DomesticContainers、InternationalContainers各仅1条记录。

原因与解决方案 #

核心原因 #

EF Core生成的SQL将两个独立的一对多集合（DomesticRecords和InternationalRecords）通过JOIN合并到同一张结果表中，导致笛卡尔积爆炸：1条国内容器 × 4000条国内记录 × 1条国际容器 × 65000条国际记录 = 2.6亿条结果行，SQLite需要处理海量数据，自然卡顿。

解决方案 #

方案1：使用拆分查询（推荐）

EF Core 5.0及以上支持AsSplitQuery()，让EF自动将查询拆分为多个独立SQL语句，避免笛卡尔积：

var query = context.Clients
   .Include(c => c.InternationalContainer).ThenInclude(ic => ic.LinkedRecords)
   .Include(c => c.DomesticContainer).ThenInclude(dc => dc.LinkedRecords)
   .AsSplitQuery();

该方法会生成3条独立SQL：

• 查询Clients及其关联的DomesticContainers、InternationalContainers
• 查询关联的DomesticRecords
• 查询关联的InternationalRecords
  完全避免笛卡尔积，性能大幅提升。

方案2：手动拆分查询

如果使用低版本EF Core，可手动拆分查询，利用EF变更跟踪自动关联数据：

// 先查询客户及容器
var clients = context.Clients
   .Include(c => c.InternationalContainer)
   .Include(c => c.DomesticContainer)
   .ToList();

// 提取关联容器ID，批量加载记录
var containerIds = clients
   .SelectMany(c => new[] { c.DomesticContainerId, c.InternationalContainerId })
   .Where(id => id != null)
   .Cast<int>()
   .ToList();

context.DomesticRecords.Where(r => containerIds.Contains(r.ContainerId)).Load();
context.InternationalRecords.Where(r => containerIds.Contains(r.ContainerId)).Load();

方案3：添加索引优化（辅助）

虽然核心问题是笛卡尔积，但为关联字段添加索引可进一步提升单条查询性能：

-- 给DomesticRecords的ContainerId添加索引
CREATE INDEX IX_DomesticRecords_ContainerId ON DomesticRecords(ContainerId);
-- 给InternationalRecords的ContainerId添加索引
CREATE INDEX IX_InternationalRecords_ContainerId ON InternationalRecords(ContainerId);

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