Grouping in EF Core 3.x does seem to be quite picky about what it can and cannot translate.
Try projecting the navigation property value first:
var query = from t1 in _context.Apiapplicant
join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id
let tg = new
{
t2.LastReqStatus,
lastReqName = t2.LastReqStatusNavigation.Name
}
group tg by tg.LastReqStatus into ApiAppGp
select new
{
lastReqName = ApiAppGp.Max(x => x.lastReqName),
ReqCount = ApiAppGp.Count()
};
var RegisteredReqStatus = query.ToList();
If it still doesn't work, you might need to fall back to the extension method instead:
var query = from t1 in _context.Apiapplicant
join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id
select new
{
t2.LastReqStatus,
lastReqName = t2.LastReqStatusNavigation.Name
};
var RegisteredReqStatus = query
.GroupBy(tg => tg.LastReqStatus, (key, items) => new
{
lastReqName = items.Max(x => x.lastReqName),
ReqCount = items.Count()
})
.ToList();