How to project LINQ-to-SQL query into custom object with a GroupBy and OrderBy

I'm trying to convert this query so that it will output to a custom DTO type object. I want to get only pages with the highest revision number for the int[] that I pass in.

return from page in db.Pages
               where intItemIdArray.Contains(page.pageId)
               group page by page.pageId into g
               orderby g.Max(x => x.pageId)
               select g.OrderByDescending(t => t.revision).First();

But when I try to replace

select g.OrderByDescending(t => t.revision).First();

With something like

select (new JPage {pageid = g.pageId, title = g.title, etc})
    .OrderByDescending(t => t.revision)
    .First();

It doesn't work, can anyone help me out?

This is what I have gone with currently, which I don't like, but it is working perfectly, and I don't need to optimize beyond this currently.

It would be great if someone could improve this.

var pages = from page in db.Pages
               where intItemIdArray.Contains(page.pageId)
               group page by page.pageId into g
               orderby g.Max(x => x.pageId)
               select g.OrderByDescending(t => t.revision).First();

        return pages.Select(x => new JPage() { 
            pageId = x.pageId,
            pageKey = x.pageKey,
            title = x.title,
            body = x.body,
            isFolder = x.isFolder.ToString(),
            leftNode = x.leftNode,
            rightNode = x.rightNod开发者_开发技巧e,
            revision = x.revision,
            sort = x.sort,
            createdBy = x.createdBy.ToString(),
            createdDate = Utility.DateTimeToUnixTimeStamp(x.createdDate).ToString(),
            modifiedDate = Utility.DateTimeToUnixTimeStamp(x.modifiedDate).ToString(),
            pageVariationId = x.pagesVariationId,
            parentId = x.parentId
        })
        .AsQueryable(); 

I'd suggest that you order before you select; i.e. instead of

select (new JPage {pageid = g.pageId, title = g.title, etc}
    .OrderByDescending(t => t.revision).First();

you should try

.OrderByDescending(t => t.revision)
    .Select(new JPage {pageid = g.pageId, title = g.title, etc})
    .First();

You can't order by 'revision' if it doesn't exist in the result of the previous 'select'

This should be a slight improvement

var pages = from page in db.Pages
               where intItemIdArray.Contains(page.pageId)
               group page by page.pageId into g
               select g.First(a => a.revision == g.Max(b => b.revision));