Filtering in Linq in MVC4

Question

My table structure is as follows

Course

ID    CourseName     

1       PHP
2       WORDPRESS

MainCourse

ID     MainCourseName
1       FoundationPhp
2       FoundationWordPress
3       Diploma

SubCourse

ID       MainCourseID   CourseID
 1           1             1
 2           2             2
 3           3             1
 3           3             2 

I have a search scenario which should fetch the below result

When an user searches by PHP alone,he should get the following result

 MainCourseName    CourseCombination    
  FoundationPHP       PHP
  Diploma             PHP,WORDPRESS

When an user searches by WORDPRESS alone,he should get the following result

  MainCourseName           CourseCombination    
  FoundationWordPress       WORDPRESS
  Diploma                   PHP,WORDPRESS

When an user searches by PHP,WORDPRESS ,he should get the following result.

   MainCourseName           CourseCombination
    Diploma                    PHP,WORDPRESS  

NOTE:If there is any other combination that contains both PHP & WORDPRESS that should be shown as well.Example of one such combination will be like PHP,WORDPRESS,JAVA.

I have tried the following method

     List<int> CourseId={1,2}//means user searches by both PHP & WORDPRESS
     var courseList = _db.SubCourses
                      .AsEnumerable()
                      .Where(mcd => courseId.Contains(mcd.Course.Id))
                      .Select(mc => new RegistraionVM.clsCourseCodeSearch
                       {
                          CourseCode = mc.MainCourse.MainCourseName,
                          CourseCombination = string.Join(",", mc.MainCourse.SubCourse
                                                                .Select(mcd => mcd.Course.Name))                     

                        }).Distinct().Take(5).ToList();

The above query returns the following result

  MainCourseName        CourseCombination    
  FoundationPHP         PHP
  FoundationWordPress   WORDPRESS
  Diploma               PHP,WORDPRESS

Desired result is

  MainCourseName        CourseCombination        
  Diploma               PHP,WORDPRESS

How can I acheive the above result

Answer 1

You need to first groupby the MainCourseID, then use an .All() clause to select only items where all the resulting subcourses are included in the filter

var filter = new int[] { 1 }; // or new int[] { 1, 2 } etc

var results = list.GroupBy(x => x.MainCourseID).Select(x => new
{
    MainCourse = x.FirstOrDefault().MainCourse,
    SubCourseIDs = x.Select(y => y.Course.ID),
    SubCourseNames = x.Select(y => y.Course.CourseName)
}).Where(x => filter.All(y => x.SubCourseIDs.Contains(y))).Select(x => new RegistraionVM.clsCourseCodeSearch()
{
    CourseCode = x.MainCourse.MainCourseName,
    CourseCombination = String.Join(", ", x.SubCourseNames)
}).Take(5).ToList();