The Linq expression cannot be translated

Question

I am working on the following linq query.

locations = from s in this._dbContext.Sublocations.AsNoTracking()
            join w in this._dbContext.WOWorkCenterAssignments.AsNoTracking() on s.ID equals w.ID                                
            group w by s.LocationID
            into g
            select new EAMSubSite
            {
                  LocationId = g.Key,
                  Workcenters = g.Select(x => new WorkcenterInfo { Id = x.ID, Name = x.Name }).ToList()
            };

But it's giving me this error.

The LINQ expression '(GroupByShaperExpression: KeySelector: (s.LocationID),  ElementSelector:(EntityShaperExpression: 
    EntityType: WorkCenterAssignment
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False ) )
    .Select(x => new WorkcenterInfo{ 
        Id = x.ID, 
        Name = x.Name 
    }
    )' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I am not able to figure out what's going wrong here. Any help is much appreciated.

Answer 1

There is no equivalent in the SQL which groups items and then retrieve details. Grouping in SQL for aggregation only. You have to group entities on the client side. To make query faster, we can retrieve only needed fields and group them on the client:

var rawData = 
    from s this._dbContext.Sublocations
    join w in this._dbContext.WOWorkCenterAssignments on s.ID equals w.ID                                
    select new 
    {
        s.LocationID,
        w.ID,
        w.Name
    };

locations = 
    from r in rawData.AsEnumerable()
    group r by r.LocationID into g
    select new EAMSubSite
    {
        LocationId = g.Key,
        Workcenters = g.Select(x => new WorkcenterInfo { Id = x.ID, Name = x.Name }).ToList()
    };