首先我们得准备一下数据
public void InitData()
{
using (var dbCotnext = this.dbContextFactory.CreateDbContext())
{
List<Student> students = GetStudents();
if (dbCotnext.Students.Count() < 1)
{
dbCotnext.SchoolAreas.AddRange(GetSchoolAreas());
dbCotnext.SaveChanges();
dbCotnext.Classes.AddRange(GetClasses());
dbCotnext.AddRange(students);
dbCotnext.SaveChanges();
}
}
}
private static List<SchoolArea> GetSchoolAreas()
{
return new List<SchoolArea> { "南区", "北区", "东区" };
}
private static List<Class> GetClasses()
{
var class1 = new Class() { Name = "104", SchoolAreaId = 1 };
var class2 = new Class() { Name = "105", SchoolAreaId = 1 };
var class3 = new Class() { Name = "106", SchoolAreaId = 2 };
return new List<Class> { class1, class2, class3 };
}
private static List<Student> GetStudents()
{
var student1 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-12), ClassId = 1, Grade = 2, Name = "张三", TotalScore = 200 };
var student2 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 1, Grade = 2, Name = "李四", TotalScore = 198 };
var student3 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "王五", TotalScore = 197 };
var student4 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "赵七", TotalScore = 196 };
var student5 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "狗蛋", TotalScore = 195 };
var student6 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = 2, Grade = 2, Name = "江小牙", TotalScore = 180 };
//这个学生没有班级
var student7 = new Student() { BirthDate = DateTime.UtcNow.AddYears(-13), ClassId = null, Grade = 2, Name = "孙小空", TotalScore = 199 };
return new List<Student>() { student1, student2, student3, student4, student5, student6, student7 };
}
准备好数据后。表中的数据如下 class 106 没有学生数据 孙小空的classId为null
Inner join 通过 classId进行关联，两边必须都有数据。
public void InnerJoinOneToMany()
{
// inner join linq
var query = from s in malemaDbContext.Students
join c in malemaDbContext.Classes
on s.ClassId equals c.Id
// orderby s.ClassId descending ascending 这边可以进行排序，orderby是连在一块的，跟sql不一样
select new { s.Id, s.Name, s.TotalScore, className = c.Name };
var result = query.ToList();
//inner join lambda
var innerResult = malemaDbContext.Students.Join(malemaDbContext.Classes,
s => s.ClassId, //选出来的key是用来对比的
c => c.Id,
(s, c) => new { s.Id, s.Name, s.TotalScore, ClassName = c.Name }).ToList();
}
找到6条数据 最后一条没有找到. 上面两种方式生成的sql如下
SELECT [s].[Id], [s].[Name], [s].[TotalScore], [c].[Name] AS [ClassName]
FROM [Students] AS [s]
INNER JOIN [Classes] AS [c] ON [s].[ClassId] = [c].[Id]
Student Left Join Class. 取所有的Student并且把关联的Class的数据取出来。如果没有关联的class所为Null
public void LeftJoinOneToMany()
var leftJoin = from s in malemaDbContext.Students
join c in malemaDbContext.Classes
on s.ClassId equals c.Id into joinTemp
from tmp in joinTemp.DefaultIfEmpty() //DefaultIfEmpty() 不写的话是变成了Inner join
select new { s.Id, s.Name, s.TotalScore, ClassName = tmp.Name };
// 如果查询的数据是 数字类型的话。则需要把 类型转成 可空类型 (decimal?)class.AverageScore
var leftJoinResult = leftJoin.ToList();
//假设没有关联时的写法 使用的是 groupJoin，
var resul2 = malemaDbContext.Students.GroupJoin(malemaDbContext.Classes,
s => s.ClassId,
c => c.Id,
(s, c) => new { s, c }).SelectMany(joinTemp => joinTemp.c.DefaultIfEmpty(), (temp, c) => new
{
temp.s.Id,
temp.s.Name,
temp.s.TotalScore,
ClassName = c.Name
}).ToList();
//使用Projection Query 投影查询 可以直接用 x.Class 获取, 最方便是用这种方式。好理解
var resutl3 = malemaDbContext.Students
.Select(x => new { x.Id, x.Name, x.TotalScore, ClassName = x.Class.Name }).ToList();
}
找到7条，最后一条的className为Null
三种生成的sql语句都是一样的 如下
SELECT [s].[Id], [s].[Name], [s].[TotalScore], [c].[Name] AS [ClassName]
FROM [Students] AS [s]
LEFT JOIN [Classes] AS [c] ON [s].[ClassId] = [c].[Id]
这边要做的是Student表 Right Join Class表 但是 因为 Linq没有RightJoin的语法。 本质上用的是 Left Join. 相当于 Class表 Left Join Student 表
public void RightJoinOneToMany()
{
// 因为有课程没有学生，所以 studentId可能是会为空的。要使用可空类型
var rightJoin = from c in malemaDbContext.Classes
join s in malemaDbContext.Students
on c.Id equals s.ClassId into joinTemp
from tmp in joinTemp.DefaultIfEmpty()
select new
{
ClassName = c.Name,
StudentId = (int?)tmp.Id,
StudentName = tmp.Name,
TotalScore = (int?)tmp.TotalScore
};
var result1 = rightJoin.ToList();
//假设没有关联时的写法 使用的是 groupJoin，
var result2 = malemaDbContext.Classes.GroupJoin(malemaDbContext.Students,
c => c.Id,
s => s.ClassId,
(c, s) => new { c, s }).SelectMany(joinTemp => joinTemp.s.DefaultIfEmpty(), (temp, s) => new
{
ClassName = temp.c.Name,
StudentId = (int?)s.Id,
StudentName = s.Name,
TotalScore = (int?)s.TotalScore,
}).ToList();
// 会把没有 student的也查询出来。
var result3 = malemaDbContext.Classes.SelectMany(
x => x.Students.DefaultIfEmpty(), (x, s) => new
{
ClassName = x.Name,
StudentId = (int?)s.Id,
StudentName = s.Name,
TotalScore = (int?)s.TotalScore
}).ToList();
}
找到7条，最后一条的student相关的都为Null
三种生成的sql语句一样如下
SELECT [c].[Name] AS [ClassName], [s].[Id] AS [StudentId], [s].[Name] AS [StudentName], CAST([s].[TotalScore] AS int) AS [TotalScore]
FROM [Classes] AS [c]
LEFT JOIN [Students] AS [s] ON [c].[Id] = [s].[ClassId]
我们正常的业务大部分使用的都是Left join这种功能。借助Efcore自带投影查询功能，我们可以方便写出多次Left join的语句。阅读起来也没有一点压力。
public void TwoLevelLeftJoin()
{
//多级查询 Left Join 两级
var resutl5 = malemaDbContext.Students
.Select(x => new
{
x.Id,
x.Name,
ClassName = x.Class.Name,
AreaName = x.Class.SchoolArea.Name
}).ToList();
}
找到7条，最后一条的class schoolArea 相关的都为Null 生成的sql语句如下
SELECT [s].[Id], [s].[Name], [c].[Name] AS [ClassName], [s0].[Name] AS [AreaName]
FROM [Students] AS [s]
LEFT JOIN [Classes] AS [c] ON [s].[ClassId] = [c].[Id]
LEFT JOIN [SchoolAreas] AS [s0] ON [c].[SchoolAreaId] = [s0].[Id]
public void CartesianProduct()
{
var query = from s in malemaDbContext.Students
from c in malemaDbContext.Classes
select new { StduentName = s.Name, ClassName = c.Name };
var result = query.ToList();
}
生成的sql语名如下
SELECT [s].[Name] AS [StduentName], [c].[Name] AS [ClassName]
FROM [Students] AS [s]
CROSS JOIN [Classes] AS [c]
上面的例子可能在实际业务上没有意义。 生成记录集的数量是 21 = 7 * 3 电商当中一个产品的经常有两种以上的属性 颜色 还有 Size。通过下面的写法可以方便的列出所有的组合。我们就可以跟据它们一起来做库存管理。
public void CartesianProduct2()
{
var color = new List<string>() { "red", "green", "blue" };
var size = new List<string>() { "M", "L" };
var query = from c in color
from s in size
select new { c, s };
var result = query.ToList();
var result2 = color.SelectMany(g => size, (c, s) => new { c, s });
}
找到的结果如下
上面的完整代码可以在分支querying/one_to_many_join看到
git clone https://gitee.com/malema/Examples
git checkout querying/one_to_many_join
OneToManyJoinExample.cs