如何用.NET 7(LinqToDB)合并PostgreSQL查询，单查询获取Scout权限内报告

LinqToDB实现方案 #

方案1：使用Any实现OR条件（推荐，避免重复） #

这种方式通过Exists语义直接在WHERE子句中合并两个条件，不会返回重复的报告记录，生成的SQL更高效：

int scoutId = // 当前Scout的ID
int skip = // 分页偏移量
int take = // 每页数量

var query = from p in db.Reports
            where p.CreatedBy == scoutId 
               || db.ReportAccess.Any(ra => ra.ReportId == p.Id && ra.ScoutId == scoutId)
            orderby p.Id // 必须指定排序字段，保证分页结果稳定
            select p;

var paginatedReports = query.Skip(skip).Take(take).ToList();

方案2：使用Union合并两个查询 #

如果更倾向于复用原有两个查询逻辑，可以用Union自动去重，合并结果：

var createdReports = from p in db.Reports 
                     where p.CreatedBy == scoutId 
                     select p;

var accessibleReports = from p in db.Reports
                        join ra in db.ReportAccess on p.Id equals ra.ReportId
                        where ra.ScoutId == scoutId
                        select p;

var combinedQuery = createdReports.Union(accessibleReports)
                                  .OrderBy(p => p.Id) // 分页必须排序
                                  .Skip(skip)
                                  .Take(take);

var paginatedReports = combinedQuery.ToList();

对应的PostgreSQL SQL示例 #

对应方案1的SQL（Exists方式） #

SELECT r.*
FROM reports r
WHERE r.created_by = @scoutId
   OR EXISTS (
       SELECT 1
       FROM report_access ra
       WHERE ra.report_id = r.id 
         AND ra.scout_id = @scoutId
   )
ORDER BY r.id -- 必须排序，确保分页一致性
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY;

对应方案2的SQL（Union方式） #

SELECT * FROM (
    SELECT r.* FROM reports r WHERE r.created_by = @scoutId
    UNION
    SELECT r.* FROM reports r
    JOIN report_access ra ON r.id = ra.report_id
    WHERE ra.scout_id = @scoutId
) AS combined_reports
ORDER BY id
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY;

关键注意事项 #

• 必须指定排序字段：PostgreSQL的OFFSET/FETCH依赖有序结果，若未排序，每次分页返回的结果可能不一致（数据库默认的行顺序不保证稳定）。建议选择Id或其他业务相关的排序字段（如创建时间）。
• 避免重复记录：直接用Join合并会导致同一报告被多次返回（如果存在多条权限记录），而Any/Exists或Union可以避免这个问题。
• 性能优化：确保reports.created_by、report_access.report_id和report_access.scout_id字段上创建了索引，提升查询效率。

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