如何用LINQ实现INNER JOIN及特定WHERE条件的查询

现有两个数据库表：

ReconFinalCost (Id, LeaseDocNumber, SnapshotId)  
ReconSnapshot (Id, FiscalYear, FiscalQuarter)

其中ReconFinalCost.SnapshotId是ReconSnapshot.Id的外键。

在C#代码中，我有一个List<ReconFinalCost> leaselist，需要编写LINQ语句实现以下逻辑：将ReconFinalCost与ReconSnapshot按ReconFinalCost.SnapshotId == ReconSnapshot.Id内连接，保留leaselist中所有满足不存在其他LeaseDocNumber相同的ReconFinalCost条目，且该条目对应的ReconSnapshot财年相同但财季更大的数据。

对应的SQL查询如下：

select * from "ReconFinalCost"
inner join "ReconSnapshot" on "ReconFinalCost"."SnapshotId" = "ReconSnapshot"."Id"
where NOT EXISTS (
    SELECT 1 FROM "ReconFinalCost" B
    inner join "ReconSnapshot" RS2 on RS2."Id" = B."SnapshotId"
    where  "ReconFinalCost"."LeaseDocNumber" = B."LeaseDocNumber" and             
           ( RS2."FiscalYear" = "ReconSnapshot"."FiscalYear" and 
             RS2."FiscalQuarter" > "ReconSnapshot"."FiscalQuarter" )
    )

当前我的代码实现：

List<ReconFinalCost> leaselist;
List<ReconFinalCost> originalLeaseList = leaselist;
leaselist = leaselist
    .Where(x => !originalLeaseList.Any(
        y => y.LeaseDocNumber == x.LeaseDocNumber &&
            getIdsOfFutureQuarterSnapshots(x.SnapshotId).Any(
                 futureSnapshotId => futureSnapshotId == y.SnapshotId) ) )
    .OrderBy(x => x.LeaseDocNumber).ToList();
...

private List<long> getIdsOfFutureQuarterSnapshots(long? snapshotId)
{
     // 此函数返回与给定snapshotId同财年但财季更大的ReconSnapshot的Id列表
     ReconSnapshot givenSnapshot = repository.GetReconSnapshotByID( (int)snapshotId );
     return _context.ReconSnapshots.Where(r =>   r.FiscalYear == givenSnapshot.FiscalYear && 
            r.FiscalQuarter > givenSnapshot.FiscalQuarter ).Select(r => r.Id).ToList();
}

请问如何编写LINQ语法，直接实现ReconFinalCost与ReconSnapshot的内连接，并指定「相同财年但更大财季」的WHERE条件，替代当前调用单独函数的方式？

场景1：直接通过EF Core查询数据库（性能更优） #

如果leaselist是从数据库查询而来，建议直接用IQueryable编写查询，LINQ会被翻译成SQL执行，避免内存中处理大量数据：

// 假设_context是你的DbContext实例
var result = _context.ReconFinalCost
    .Join(_context.ReconSnapshot,
          cost => cost.SnapshotId,
          snapshot => snapshot.Id,
          (cost, snapshot) => new { cost, snapshot })
    .Where(joined => !_context.ReconFinalCost
        .Join(_context.ReconSnapshot,
              b => b.SnapshotId,
              rs2 => rs2.Id,
              (b, rs2) => new { b, rs2 })
        .Any(subJoined => subJoined.b.LeaseDocNumber == joined.cost.LeaseDocNumber &&
                          subJoined.rs2.FiscalYear == joined.snapshot.FiscalYear &&
                          subJoined.rs2.FiscalQuarter > joined.snapshot.FiscalQuarter))
    .Select(joined => joined.cost)
    .OrderBy(cost => cost.LeaseDocNumber)
    .ToList();

这段代码完全对应给出的SQL逻辑，通过两次Join实现NOT EXISTS的过滤条件，最终返回符合要求的ReconFinalCost列表。

场景2：处理内存中的List #

如果leaselist已经是内存中的集合，先把关联的ReconSnapshot数据加载到内存，再进行处理：

// 先批量获取所有相关的ReconSnapshot数据，避免多次查询数据库
var snapshotIds = leaselist.Where(c => c.SnapshotId.HasValue).Select(c => c.SnapshotId.Value).Distinct();
var snapshotDict = _context.ReconSnapshots
    .Where(s => snapshotIds.Contains(s.Id))
    .ToDictionary(s => s.Id);

var result = leaselist
    .Where(c => c.SnapshotId.HasValue)
    .Join(snapshotDict,
          cost => cost.SnapshotId.Value,
          snapshotKv => snapshotKv.Key,
          (cost, snapshotKv) => new { cost, snapshot = snapshotKv.Value })
    .Where(joined => !leaselist
        .Where(b => b.SnapshotId.HasValue)
        .Join(snapshotDict,
              b => b.SnapshotId.Value,
              rs2Kv => rs2Kv.Key,
              (b, rs2Kv) => new { b, rs2 = rs2Kv.Value })
        .Any(subJoined => subJoined.b.LeaseDocNumber == joined.cost.LeaseDocNumber &&
                          subJoined.rs2.FiscalYear == joined.snapshot.FiscalYear &&
                          subJoined.rs2.FiscalQuarter > joined.snapshot.FiscalQuarter))
    .Select(joined => joined.cost)
    .OrderBy(cost => cost.LeaseDocNumber)
    .ToList();

这里先将需要的ReconSnapshot数据存入字典，避免重复查询数据库，之后在内存中完成Join和条件过滤，逻辑与SQL完全匹配。

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