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

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

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)
        .FirstOrDefault())
    .ToList();
   
Comments
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
   
5ed!
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