在Simple.Data中模拟子查询

Question

经过广泛的研究，我知道不能在Simple.Data中进行子查询。然而，有了足够的联接和排序，我偶尔会看到CTE弹出，这对我来说很有用。我创建了一个fiddle来演示我想用Simple.Data（在ADO.NET之上）实现什么，我只是不知道如何在Simple.Data查询（或一组查询）中构建它。

如果我有两张桌子：

CREATE TABLE Request
    (id int, 
     payload varchar(50))
;
CREATE TABLE Response
    (id int,
     requestId int,
     payload varchar(50),
     sortableValue int,
     filterField bit)
;

如何获得子表的已筛选顶部结果？例如

SELECT *
FROM Request ereq
JOIN
    Response eres 
    ON eres.id = (SELECT TOP 1 id from Response 
                  WHERE requestId = ereq.id AND filterField = 1
                  ORDER BY sortableValue DESC)

或使用CTE

WITH sorted_content AS 
(
  SELECT ROW_NUMBER() OVER (PARTITION BY requestId ORDER BY sortableValue DESC) as rowId,
  *
  FROM Response
  WHERE filterField = 1
)
SELECT * 
FROM Request ereq
JOIN sorted_content sorted_eres 
     ON sorted_eres.requestId = ereq.id 
        AND sorted_eres.rowId = 1

http://sqlfiddle.com/#！6/33ac5/17

目标是能够在Simple.Data表达式中执行类似于上述表达式的操作，这样我就可以利用分页了。如果我在事后进行筛选，分页就会变得有问题/代价高昂。

Answer 1

我希望有人能想出一个更好的解决方案，但我们最终想出的解决方案是使用View:

CREATE VIEW FilteredResponse
AS 
  SELECT Response1.* 
  FROM   (SELECT (SELECT TOP (1) id 
                  FROM   Response AS ri 
                  WHERE  ( requestId = ro.requestId ) 
                         AND ( filterField = 1 ) 
                  ORDER  BY sortableValue DESC) AS rid 
          FROM   (SELECT DISTINCT requestId 
                  FROM   Response) AS ro) AS sortedResponse 
         INNER JOIN Response AS Response1 
                 ON sortedResponse.rid = Response1.id 

现在，下面的SQL生成了适当的结果：

SELECT * FROM Request ereq
JOIN FilteredResponse eres ON eres.requestId = ereq.id

这是一个非常简单的simple.数据查询：

_db.Request.FindAll().Join(_db.FilteredResponse).On(_db.FilteredResponse.RequestId == _db.Request.Id)

全小提琴：http://sqlfiddle.com/#！6/fe341/3/0

我们可以这样做，因为我们的filterField值在所有情况下都是已知的。这将不适用于动态filterField。

我仍然希望社区里有人能找到一种更清洁的方式来实现这一点。