c# - 从一对多的情况我如何在 Entity Framework 中获得共同的项目

Question

我刚开始使用 Entity Framework，但在为以下情况生成查询时遇到了困难。

我目前有两个模型类 Student 和 Sport。一个学生可以参加多项运动。这是我的模型的样子

public class DbContext : DbContext
{
        public DbContext(): base("name=DbContext")
        {
        }

        public DbSet<Student> MyStudents { get; set; }
        public DbSet<Sport> MySports { get; set; }
}

public class Student
{
    public List<Sport> Actions { get; set; }

    public string Name { get; set; }
}

public class Sport
{
    public string SportName { get; set; }
}

我的问题是如何获得所有学生参加的所有运动的列表？简而言之，我正在寻找共同的运动。所以基本上在下面的情况下

Student A played Sports : Soccer , Tennis , Bowling
Student B played Sports : Soccer , Tennis , 
Student C played Sport  : Tennis

那么应该只返回 Tennis

Answer 1

使用您提供的数据库架构，您可以获取每个学生的常见运动检查运动:

var sports = new[]
{
    new Sport { SportName = "Tennis" },
    new Sport { SportName = "Soccer" },
    new Sport { SportName = "Bowling" }
};

var students = new[]
{
    new Student
    {
        Name = "Student 1",
        Actions = sports
    },
    new Student
    {
        Name = "Student 2",
        Actions = new[] { sports[0], sports[1] }
    },
    new Student
    {
        Name = "Student 3",
        Actions = new[] { sports[0] }
    }
};

// Or
var sports = context.Sports;
var students = context.Students;

// In case students' sports are objects (as in this sample) you can use such a query:
var commonSports = sports.Where(sport =>
    students.All(student => student.Actions.Contains(sport)));
// In case you're going to check the sports by name, this:
var commonSports = sports.Where(sport =>
    students.All(student => student.Actions.Any(studSport => 
         studSport.SportName == sport.SportName)));
Console.WriteLine($"Comon sports: {string.Join(",", commonSports.Select(i => i.SportName))}");
// To get only names of common sports:
var sportNames = commonSports.Select(i => i.SportName);
Console.Read();

如果您使用关系数据库，那么按照 here 的描述实现多对多关系会更容易(对我而言)更符合逻辑。 :