Can't select properties after group by in linq query-CodePudding

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();

CodePudding user response：

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

CodePudding user response：

assuming your model is set up correctly use navigation properties

var query = from org in _dbContext.Organizations
            where org.Departments.Any(d => d.Whatever)
            select new OrganizationDto
            {
                OrganizationId = org.Id,
                OrganizationName = org.Name,
                TotalUsers = org.Users.Count(),
            };
var list = await query.AsNoTracking().ToListAsync();