Aggregation count in linq query returns null

I'm implementing asp.net core 3.1 project. In my controller I have a linq query like the following and without defining inprocessapicount and pendingcount which claculates counts of related amounts, it works fine but after adding them to applicants query in select part, applicants returns null and the error is: .Count(a => a.requestStatus == "bb") 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() . I appreciate if anyone suggests me a solution.


What I want is: There are some applicants in Database and each applicant may have multiple or zero requests. (Each applicant orders multiple or zero itemID) each one of those applicants needs to get some item (itemID) and each of those item has requeststatus =="aa" or requeststatus =="bb" or none of them which the status in that case is "general". Now I want to understand, how many each applicant's itemID has requeststatus=="aa" and how many each applicant's itemID has requeststatus=="bb" according to all ItemId they have ordered.

var applicants = (from t1 in _context.VwDesk

                              {

                                  itemID = t1.ItemId,
                                  applicantID = t1.ApplicantId,
                                  applicantName = t1.ApplicantName,

                                  gateName =t1.GateName,

                                  requestStatus = t1.LastReqStatus
                              }
                              group tg by new { tg.requestStatus,tg.itemID ,tg.applicantID, tg.applicantName} into ApiAppGp
                              select new
                              {
                                  applicantName = ApiAppGp.Key.applicantName,

                                  itemname = ApiAppGp.Key.itemID,

                                  itemcount =ApiAppGp.Count(),

                                  inprocessapicount = ApiAppGp.Where(a => a.requestStatus == "bb").Count(),
                                  pendingapicount = ApiAppGp.Where(a => a.requestStatus == "aa").Count()


                              }).ToList();