How do I write complex group by LINQ query in dot net core 3.1 ?

See more:

.NET-Core

EF-Core

The problem is that, in the group by I want the first result in the group by using first() or firstOrDefault() but I get an error saying

Quote:

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

Now, If I use IEnumerable .. I am taking all the records which I don't need it and then I am applying group by which is impacting my performance.

Instead of first() if I use min() or max() query did work, but that is the invalid entry I am getting.

All I want is , in group by I want first() records for each row.

What I have tried:

Old query

var cutOffDate = DateTime.UtcNow.AddMinutes(-cutOffMinutes);

            var skipperLocations = GetUserLocations().AsEnumerable()
                .Where(l => l.Timestamp > cutOffDate)
                .OrderByDescending(l => l.Timestamp)
                .GroupBy(l => l.UserId)
                .Select(l => new UserLocation
                {
                    UserId = l.Key,
                    Latitude = l.First().Latitude,
                    Longitude = l.First().Longitude,
                    Speed = l.First().Speed,
                    Timestamp = l.First().Timestamp
                }).ToList();

            return skipperLocations.Join(_appContext.Users, o => o.UserId, i => i.Id, (o, i) => new SkipperLocation
            {
                UserId = o.UserId,
                Latitude = o.Latitude,
                Longitude = o.Longitude,
                Speed = o.Speed,
                Timestamp = o.Timestamp,
                Name = i.FriendlyName,
                BoatNumber = i.BoatNumber
            });

New Query which is working, but not getting proper records due to min()

var cutOffDate = DateTime.UtcNow.AddMinutes(-cutOffMinutes);
            return (from cust in _appContext.UserLocations
                    join u in _appContext.Users
                    on cust.UserId equals u.Id
                    where cust.Timestamp > cutOffDate
                    group new { cust, u } by new { cust.UserId, u.FirstName, u.LastName, u.BoatNumber } into g
                    select new SkipperLocation
                    {
                        UserId = g.Key.UserId,
                        Latitude = g.Min(x => x.cust.Latitude),
                        Longitude = g.Min(x => x.cust.Longitude),
                        Speed = g.Min(x => x.cust.Speed),
                        Timestamp = g.Max(x => x.cust.Timestamp),
                        Name = g.Key.FirstName + " " + g.Key.LastName,
                        BoatNumber = g.Key.BoatNumber
                    }).OrderByDescending(c => c.Timestamp).AsNoTracking().ToList();