GroupBy + OrderByDescending, returning separate id

Question

I have a query that I just can't get Entity Framework Core 3 (and 5, tried both) to translate.

What I want to do, is to group by a certain id, fetch the latest entry for said id, and return another id. In reality, I want to fetch the the id of the latest chat message for an arbitrary amount of chat rooms.

So with the following class:

class ChatMessageEntry
{
        [Key]
        [Required]
        public Guid Id { get; set; }

        [Required]
        public Guid ChatRoomId { get; set; }

        [Required]
        [DataType(DataType.DateTime)]
        public DateTime CreatedAtUtc { get; set; }

        // message etc here...
}

, given a list of ChatRoomIds, I want to retrieve the Id of the last entry (CreatedAtUtc) per ChatRoomId.

What I have tried (chatRoomIds is a list of Guids, corresponding to ChatMessageEntry.ChatRoomId):

            var lastChatMessagePerChat = await this.chatContext.ChatMessages.AsNoTracking()
                .Where(t => chatRoomIds.Contains(t.ChatRoomId) && !t.DeletedAtUtc.HasValue)
                .GroupBy(t => t.ChatRoomId)
                .Select(g => g.OrderByDescending(t => t.CreatedAtUtc).First())
                .ToListAsync()

...throws...

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression:
KeySelector: new { 
    Id = c.Id, 
    ChatRoomId = c.ChatRoomId, 
    CreatedAtUtc = c.CreatedAtUtc
 }, 
ElementSelector:EntityShaperExpression: 
    EntityType: ChatMessageEntry
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .OrderByDescending(t => t.CreatedAtUtc)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

When I read up on the GroupBy operations in Entity Framework, I understood that only operations such as Count and Max work. So I rewrote it as such:

            var lastChatMessageIdPerChat = await this.chatContext.ChatMessages.AsNoTracking()
                .Where(t => chatRoomIds.Contains(t.ChatRoomId) && !t.DeletedAtUtc.HasValue)
                .GroupBy(t => t.ChatRoomId)
                .Select(g => new
                {
                    Id = g.Select(t => t.Id),
                    CreatedAtUtc = g.Max(t => t.CreatedAtUtc),
                })
                .ToListAsync();

...which in turn throws...

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression:
KeySelector: c.ChatRoomId, 
ElementSelector:EntityShaperExpression: 
    EntityType: ChatMessageEntry
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .Select(s => s.Id)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

...because I'm still using g.Select inside the group. I could retrieve ChatRoomId instead:

            var lastChatMessageIdPerChat = await this.chatContext.ChatMessages.AsNoTracking()
                .Where(t => chatRoomIds.Contains(t.ChatRoomId) && !t.DeletedAtUtc.HasValue)
                .GroupBy(t => t.ChatRoomId)
                .Select(g => new
                {
                    ChatRoomId = g.Key,
                    CreatedAtUtc = g.Max(t => t.CreatedAtUtc),
                })
                .ToListAsync();

...which would technically work but I need the latest entry's Id, not its grouping id, so this is useless to me. If I add another grouping,

.GroupBy(t => new { t.ChatRoomId, t.Id })

, then I can access the Id but the sort no longer works (the result is as if I didn't group them at all).

Very appreciative of help. Thanks!