CODEWITHSUNDEEP
c#.netlinqc#-4.0
user1400915
user1400915

Reputation: 1943

LINQ query to join two sets and filter on the result set

I have two tables 

School
ID  Name 
1   School1
2   School2

Student
ID  SchoolID IsAStudent  IsATeacher
1    1             1         0
2    1             1         0
3    2             1         0
4    2             0         1

public class School
{
 public int ID {get;set;}
 public string Name {get;set;}
}

I have a List<School> school= new List<School>();

Input:

School s1 = new School() { ID =1 ,Name ="School1"};
School s2 = new School() {ID= 2, Name = "School2"};
school.Add(s1);
school.Add(s2);

This list contains School with Ids 1 and 2.

Expected Output:

I need to check if atleast of one of the Schools doesnt have a teacher.

In our example since School1 doesnt have a teacher I should get true from the below funtion:

public bool IsTeacherNotPresentAtleastInOneSchool(List<School> school)
{
  var a = (from b in school
           join c in _studentEntity.GetAll()
           on b.ID equals c.SchoolID
           where c.IsATeacher == false
           select b).ToList();
 if(a.Count >0)
   return true;
 else
   return false;
}

Although the above usecase will pass ,the a.Count will return 3 records which causes one usecase below to fail.

.Suppose I have only one School with School2 , then there would be 2 rows in Student table - one row as a student with ID 3 and another as teacher with ID 4 .

Even in this situation I would get a.Count as 1 which is incorrect since my problem statement is "If Atleast one School which doesnt have a teacher return true". How do I solve this?

Upvotes: 0

Views: 219

Answers (1)

Mohammed Sajid
Mohammed Sajid

Reputation: 4903

You can Group students by schoolId before join, i tried this code for Linq to Object, and it's wok well:

1 - Build grouping School for students, and check if all IsATeacher for each schoolin students are false

var groupedSchool = _studentEntity.GetAll()
    .GroupBy(x => x.SchoolID)
    .ToDictionary(k => k.Key, v => v.All(x => !x.IsATeacher));

Result for 1:


SchoolID  IsDontHasTeacher
1          true
2          false

2 - change the existing Query to:

var a = (from b in schools
         join c in groupedSchool
         on b.ID equals c.Key 
         where c.Value == true
         select b).ToList();
return a.Count > 0;

I hope this help

Upvotes: 1

Related Questions