Converting SQL to LINQ query when I cannot use "IN"

I'm trying to convert this very simple piece of SQL to LINQ:

select * from Projects p
inner join Documents d
    on p.ProjectID = d.ProjectID
left join Revisions r
    on r.DocumentID = d.DocumentID 
    and r.RevisionID IN (SELECT max(r2.RevisionID) FROM Revisions r2 GROUP BY r2.DocumentID) 
WHERE p.ProjectID = 21 -- Query string in code

This says, if any revisions exist for a document, return me the highest revision ID. As it's a left join, if not revisions exist, I still want the results returned.

This works as expected, any revisions which exist are shown (and the highest revision ID is returned) and so are all documents without any revisions.

When trying to write this using LINQ, I only get results where revisions exist for a document.

Here is my attempt so far:

    var query = from p in db.Projects
                              join d in db.Documents on new { ProjectID = p.ProjectID } equals new { ProjectID = Convert.ToInt32(d.ProjectID) }
                              join r in db.Revisions on new { DocumentID = d.DocumentID } equals new { DocumentID = Convert.ToInt32(r.DocumentID) } into r_join
                              from r in r_join.DefaultIfEmpty()
                              where
                               (from r2 in db.Revisions
                                   group r2 by new { r2.DocumentID }
                                       into g
                                       select new { MaxRevisionID = g.Max(x => x.RevisionID) }).Contains(
                                       new { MaxRevisionID = r.RevisionID }) &&
                                p.ProjectID == Convert.ToInt32(Request.QueryString["projectId"])
                              select new { d.DocumentID, d.DocumentNumber, d.DocumentTitle, RevisionNumber = r.RevisionNumber ?? "<No rev>", Status = r.DocumentStatuse == null ? "<Not set>" : r.DocumentStatuse.Status };

I'm not very good at LINQ and have been using the converter "Linqer" to help me out, but when trying I get the following message:

"SQL cannot be converted to LINQ: Only "=" operator in JOIN expression can be used. "IN" operator cannot be converted."

You'll see I have .DefaultIfEmpty() on the revisions table. If I remove the where ( piece of code which does the grouping, I get the desired results whether or not a revision exists for a document or not. But the where clause should return the highest revision number for a document IF there is a link, if not I still want to return all the other data. Unlike my SQL code, this doesn't happen. It only ever returns me data where there is a link to the revisions table.

I hope that makes a little bit of sense. The group by code is what is messing up my result set. Regardless if there is a link to the revisions table, I still want my results returned. Please help!

Thanks.

=======

The code I am now using thanks to Gert.

        var query = from p in db.Projects
                    from d in p.Documents
                    where p.ProjectID == Convert.ToInt32(Request.QueryString["projectId"])
                    select new
                               {
                                   p.ProjectID,
                                   d.DocumentNumber,
                                   d.DocumentID,
                                   d.DocumentTitle,
                                   Status = d.Revisions
                                    .OrderByDescending(rn => rn.RevisionID)
                                    .FirstOrDefault().DocumentStatuse.Status,
                                   RevisionNumber = d.Revisions
                                    .OrderByDescending(rn => rn.RevisionID)
                                    .FirstOrDefault().RevisionNumber
                               };

        gvDocumentSelection.DataSource = query;
        gvDocumentSelection.DataBind();

Although this works, you'll see I'm selecting two fields from the revisions table by running the same code, but selecting two different fields. I'm guessing there is a better, more efficient way to do this? Ideally I would like to join on the revisions table in case I need to access more fields, but then I'm left with the same grouping problem again.

           Status = d.Revisions
            .OrderByDescending(rn => rn.RevisionID)
            .FirstOrDefault().DocumentStatuse.Status,
           RevisionNumber = d.Revisions
            .OrderByDescending(rn => rn.RevisionID)
            .FirstOrDefault().RevisionNumber

Final working code:

        var query = from p in db.Projects
                    from d in p.Documents
                    where p.ProjectID == Convert.ToInt32(Request.QueryString["projectId"])
                    select new
                               {
                                   p.ProjectID,
                                   d.DocumentNumber,
                                   d.DocumentID,
                                   d.DocumentTitle,
                                   LastRevision = d.Revisions
                        .OrderByDescending(rn => rn.RevisionID)
                        .FirstOrDefault()
                               };

        var results = from x in query
                      select
                          new
                              {
                                  x.ProjectID,
                                  x.DocumentNumber,
                                  x.DocumentID,
                                  x.DocumentTitle,
                                  x.LastRevision.RevisionNumber,
                                  x.LastRevision.DocumentStatuse.Status
                              };

        gvDocumentSelection.DataSource = results;
        gvDocumentSelection.DataBind();