It seems that I need some help from EF guru. So, I have Postgres DB with EF Core in my project. There is a table RequestDbModel which might contain many rows with the same Id and different ts. I need to select Request collection from this table where Items are rows with the same Id and also some extra fields have to be filled from these Items ordered by ts. I can do this, but only if I am materializing the query. I believe this can be done without materializing, but I cannot figure out how and I would appreciate any help

• Request.Status – Status of the lates (newest) item in Items
• Request.CreatedTime – ts of the first (oldest) item in Items
• Request.AppliedTime – oldest ts of item in Items with Status == “Approved”
• Request.CreatorId – UserId of the first (oldest) item in
  Items
• Request.ApproverId – UserId for the oldest item in Items with
  Status == “Approved” or null
• Request.Comment – Comment for the oldest
  item in Items

    // builder.HasKey(p => new { p.Id, p.Timestamp })
    //         .HasName("requests_pkey");
    public class RequestDbModel
    {
        public long Id { get; init; }
        public required string Status { get; set; }
        public required DateTime Timestamp { get; init; }
        public required Guid UserId { get; init; } 
        public string? Comment { get; init; }
    }
    
    public sealed class Request
    {
        public required long Id { get; init; }
        public required string Status { get; init; }
        public DateTime CreatedTime { get; init; }
        public DateTime? AppliedTime { get; init; }
        public required Guid CreatorId { get; init; }
        public Guid? ApproverId { get; init; }
        public string? Comment { get; init; }
        public required RequestItemHistory[] Items { get; init; }
    }

    public sealed class RequestItemHistory
    {
        public required string Status { get; init; }
        public required DateTime Timestamp { get; init; }
        public required Guid UserId  { get; init; }
        public string? Comment { get; init; }
    }
    
    public IQueryable<Request> GetRequests()
    {
        var groups = context.Requests.AsNoTracking()
            .GroupBy(r => r.Id)
            .Select(g => new
            {
                Id = g.Key,
                Items = g.OrderBy(r => r.Timestamp).Select(r => new RequestItemHistory
                {
                    Status = r.Status,
                    Timestamp = r.Timestamp,
                    UserId = r.UserId,
                    Comment = r.Comment,
                })//.ToList()
            });

        var result = groups
            .ToList()
            .Select(x => new Request
            {
                Id = x.Id,
                Status = x.Items.Last().Status,
                CreatedTime = x.Items.First().Timestamp,
                AppliedTime = x.Items.Where(i => i.Status == "Approved").OrderByDescending(i => i.Timestamp).Select(i => (DateTime?)i.Timestamp).FirstOrDefault(),
                CreatorId = x.Items.First().User,
                ApproverId = x.Items.Where(i => i.Status == "Approved").OrderByDescending(i => i.Timestamp).Select(i => i.User).FirstOrDefault(),
                Comment = x.Items.Last().Comment,
                Items = x.Items.ToArray()
            }).AsQueryable();

        return result;        
    }