I have two tables, Organization and ApplicationUser. The relationship between them is One to Many. That means one Organization can have multiple Users. Now I need to write a query to show some organization properties along with the total users for each organization. I am trying to write the query. But after GroupBy function whenever I try to fetch the Property nothing comes. Here is the query:
var lists = await (from org in _dbContext.Organizations.AsNoTracking()
join dept in _dbContext.Departments.AsNoTracking() on org.Id equals dept.OrganizationId into orgDeptTemp
from orgDept in orgDeptTemp.DefaultIfEmpty()
join user in _dbContext.ApplicationUsers.AsNoTracking() on org.Id equals user.OrganizationId into orgUserTemp
from orgUser in orgUserTemp.DefaultIfEmpty()
group org by org.Id into orgGroupTemp
select new OrganizationDto
{
OrganizationId = orgGroupTemp.Key,
OrganizationName = orgGroupTemp.Key.......,
TotalUsers = How to get the total user
})
.ToListAsync();
In SQL the only available columns after a GROUP BY are the group key and aggregated columns. You need to add them into the group by line. So for example, group by org id and name