Simulate Subquery in Simple.Data?

It is my understanding after extensive research that you cannot do sub-queries in Simple.Data. However, with enough joins and sorts, I have occasionally seen a CTE pop out, which would serve my purposes. I have created a fiddle to demonstrate what I'd like to achieve with Simple.Data (on top of ADO.NET), I just can't figure out how to structure this in a simple.data query (or set of queries).

If I have two tables:

CREATE TABLE Request
    (id int, 
     payload varchar(50))
;

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

How can I get a filtered top result of the child table? e.g.

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)

or with a 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

The goal is to be able to perform something similar to the above expressions in a Simple.Data expression so that I can leverage paging. If I filter after the fact, paging becomes questionable/expensive.

Solution

I'm hoping somebody will come up with a better solution, but the solution we finally came up with was to use a 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

And now the following SQL produces the appropriate result:

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

Which is a very simple Simple.Data query:

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

Full fiddle: http://sqlfiddle.com/#!6/fe341/3/0

We can get away with this because our filterField value is known for all cases. This will not work for a dynamic filterField.

I'm still hoping somebody from the community can find a cleaner way to implement this.