The Linq expression cannot be translated-CodePudding

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.

CodePudding user response：

EF Core is notoriously weak at translating groupings. It either has to do with the ToList call in your select clause. In that case try to remove it and convert the property to an IEnumerable. Else, replace the join with an inner select:

locations = from s in this._dbContext.Sublocations.AsNoTracking()
        select new EAMSubSite
        {
              LocationId = s. ...,
              Workcenters = from w in this._dbContext.WOWorkCenterAssignments ...
        };

CodePudding user response：

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