Csharp 外部应用&&的LINQ；左外部连接&类似于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)

它try 首先 Select 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 With Result类似于这个查询？

提前谢谢！