Click here to Skip to main content
14,775,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

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

            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();
Updated 16-Jul-20 4:03am

1 solution

EF Core's GroupBy support can be a bit flaky. Try starting the query with the users instead:
var skipperLocations = _appContext.Users
    .Select(u => u.Locations
        .Where(l => l.Timestamp > cutOffDate)
        .OrderByDescending(l => l.Timestamp)
Torakami 16-Jul-20 9:40am
Not sure if this will work, looking at what kind of query I am trying to build, let me check.
Maciej Los 16-Jul-20 9:48am
kswoll 19-Feb-21 17:18pm
This solution worked great in my (similar) scenario. Thanks Richard!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900