Using the following separate queryables, we were able to generate expected SQL query mentioned in the question.
var reporterId = 31;
var joinedEntriesQueryable =
from reportedTimeEntry in dbContext.ReportedTimeEntries
join ticketEntry in dbContext.TicketEntries
on reportedTimeEntry.TicketId equals ticketEntry.Id
where reportedTimeEntry.ReporterId == reporterId
where ticketEntry.IsFinished == false
select new
{
ticketEntry.Id,
ticketEntry.IsFinished,
ticketEntry.Title,
reportedTimeEntry.Created
};
var lastReportedTicketsQueryable =
from joinedEntry in joinedEntriesQueryable
group joinedEntry
by new { joinedEntry.Id, joinedEntry.IsFinished, joinedEntry.Title }
into groupedJoinedEntries
select new
{
groupedJoinedEntries.Key,
maxCreated = groupedJoinedEntries.Max(x => x.Created),
};
var result = await lastReportedTicketsQueryable
.OrderByDescending(x => x.maxCreated)
.Take(3)
.ToListAsync();