How to join two linq queries to find some common data between them

[ElenaRez]

I have two linq queries like the following and their output results are correct:

var mytotal = _context.Apiapplicant.Where(c => !c.ApiRequestDate.Equals("") && c.IsDeleted.Equals(false)).GroupBy(o => new
{
Month = o.ApiRequestDate.Substring(5, 2),
Year = o.ApiRequestDate.Substring(0, 4)
}).Select(g => new
{
Month = g.Key.Month,
Year = g.Key.Year,
Total = g.Count()
}).OrderByDescending(a => a.Year).ThenByDescending(a => a.Month).ToList();

The above query gives the total requests that was registerd in each month.

var numerator = 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
//join t4 in mytotal on new { t2.Date.Substring(0,4), t2.Date.Substring(2,5) } equals new { t4.Year, t4.Month }
// join t4 in mytotal on t2.Date.Substring(2, 5) equals t4.Month

                        where t1.IsDeleted == false && t3.Name == "granted" && t2.Date != null && t1.ApiRequestNo != null

                        group t1
                        by new
                        {
                            lastReq = t2.LastReqStatus,
                            Year = t2.Date.Substring(0, 4),
                            Month = t2.Date.Substring(5, 2)
                        } into g

                        select new
                        {
                            Year = g.Key.Year,
                            Month = g.Key.Month,
                            lastReq = g.Key.lastReq,
                            GrantedCount = g.Count()
                        };


        var GrantedReqStatus = numerator.OrderByDescending(x => x.Year).ThenByDescending(a => a.Month).ToList();

The above query gives total requests for each month that has "granted" status. Now I want to find for all the requests that are registered in each month, how many of them has "granted" status. To obtain it, I've made a query like the following which should join both GrantedReqStatus and mytotal queries:

var LastGrantedStatus = (from t1 in mytotal
from t2 in GrantedReqStatus
where (t1.Month == t

[Lost User]

Don't attempt to join. Have the results in their lists as you already have, and then check those lists for items that are "common/similar". The more you do in a linq query, the more complex it becomes, up to the point where you get stuck on questions like this one. Do the queries, then continue processing, instead of trying to merge the queries.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.