LINQ: Join 5 or 6 tables and group by one then count groupby data

Question

Tables are : From Child level to Parent Level:

TRAINING_TEACHER >TEACHER >INSTITUTE >SUBDISTRICT >DISTRICT ZONE(DIVISION)

I need to show institute name and it's male and female teacher participated in training group by SUBDISTRICT. My View model is :

 public class TRAINING_STATUS_VIEW_MODEL
  {
    public int countMale { get; set; }
    public int countFemale { get; set; }
    public ZONE ZONE_VIEW_MODEL { get; set; }
    public DISTRICT DISTRICT_VIEW_MODEL { get; set; }
    public SUBDISTRICTUPAZILA_VIEW_MODEL { get; set; }
    public INSTITUTE INSTITUTE_VIEW_MODEL { get; set; }
  }

Where countMale, countFemale are numbers of teacher from each institute in TRAINING_TEACHER table according to gender. There is a gender column in TEACHER table. Now here is the query I can not performing group by as institute and count.

var trainingStatusReport = 
from trainingTeacher in db.TRAINING_TEACHER 
join teacher in db.TEACHERs on trainingTeacher.TEACHER_NO equals teacher.TABLE_ID 
join institute in db.INSTITUTEs on teacher.INS_ID equals institute.INS_ID
join subDistrict in db.subDistricts on institute.LOC_ID equals (decimal)subDistrict.LOC_ID
join district in db.DISTRICTs on (decimal)subDistrict.DCODE equals (decimal)district.DCODE
join zone  in db.ZONEs on  district.ZC equals  zone.ZC   
select new TRAINING_STATUS_VIEW_MODEL { 
..........
.......... I can't go here
} ;

I have tried with group institute by institute.ins_id but couldn't code in select section.

Do you need the grouping of teachers by institute alone, or a grouping of teachers by zone? — Balah, Jan 31 '16 at 13:44
I need to draw it now. Another better understanding way is training_teacher is under techer, teacher is under instiute , institute is under subdistrict, subdistrict is under distrcit,distrcit is under zone. The thing I can not is group by subdistrcit and count as institute male and female teacher. — Muhammad Ashikuzzaman, Jan 31 '16 at 13:56
All these joins are not necessary. Declare the proper navigation properties and all joins go away. — usr, Jan 31 '16 at 15:17
Are you able to add the navigation properties? The group by will be much simpler to write out. i.e. you can then go `group trainingTeacher by trainingTeacher.Teacher.Institute into instituteGroup` and use the aggregations there — Balah, Feb 01 '16 at 07:34
@Balah your suggestion worked :) But fall in truble when wanted to join with the grouped variabe by firstordefault().column_name — Muhammad Ashikuzzaman, Feb 03 '16 at 06:32
For that you will need the 'let' statement, not a join. I will post an answer just now — Balah, Feb 03 '16 at 07:04
I have solved my answer from here http://stackoverflow.com/questions/9173410/linq-combining-join-and-group-by/9173994#comment58058289_9173994 both two answer from @AakashM and @ L-Three was helpful — Muhammad Ashikuzzaman, Feb 03 '16 at 07:10

score 0 · Answer 1 · answered Feb 03 '16 at 07:10

To use a group by with further joins is possible, but can get inefficient. A better alternative to joins is to

use navigation properties
use the let statement to simplify any other complex logic.

Here is an example of how to combine the two. Does this solve the problem?

var trainingStatusReport =
    from trainingTeacher in db.TRAINING_TEACHER
    group trainingTeacher by trainingTeacher.Teacher.Institute into instituteGroup
    let institute = instituteGroup.Select(t => t.Teacher.Institute).FirstOrDefault()
    select new TRAINING_STATUS_VIEW_MODEL
    {
        countMale = instituteGroup.Count(t => t.Teacher.Gender == Male),
        countFemail = instituteGroup.Count(t => t.Teacher.Gender == Femail),
        ZONE = institute.SubDistrict.District.Zone,
        DISTRICT = institute.SubDistrict.District,
        SUBDISTRICT = institute.SubDistrict,
        INSTITUTE = institute
    };

LINQ: Join 5 or 6 tables and group by one then count groupby data

1 Answers1