LINQ for “Outer Apply” / “Left Outer Join” 有点像 PostgreSQL 的查询

问：

我的 linq 查询有问题，它执行得非常慢。当我调试并查看翻译后的查询时，我看到了问题所在。我的 LinQ 并没有真正实现我想要的查询。

这是我现在拥有的 LinQ：

from doc in context.document
from lasthistory in context.documenthistory
    .Where(x => x.documentid == doc.id)
    .OrderByDescending(x => x.actiondatetime)
    .Take(1)
    .DefaultIfEmpty()
where lasthistory.actiondatetime >= periodFrom
    && lasthistory.actiondatetime < periodTo.AddDays(1)
select new
{
    id = doc.id,
    lastactionby = lasthistory.actionby,
    lastactiondatetime = lasthistory.actiondatetime
}

下面是这个 LINQ 的翻译查询

SELECT d.id, t0.actionby AS lastactionby, t0.actiondatetime AS lastactiondatetime
FROM dbo.document AS d
LEFT JOIN (
    SELECT t.actionby, t.actiondatetime, t.documentid
    FROM (
        SELECT d0.actionby, d0.actiondatetime, d0.documentid, ROW_NUMBER() OVER(PARTITION BY d0.documentid ORDER BY d0.actiondatetime DESC) AS row
        FROM dbo.documenthistory AS d0
    ) AS t
    WHERE t.row <= 1
) AS t0 ON d.id = t0.documentid
WHERE (t0.actiondatetime >= @__periodFrom_1) AND (t0.actiondatetime < @__AddDays_2)

它首先尝试选择documenthistory中的所有数据，这就是我的查询执行速度非常慢的原因。

我现在已经将 LinQ 更新为如下所示：

from doc in context.document
let lasthistory = context.documenthistory
    .Where(x => x.documentid == doc.id)
    .OrderByDescending(x => x.actiondatetime)
    .FirstOrDefault()
where lasthistory.actiondatetime >= periodFrom
    && lasthistory.actiondatetime < periodTo.AddDays(1)
select new
{
    id = doc.id,
    lastactionby = lasthistory.actionby,
    lastactiondatetime = lasthistory.actiondatetime
}

它的性能比第一个更好，但是翻译后的查询有点“丑陋”，子查询太多了

SELECT d.id, 
    (
        SELECT d2.actionby 
        FROM dbo.documenthistory AS d2 
        WHERE d2.documentid = d.id 
        ORDER BY d2.actiondatetime DESC 
        LIMIT 1
    ) AS lastactionby, 
    (
        SELECT d3.actiondatetime 
        FROM dbo.documenthistory AS d3 
        WHERE d3.documentid = d.id 
        ORDER BY d3.actiondatetime DESC 
        LIMIT 1
    ) AS lastactiondatetime
FROM dbo.document AS d
WHERE ((SELECT d0.actiondatetime FROM dbo.documenthistory AS d0 WHERE d0.documentid = d.id ORDER BY d0.actiondatetime DESC LIMIT 1) >= @__periodFrom_1)
    AND ((SELECT d1.actiondatetime FROM dbo.documenthistory AS d1 WHERE d1.documentid = d.id ORDER BY d1.actiondatetime DESC LIMIT 1) < @__AddDays_2)

我想要的查询是这样的：

SELECT d.id, t.actionby AS lastactionby, t.actiondatetime AS lastactiondatetime
FROM dbo.document AS d
LEFT JOIN LATERAL (
    SELECT d0.actionby, d0.actiondatetime
    FROM dbo.documenthistory AS d0
    WHERE d0.documentid = d.id
    ORDER BY d0.actiondatetime DESC
    FETCH FIRST 1 ROW ONLY
) t ON true
WHERE (t.actiondatetime >= @__periodFrom_1) AND (t.actiondatetime < @__AddDays_2)

有没有办法在 Linq 中做到这一点？或者也许 Linq 的结果与此查询类似？

提前致谢！