Many to many 多对多的查询

在多对多的例子里面我们做了三个表 Student, Course, StudentCourse, 并且用下面的代码添加了一些数据

 public void InitData()
        {
            using (var dbContext = this.dbContextFactory.CreateDbContext())
            {
                if (dbContext.Students.Count() < 1)
                {
                    var courses = GetCourses();
                    dbContext.AddRange(courses);
                    dbContext.SaveChanges();

                    var students = GetStudents();
                    dbContext.AddRange(students);
                    dbContext.SaveChanges();

                    var studentCourse1 = new StudentCourse()
                    {
                        StudentId = 1,
                        CourseId = 1,
                    };

                    var studentCourse2 = new StudentCourse()
                    {
                        StudentId = 1,
                        CourseId = 2,
                    };
                    var studentCourse3 = new StudentCourse()
                    {
                        StudentId = 2,
                        CourseId = 1,
                    };

                    dbContext.AddRange(studentCourse1, studentCourse2, studentCourse3);
                    dbContext.SaveChanges();
                }
            }
        }

        private static List<Course> GetCourses()
        {
            var course1 = new Course() { Name = "英语" };
            var course2 = new Course { Name = "足球" };

            return new List<Course>() { course1, course2 };
        }

        private static List<Student> GetStudents()
        {
            var student1 = new Student() { Name = "张三", };
            var student2 = new Student() { Name = "李四", };

            return new List<Student>() { student1, student2 };
        }

我们给 课程表加了两条数据 英语 足球 我们给 学生表加了两条数据张三 李四 张三报了两个课程 英语 足球 李四 只报了一个课程 英语

很多时间我们查询学生的时候需要把学生的课程全部查询出来。

这样就可以用下面的语句来查询了

public void GetStudentWithCourses()
{
    var scQuery = from s in malemaDbContext.Students
                    join sc in malemaDbContext.StudentCourses
                    on s.Id equals sc.StudentId
                    select new { s.Name, sc.CourseId };

    var query2 = from sc in scQuery
                    join c in malemaDbContext.Courses
                    on sc.CourseId equals c.Id
                    select new { sc.Name, courseName = c.Name };

    query2.ToList();

//使用 预先加载 eager loading
    var student = this.malemaDbContext.Students.Include(x => x.StudentCourses)
        .ThenInclude(x => x.Course);
}

生成的sql语句如下

--第一个生成的sql的语句
  SELECT [s].[Name], [c].[Name] AS [courseName]
      FROM [Students] AS [s]
      INNER JOIN [StudentCourses] AS [s0] ON [s].[Id] = [s0].[StudentId]
      INNER JOIN [Courses] AS [c] ON [s0].[CourseId] = [c].[Id]

-- 第二个生成的sql语句
SELECT [s].[Id], [s].[Name], [t].[Id], [t].[CourseId], [t].[StudentId], [t].[Id0], [t].[Name]
      FROM [Students] AS [s]
      LEFT JOIN (
          SELECT [s0].[Id], [s0].[CourseId], [s0].[StudentId], [c].[Id] AS [Id0], [c].[Name]
          FROM [StudentCourses] AS [s0]
          INNER JOIN [Courses] AS [c] ON [s0].[CourseId] = [c].[Id]
      ) AS [t] ON [s].[Id] = [t].[StudentId]
      ORDER BY [s].[Id], [t].[Id], [t].[Id0]

查询课程及它的学生

public void GetCourseWithStudents()
{
    var students = this.malemaDbContext.Courses.Include(x => x.StudentCourses)
        .ThenInclude(x => x.Student).ToList();
}

生成的sql语句如下

  SELECT [c].[Id], [c].[Name], [t].[Id], [t].[CourseId], [t].[StudentId], [t].[Id0], [t].[Name]
      FROM [Courses] AS [c]
      LEFT JOIN (
          SELECT [s].[Id], [s].[CourseId], [s].[StudentId], [s0].[Id] AS [Id0], [s0].[Name]
          FROM [StudentCourses] AS [s]
          INNER JOIN [Students] AS [s0] ON [s].[StudentId] = [s0].[Id]
      ) AS [t] ON [c].[Id] = [t].[CourseId]
      ORDER BY [c].[Id], [t].[Id], [t].[Id0]
      

sql查询出来的原始结果如下

但是linq出来的是两条记录，不是三条哦。符合我们的预期

从上面的例子我们可以看出借助Include我们可以很方便的进行一些查询了。

上面的完整代码可以在分支querying/many_to_many看到

git clone https://gitee.com/malema/Examples
git checkout querying/many_to_many

ManyToManyExample.cs