This is my first time using an anonymous function with LINQ syntax. We are getting multiple Id's to set up a way for us to query another table for a specific "effective date" but something is throwing off my query.

Error Message

'((Microsoft.AspNetCore.Http.DefaultHttpContext)httpContext).Session' threw an exception of type 'System.InvalidOperationException'

"System.InvalidOperationException: The LINQ expression 
'GroupByShaperExpression:\nKeySelector: new { \n    operatorId = 
t.OperatorId, \n    regionId = t.RegionId\n }, 
\nElementSelector:new { \n    operatorId = 
ProjectionBindingExpression: operatorId, \n    regionId = 
ProjectionBindingExpression: regionId, \n    effectiveDate = 
ProjectionBindingExpression: effectiveDate\n }\n    
.OrderByDescending(ed => ed.effectiveDate)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 
 'ToListAsync'.

Any help would be great!

Query

     var opRegionEffectiveDate = await (from reg in  _orppr.GetAllQueryable()
           .Where(opReg => result.Select(o => o.Id).Contains(opReg.OperatorId)).Select(opReg => new {operatorId = opReg.OperatorId, regionId = opReg.RegionId, effectiveDate = opReg.EffectiveDate}).Distinct()
            group reg by new  {reg.operatorId, reg.regionId}
            into regef 
            select new { oprId = regef.Key.operatorId, regId = regef.Key.regionId, efdate = regef.OrderByDescending(ed => ed.effectiveDate).FirstOrDefault()})
            .ToListAsync();

Full Method

public async Task<IEnumerable<OperatorViewModel>> GetAllAsync()
    {
        var retList = new List<OperatorViewModel>();
        var result = await _operatorRepository.GetAllQueryable().Include(o => o.GroupEmails).Where(o => o.TenantId == TenantId).ToListAsync();
        // var regions =  _orppr.GetAllQueryable();
       var opRegionEffectiveDate = await (from reg in  _orppr.GetAllQueryable()
           .Where(opReg => result.Select(o => o.Id).Contains(opReg.OperatorId)).Select(opReg => new {operatorId = opReg.OperatorId, regionId = opReg.RegionId, effectiveDate = opReg.EffectiveDate}).Distinct()
            group reg by new  {reg.operatorId, reg.regionId}
            into regef 
            select new { oprId = regef.Key.operatorId, regId = regef.Key.regionId, efdate = regef.OrderByDescending(ed => ed.effectiveDate).FirstOrDefault()})
            .ToListAsync();


        var regions = await _regionRepository.GetAllQueryable()
        .Where(reg => opRegionEffectiveDate.Select(ore => ore.regId).Contains(reg.Id))
        .ToListAsync();

        foreach (var oper in result)
        {
            var regionsToAdd = opRegionEffectiveDate.Where(r => r.oprId == oper.Id).Select(r => r.regId);

            var regionsList = regions.Where(r => regionsToAdd.Contains(r.Id));

            // var currRegions = await regions.Where(opReg => opReg.OperatorId == oper.Id)
            //     .Include(opReg => opReg.Region)
            //     .Select(opReg => opReg.Region)
            //     .OrderBy(reg => reg.Name)
            //     .Distinct()
            //     .ToListAsync();

            var currOper = _mapper.Map<Operator, OperatorViewModel>(oper);
            currOper.Regions = _mapper.Map<IEnumerable<Region>, IEnumerable<RegionViewModel>>(regionsList);
            foreach(var reg in currOper.Regions)
            {
                var item = opRegionEffectiveDate.Single(r => r.oprId == oper.Id && r.regId == reg.Id);
                reg.EffectiveDate = item.efdate.ToString();
            }
            retList.Add(currOper);
        }
        return retList.OrderBy(r => r.OperatorName);
    }

CodePudding user response：

    var opRegionEffectiveDate = await _orppr
        .GetAllQueryable().Where(opReg => result.Select(o => o.Id).Contains(opReg.OperatorId))
.Distinct()
        .OrderByDescending(ed => ed.effectiveDate)
        .Select(opReg => new {operatorId = opReg.OperatorId, regionId = opReg.RegionId, effectiveDate = opReg.EffectiveDate})
        .GroupBy(reg =>new  {reg.operatorId, reg.regionId})
.Select(x => x).FirstOrDefault()
        .Select(x => new { oprId = x.operatorId, regId = x.regionId, x.effectiveDate})
        .ToListAsync();

CodePudding user response：

Try following :

var opRegionEffectiveDate = await (_orppr
    .GetAllQueryable().Where(opReg => result.Select(o => o.Id).Contains(opReg.OperatorId))
    .OrderByDescending(ed => ed.effectiveDate)
    .Select(opReg => new {operatorId = opReg.OperatorId, regionId = opReg.RegionId, effectiveDate = opReg.EffectiveDate})
    .GroupBy(reg =>new  {reg.operatorId, reg.regionId})
    .Select(x => x).FirstOrDefault()
    .Select(x => new { oprId = x.Key.operatorId, regId = x.Key.regionId, x.effectiveDate})
    .ToListAsync()
    );