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. I mean finding how many records in mytotal are in numerator for each month. 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 Gra

Lost User

You already posted this question in the .NET forum. Please do not crosspost.

Maciej Los

I think you're trying to resolve your issue by complicating it too much... ;) Note, that Count can accept condition to get interesting data (which works like Where). See: [Enumerable.Count Method (System.Linq) | Microsoft Docs](https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.count?view=netcore-3.1#System\_Linq\_Enumerable\_Count\_\_1\_System\_Collections\_Generic\_IEnumerable\_\_\_0\_\_) So...

var finalTotal = 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 && 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,
Granted = g.Count(x=>x.t2.Name == "granted"),
Other = g.Count(x=>x.t2.Name != "granted"),
TotalCount = g.Count()
};