GROUP BY together with ORDER BY MAX in Entity Framework Core using LINQ

Question

Let's assume these 2 classes and corresponding DB tables.

public class Ticket
{
    public int Id { get; set; }
    public string Title { get; set; }
    public bool IsFinished { get; set; }
}

public class ReportedTime
{
    public int Id { get; set; }
    public int ReporterId { get; set; }
    public int TicketId { get; set; }
    public DateTime Created { get; set; }
    public int ReportedMinutes { get; set; }
}

We would like to display the last 3 distinct unfinished (IsFinished equals false) tickets on which a specific reporter (e.g. ReporterId equals 31) reported time.

In SQL, we can imagine this version (not necessary if you have a better idea):

SELECT TOP(3) [t].[Id], [t].[IsFinished], [t].[Title]
FROM [ReportedTime] AS [r]
INNER JOIN [Ticket] AS [t] ON [r].[TicketId] = [t].[Id]
WHERE ([r].[ReporterId] = 31) AND ([t].[IsFinished] = CAST(0 AS bit))
GROUP BY [t].[Id], [t].[IsFinished], [t].[Title]
ORDER BY MAX([r].[Created]) DESC

In C#/Entity Framework Core, we struggle with the translation of the GROUP BY part together with the ORDER BY MAX part. Any hint please?

var reporterId = 31;
var ticketsQueryable =
    from reportedTimeEntry in dbContext.ReportedTimeEntries
    join ticketEntry in dbContext.TicketEntries
        on reportedTimeEntry.TicketId equals ticketEntry.Id
    where reportedTimeEntry.ReporterId == reporterId
    where ticketEntry.IsFinished == false
    group ticketEntry
        by new { ticketEntry.Id, ticketEntry.Title, ticketEntry.IsFinished }
        into groupedTicketEntry
    // orderby ? descending
    select groupedTicketEntry.Key;

var result = await ticketsQueryable.Take(3).ToListAsync();

Answer 1

Hello Davi

i'm assuming that your Ticket.Id is unique

You can get your unfinished tickets together with report Created property in a group collection of anonymous type:

var groupItems =  dbContext.ReportedTimeEntries
                  .Where(r => r.ReporterId == 31)
		          .Select(r=> new {ticket = dbContext.TicketEntries.FirstOrDefault(t => t.Id == r.TicketId && t.IsFinished == false), created = r.Created})
		         .OrderByDescending(e => e.created)  
		         .GroupBy(x => x.ticket.Id);
// groupItems are IEnumerable<IGrouping<int,object>> 

then you just take top item from each group, and order by date again

var items = groupitems

               .Select(g => g.FirstOrDefault())

               .OrderByDescending(x=>x.created) 

               .Take(3);

You can combine expressions in one.

Answer 2

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();