C# List Join List And Filter From Another List

Question

Need some help constructing proper LINQ query with join.

I have the following setup:

public class Student
{
    public string StudentName { get; set; }
    public string StudentEmail { get; set; }
}

public class Enrolled
{
    public Student Student { get; set; }
    public string Subject { get; set; }
}

public class Dropped
{
    public Student Student { get; set; }
    public string Subject { get; set; }
}


// Populating the List.
// Setup the Student List
List lstStudents = new List();

var John = new Student()
{
    StudentEmail = "john@stanford.edu",
    StudentName = "John Wayne"
};

var Maya = new Student()
{
    StudentEmail = "maya@stanford.edu",
    StudentName = "Maya Agnes"
};

var Eric = new Student()
{
    StudentEmail = "eric@stanford.edu",
    StudentName = "Eric James"
};

var Ellen = new Student()
{
    StudentEmail = "ellen@stanford.edu",
    StudentName = "Ellen Page"
};

lstStudents.Add(John);
lstStudents.Add(Maya);
lstStudents.Add(Eric);
lstStudents.Add(Ellen);

// Setup the Enrolled List
List lstEnrolled = new List();

// John
var JohnMath = new Enrolled() { Student = John, Subject = "Math" };
var JohnScience = new Enrolled() { Student = John, Subject = "Science" };
var JohnEnglish = new Enrolled() { Student = John, Subject = "English" };

// Maya
var MayaMath = new Enrolled() { Student = Maya, Subject = "Math" };

// Eric
var EricMath = new Enrolled() { Student = Eric, Subject = "Math" };
var EricScience = new Enrolled() { Student = Eric, Subject = "Science" };
var EricSocial = new Enrolled() { Student = Eric, Subject = "Social" };

// Ellen
var EllenMath = new Enrolled() { Student = Ellen, Subject = "Math" };
var EllenScience = new Enrolled() { Student = Ellen, Subject = "Science" };
var EllenEnglish = new Enrolled() { Student = Ellen, Subject = "English" };
var EllenSocial = new Enrolled() { Student = Ellen, Subject = "Social" };

lstEnrolled.Add(JohnMath);
lstEnrolled.Add(JohnScience);
lstEnrolled.Add(JohnEnglish);
lstEnrolled.Add(MayaMath);
lstEnrolled.Add(EricMath);
lstEnrolled.Add(EricScience);
lstEnrolled.Add(EricSocial);
lstEnrolled.Add(EllenMath);
lstEnrolled.Add(EllenScience);
lstEnrolled.Add(EllenEnglish);
lstEnrolled.Add(EllenSocial);

// Setup the Dropped List
List lstDropped = new List();

// John dropped Math
var JohnDropMath = new Dropped() { Student = John, Subject = "Math" };

// Eric dropped Social
var EricDropSocial = new Dropped() { Student = Eric, Subject = "Social" };

// Ellen Dropped Math
var EllenDropMath = new Dropped() { Student = Ellen, Subject = "Math" };

lstDropped.Add(JohnDropMath);
lstDropped.Add(EricDropSocial);
lstDropped.Add(EllenDropMath);

What I am trying to achieve is get a list of all students, and the subjects they are enrolled in one line, like:

Student     Subjects
-------     ----------------------------------
John        English, Science
Maya        Math
Eric        Math, Science
Ellen       English, Science, Social

I have constructed the following so far:

var StudentsAndCurrentSubjects = (from st in lstStudents
                                  join en in lstEnrolled 
                                      on st.StudentName equals en.Student.StudentName
                                  select new
                                  {
                                      st.StudentName,
                                      en.Subject
                                  })
                                  .ToList();

But it is giving me the result per person per subject.

I hit a wall on how to exclude the dropped items from the list.

I am thinking of traversing the dropped list, like:

foreach(d in lstDropped) 
{
    // Logic to Remove it from the StudentsAndCurrentSubjects 
}

But I feel that it is inefficient (especially if I have lots of rows).

I also do not know how to join the subjects in one line.

Looking for some help here.

Thanks.

user_1079221 · Answer

We just need to first remove dropped subjects from enrolled list. then group by student. note that we have list for each student, that it has Student and Subject as its fields, we just select the name of first student from each (as they all the same) and join the subjects together.

var result = lstEnrolled.Where(e => !lstDropped.Any(d => d.Student == e.Student && d.Subject == e.Subject))  //omit dropped courses
     .GroupBy(x => x.Student) // group results by students
     .Select(x => new {
            Name = x.First().Student.StudentName.Split(' ').First(), 
            Subjects = string.Join(", ", x.Select(e => e.Subject)) 
       }).ToArray();

for printing:

foreach(var x in result)
Console.WriteLine($"{x.Name}	{x.Subjects}");

LIVE DEMO

user_1208078 · Answer

You are almost there. The join is correct, you just need to actually apply the grouping you want:

var StudentsAndCurrentSubjects = (from st in lstStudents
                                  join en in lstEnrolled 
                                      on st.StudentName equals en.Student.StudentName
                                  select new
                                  {
                                      st.StudentName,
                                      en.Subject
                                  })
                                  .GroupBy(s => s.StudentName, d => d.Subject)
                                  .Select(grp => new { StudentName = grp.Key, Subjects = grp.ToList() })
                                  .ToList();

Then you can use the projection above to display it however you want. Something like this maybe:

foreach (var grouping in StudentsAndCurrentSubjects) {
    var studentName = grouping.StudentName;
    var subjects = string.Join(", ", grouping.Subjects);
    Console.WriteLine($"{studentName}	{subjects}");
}

user_8155 · Answer

Try a GroupJoin by using the into keyword.

var studentsAndCurrentSubjects = (
  from student in lstStudents
  join enrollment in lstEnrolled
    on student equals enrollment.Student
    into enrollments
  join drop in lstDrop
    on student equals drop.Student
    into drops
  let classes = enrollments.Select(e => e.Subject).Except(drops.Select(d => d.Subject))
  select new
  {
    Student = student.StudentName,
    Subjects = string.Join(", ", classes)
  })
  .ToList();

Or, you can subquery with let. This will have noticeably slower performance against larger lists than the other solution, but it should be fine in the database.

var studentsAndCurrentSubjects = (
  from student in lstStudents
  let enrollments = lstEnrolled.Where(x => student == x.Student).Select(x => x.Subject)
  let drops = lstDrops.Where(x => student == drop.Student).Select(x => x.Subject)
  let classes = enrollments.Except(drops)
  select new
  {
    Student = student.StudentName,
    Subjects = string.Join(", ", classes)
  })
  .ToList();

C# List Join List And Filter From Another List

3 Answers