I have a table which contains rows like below
Month - Agent - ResolveCount
1 - John - 14
1 - John - 12
2 - Smith - 64
2 - Smith - 23
My Model:
public int Month { get; set; }
public Agent Agent { get; set; }
public int ResolveCount { get; set; }
My Linq Query
List<ResolveMonthlyAgentReport> _result =
(from t in ConversationCollection.AsQueryable<Conversation>().AsEnumerable()
where t.CompanyId == _companyId && t.CreateDate.Year == _year && t.ResolvedDate != null && t.ResolvedAgentId != null
group t by new { t.CreateDate.Month, t.ResolvedAgentId }
into grp
orderby grp.Key.Month
select new ResolveMonthlyAgentReport
{
Agent = _agentList.Where(p => p.AgentId.ToString() == grp.Key.ResolvedAgentId).FirstOrDefault(),
Month = grp.Key.Month,
ResolveCount = grp.Count(t => t.ResolvedAgentId == grp.Key.ResolvedAgentId)
})
.Where(p => p.Agent != null)
.ToList();
Result:
Month - Agent - ResolveCount
1 - John - 14
1 - John - 12
2 - Smith - 64
2 - Smith - 23
. .
This is the result I Want:
Agent - 1.month - 2.month - x.month
John - 14 - 12
Smith - 64 - 23
How should my linq query be?