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