C#列表连接并从另一个列表筛选

Question

我希望您能帮助构建正确的LINQ查询与连接。

我的设置如下：

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<Student> lstStudents = new List<Student>();

var John = new Student()
{
    StudentEmail = "[e-mail]",
    StudentName = "John Wayne"
};

var Maya = new Student()
{
    StudentEmail = "[e-mail]",
    StudentName = "Maya Agnes"
};

var Eric = new Student()
{
    StudentEmail = "[e-mail]",
    StudentName = "Eric James"
};

var Ellen = new Student()
{
    StudentEmail = "[e-mail]",
    StudentName = "Ellen Page"
};

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

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

// 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<Dropped> lstDropped = new List<Dropped>();

// 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);

我想要实现的是获取所有学生及其所选课程的列表，一行显示，例如：

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

到目前为止，我已经构建了以下内容：

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

但是它给我每个人每个科目的结果。

我遇到了如何从列表中排除已丢弃项目的难题。

我考虑遍历已丢弃的列表，比如：

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

但我觉得这种方法效率很低（尤其是当有大量行时）。

我也不知道如何将主题合并成一行。

在这里寻求帮助。

谢谢。

- Chett

Answer 1

我们首先需要从已注册的名单中删除掉落的科目，然后按学生分组。 请注意，每个学生都有一个列表，其中它的字段是 Student 和 Subject，我们只需从每个列表中选择第一个学生的名称（因为它们都相同），并将科目连接在一起。

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();

打印：

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

LIVE DEMO

- Ashkan Mobayen Khiabani

---

好的解决方案。2个想法：#1 可以使用 Name = x.Key.StudentName 作为名称。#2 没有课程的学生将不会出现在输出中，这可能是需要的。 - Amy B

好的，OP明确定义了他想要的输出。 - Ashkan Mobayen Khiabani

我在我的代码中添加了一个实时演示，你可以查看。 - Ashkan Mobayen Khiabani

Answer 2

你已经接近成功了。连接是正确的，你只需要实际应用你想要的分组：

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();

然后，您可以使用上面的投影方式随意显示它。可能像这样：

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

- Mike Corcoran

Answer 3

尝试使用 "into" 关键字进行 GroupJoin。

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();

或者，您可以使用let进行子查询。这种方法在处理较大列表时性能明显较慢，但在数据库中应该没问题。

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();

- Amy B