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

David-6289 5 Reputation points
2023-03-17T17:16:58.71+00:00

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();
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
696 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. David-6289 5 Reputation points
    2023-03-18T17:45:06.6166667+00:00

    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();
    
    1 person found this answer helpful.
    0 comments No comments

  2. Andrey Novozhilov 110 Reputation points
    2023-03-18T14:21:54.9966667+00:00

    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.