Multiple connections with groupby in linq

Question

I have 5 tables from which I want to get some information using linq.i using the following query to read data from data.

var query = (from GRD in _tblStudents.GetQueryable() join apt in _tblApplicants.GetQueryable() on GRD.ApplicantID equals apt.ApplicantID join cls in _tblClasses.GetQueryable() on GRD.CityID equals cls.ClassID join prg in _tblPrograms.GetQueryable() on cls.ProgramID equals prg.ProgramID join city in _tblCities.GetQueryable() on GRD.CityID equals city.CityID where GRD.AcademicYearID == yearId && cls.ProgramID == programId group apt by new{apt.Gender} into grouped select new CityWiseStudentModel { CityName=city.CityName, //'city' does not exist in the current context Gender = grouped.Count(), programName=prg.Program, //'prg' does not exist in the current context } ); 

How can I get the city name from the city table and the program name from the prg table

Answer 1

group <--> by <--> into <--> will change the scope to IGrouping<a,b>

My opinion is not only apt.Gender is your key, but city.CityName and prg.Program

try this (or some similar):

 group apt by new{apt.Gender, city, prg} into grouped select new CityWiseStudentModel { CityName = grouped.Key.city.CityName, Gender = grouped.Count(), //rename GenderCount programName = grouped.Key.prg.Program, // Gender = grouped.Key.Gender, } 

Answer 2

Remember that grouped will only hold those things that you have grouped. If you only group adt , then city and prg will not be available in your choice.

So you need to:

• Include city and program in grouped (otherwise they are not available)
• CityName and Program access within grouped collection

Something like this should do the trick:

 ... group new { apt, cls, prg, city } by new{apt.Gender} into grouped select new CityWiseStudentModel { CityNames = grouped.Select(g => g.city.CityName), ... programNames = grouped.Select(g => g.prg.Program) }