在查询语言中，通常需要使用联接操作。在 LINQ 中，可以通过 join 子句实现联接操作。join 子句可以将来自不同源序列，并且在对象模型中没有直接关系(数据库表之间没有关系)的元素相关联，唯一的要求是每个源中的元素需要共享某个可以进行比较，以判断是否相等的值。
在 LINQ 中，join 子句可以实现 3 种类型的联接分别是内部联接、分组联接和左外部联接。
1、内部连接(相对于sql：join | inner join)
格式：join element in dataSource on exp1 equals exp2
Console.WriteLine("\r\n---------------内部连接------------------");
int[] intAry1 = { 5, 15, 25, 30, 33, 40 };//创建整数数组 intAry1 作为数据源
int[] intAry2 = { 10, 20, 30, 50, 60, 70, 80 };//创建整数数组 intAry2 作为数据源
//查询 query1 使用 join 子句从两个数据源获取数据
//演示内部联接的使用
var query1 =
from val1 in intAry1
join val2 in intAry2 on val1 % 5 equals val2 % 15
select new { VAL1 = val1, VAL2 = val2 };
foreach (var item in query1)
{
Console.WriteLine(item);
}结果：
2、分组连接
格式： join element in dataSource on exp1 equals exp2 into grpName
其中，into 关键字表示将这些数据分组并保存到 grpName 中，grpName 是保存一组数据的集合。（感觉和sql不同，sql查询的结果是平面矩形的，而linq则是平面树形的，意思是像对象的元素也是个对象）
Console.WriteLine("-----------分组连接--------------");
int[] intAry1 = { 5, 15, 25, 30, 33, 40 };//创建整数数组 intAry1 作为数据源
int[] intAry2 = { 10, 20, 30, 50, 60, 70, 80 };//创建整数数组 intAry2 作为数据源
//查询 query1 使用 join 子句从两个数据源获取数据
//演示分组联接的使用
var query1 =
from val1 in intAry1
join val2 in intAry2 on val1 % 5 equals val2 % 15 into val2Grp
select new { VAL1 = val1, VAL2GRP = val2Grp };
foreach (var item in query1)
{
Console.WriteLine(item.VAL1 + "--" + string.Join(",", item.VAL2GRP.ToArray()));
}结果：
3、左外部联接 (相对于sql：left join | left outer join)
第三种联接是左外部联接，它返回第一个集合中的所有元素，无论它是否在第二个集合中有相关元素。在 LINQ 中，通过对分组联接的结果调用 DefaultIfEmpty()方法来执行左外部联接。DefaultIfEmpty()方法从列表中获取指定元素。如果列表为空，则返回默认值。
Console.WriteLine("-------------左外部链接----------------------");
int[] intAry1 = { 5, 15, 23, 30, 33, 40 };//创建整数数组 intAry1 作为数据源
int[] intAry2 = { 10, 20, 30, 50, 60, 70, 80 };//创建整数数组 intAry2 作为数据源
//查询 query1 使用 join 子句从两个数据源获取数据
//演示左联接的使用
var query1 =
from val1 in intAry1
join val2 in intAry2 on val1 % 5 equals val2 % 15 into val2Grp
from grp in val2Grp.DefaultIfEmpty()
select new { VAL1 = val1, VAL2GRP = grp };
foreach (var item in query1)
{
Console.WriteLine(item.VAL1 + "--" + item.VAL2GRP);
}结果：
------------------------------------------------------------------------------------------
查询方法Lambda示例（GroupJoin）
public static IEnumerable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(
this IEnumerable<TOuter> outer,
IEnumerable<TInner> inner,
Func<TOuter, TKey> outerKeySelector,
Func<TInner, TKey> innerKeySelector,
Func<TOuter, IEnumerable<TInner>, TResult> resultSelector
)重载
public static IEnumerable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(
this IEnumerable<TOuter> outer,
IEnumerable<TInner> inner,
Func<TOuter, TKey> outerKeySelector,
Func<TInner, TKey> innerKeySelector,
Func<TOuter, IEnumerable<TInner>, TResult> resultSelector,
IEqualityComparer<TKey> comparer
)左链接查询组合：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TestKzt
{
#region
/// <summary>
/// 学生实体
/// </summary>
public class Student
{
public int StudentId { get; set; }
public string StudentName { get; set; }
public int Age { get; set; }
public int StandardId { get; set; }//水平
public int GradeId { get; set; }
}
/// <summary>
/// 水平/等级
/// </summary>
public class Standard
{
public int StandardId { get; set; }
public string StandardName { get; set; }//
}
public class GradeClass
{
public int GradeId { get; set; }
public string GradeName { get; set; }
}
class Program
{
static void Main(string[] args)
{
#region 数据源
IList<Student> studentList = new List<Student>()
{
new Student() {StudentId = 1, StudentName = "John", StandardId = 1,Age=2,GradeId=1},
new Student() {StudentId = 2, StudentName = "Moin", StandardId = 1,Age=3,GradeId=1},
new Student() {StudentId = 3, StudentName = "Bill", StandardId = 2,Age=5,GradeId=2},
new Student() {StudentId = 4, StudentName = "Ram", StandardId = 2,Age=20},
new Student() {StudentId = 5, StudentName = "Ron", StandardId = 5},
new Student() {StudentId = 10, StudentName = "张华", StandardId = 5},
new Student() {StudentId = 20, StudentName = "李明", StandardId = 5},
new Student() {StudentId = 30, StudentName = "王五", StandardId = 5}
};
IList<Standard> standardList = new List<Standard>()
{
new Standard() {StandardId = 1, StandardName = "优秀"},
new Standard() {StandardId = 2, StandardName = "中等"},
new Standard() {StandardId = 3, StandardName = "差生"}
};
IList<GradeClass> GradeClassList = new List<GradeClass>() {
new GradeClass() {GradeId=1,GradeName="一年级"},
new GradeClass(){GradeId=2,GradeName="二年级"}
};
#endregion
Console.WriteLine("----三个表linq内连接");
var q0 = from a in studentList
join b in standardList on a.StudentId equals b.StandardId
join g in GradeClassList on a.GradeId equals g.GradeId
where a.StudentId != 2 && !a.StudentName.Contains("John")
select new
{
a.StudentId,
a.StudentName,
a.Age,
a.StandardId,
StandarFullName =b.StandardName,
a.GradeId,
GradeName =g.GradeName
};
foreach (var item in q0)
{
var str = string.Format("学生ID:{0},姓名:{1},年龄:{2},水平ID:{3},水平:{4},班级ID:{5},班级:{6}", item.StudentId, item.StudentName, item.Age, item.StandardId, item.StandarFullName, item.GradeId, item.GradeName);
Console.WriteLine(str);
}
Console.WriteLine("----三个表linq 左连接");
var q = from a in studentList
join b in standardList on a.StudentId equals b.StandardId into Lb_join
from lb in Lb_join.DefaultIfEmpty()
join g in GradeClassList on a.GradeId equals g.GradeId into Lg_join
from lg in Lg_join.DefaultIfEmpty()
select new
{
a.StudentId,
a.StudentName,
a.Age,
a.StandardId,
StandarFullName = lb != null ? lb.StandardName : "没有对应值",
a.GradeId,
GradeName = lg != null ? lg.GradeName : "没有班级"
};
foreach (var item in q)
{
var str = string.Format("学生ID:{0},姓名:{1},年龄:{2},水平ID:{3},水平:{4},班级ID:{5},班级:{6}", item.StudentId, item.StudentName, item.Age, item.StandardId, item.StandarFullName, item.GradeId, item.GradeName);
Console.WriteLine(str);
}
Console.WriteLine("\r\n----俩个表lambda 左连接");
Console.WriteLine("\r\n---- 方法一 ------\r\n");
//方式 一
//查询公式
var groupJoin = standardList.GroupJoin(studentList,
standard => standard.StandardId,
student => student.StandardId,
(standard, studentGroup) => new
{
StandarFullName = standard.StandardName,
Students = studentGroup
});//感觉和字典类型一样，一个key，对应一个velue, velue = IEnumerable<Student>
//执行查询
foreach (var item in groupJoin)
{
var str = item.StandarFullName;
str += "【";
foreach (var student in item.Students)
{
str += student.StudentName + "-" + student.Age;
}
str += "】";
Console.WriteLine(str);
}
Console.WriteLine("\r\n------方法二-------\r\n");
//方式二
var list = standardList.GroupJoin(studentList,
x => x.StandardId,
y => y.StandardId,
(x, y) => y.DefaultIfEmpty(new Student()).Select(z => new { StandarFullName = x.StandardName, StudentName = z.StudentName, StandardId = z.StandardId, Age = z.Age })).SelectMany(x => x);
foreach (var item in list)
{
var str = item.StandarFullName;
str += item.StudentName + "-" + item.Age; ;
Console.WriteLine(str);
}
Console.WriteLine("--------三表联合内连接查询---------");
var list0 = studentList.Join(standardList, x => x.StandardId, y => y.StandardId, (x, y) => new {
stu=x,
Standard = y
}).Where(x=>x.stu.StudentId!=2).Join(GradeClassList,m=>m.stu.GradeId,n=>n.GradeId,(m,n)=>new{
m.stu.StudentId,
m.stu.StudentName,
m.stu.StandardId,
m.Standard.StandardName,
m.stu.GradeId,
n.GradeName
}).OrderByDescending(x=>x.StudentId);
foreach (var item in list0)
{
StringBuilder sb = new StringBuilder();
sb.Append("学生ID:" + item.StudentId);
sb.Append(",姓名：" + item.StudentName);
sb.Append(",水平ID：" + item.StandardId);
sb.Append(",水平：" + item.StandardName);
sb.Append(",班级：" + item.GradeName);
Console.WriteLine(sb.ToString());
}
Console.WriteLine("--------三表联合左连接查询---------");
//直接将列名提取
//var list2 = studentList.GroupJoin(standardList, x => x.StandardId, y => y.StandardId, (x, y) => y.DefaultIfEmpty(new Standard()).Select(z => new
//{
// x.StudentId,
// x.StudentName,
// x.StandardId,
// z.StandardName,
// x.GradeId
//})).SelectMany(x => x).Where(x=>x.StudentId<20).GroupJoin(GradeClassList,m=>m.GradeId,n=>n.GradeId,(m,n)=>n.DefaultIfEmpty(new GradeClass()).Select(y=>new {
//m.StudentId,
//m.StudentName,
//m.StandardId,
//m.StandardName,
//m.GradeId,
//y.GradeName
//})).SelectMany(x=>x);
//将类对象作为子元素列
var list2 = studentList.GroupJoin(standardList, x => x.StandardId, y => y.StandardId, (x, y) => y.DefaultIfEmpty(new Standard()).Select(z => new
{
stu=x,
Standard=z
})).SelectMany(x => x).Where(x => x.stu.StudentId < 20)
.GroupJoin(GradeClassList, m => m.stu.GradeId, n => n.GradeId, (m, n) => n.DefaultIfEmpty(new GradeClass()).Select(y => new
{
m.stu.StudentId,
m.stu.StudentName,
m.stu.StandardId,
m.Standard.StandardName,
m.stu.GradeId,
y.GradeName
})).SelectMany(x => x);
foreach (var item in list2)
{
StringBuilder sb = new StringBuilder();
sb.Append("学生ID:"+item.StudentId);
sb.Append(",姓名："+item.StudentName);
sb.Append(",水平ID："+item.StandardId);
sb.Append(",水平："+item.StandardName);
sb.Append(",班级："+item.GradeName);
Console.WriteLine(sb.ToString());
}
Console.WriteLine("------两表左连接-------");
var list3 = studentList.GroupJoin(standardList, x => x.StandardId, y => y.StandardId, (x, y) => new { stu = x, stan = y })
.Where(x=>x.stu.StudentId<30)
.SelectMany(x => x.stan.DefaultIfEmpty(), (x, y) => new {
StudentId=x.stu.StudentId,
StudentName=x.stu.StudentName,
StandardId=x.stu.StandardId,
StandardName=y!=null?y.StandardName:"无值"
});
foreach (var item in list3)
{
StringBuilder sb = new StringBuilder();
sb.Append("学生ID:" + item.StudentId);
sb.Append(",姓名：" + item.StudentName);
sb.Append(",水平ID：" + item.StandardId);
sb.Append(",水平：" + item.StandardName);
//sb.Append(",班级：" + item.GradeName);
Console.WriteLine(sb.ToString());
}
Console.WriteLine("------三表左连接-------");
var list4 = studentList.GroupJoin(standardList, x => x.StandardId, y => y.StandardId, (x, y) => new { stu = x, stan = y })
.Where(x => x.stu.StudentId < 30)
.SelectMany(x => x.stan.DefaultIfEmpty(), (x, y) => new {
stu=x.stu,
StudentName = y != null ? y.StandardName : "无值"
})
.GroupJoin(GradeClassList, m => m.stu.GradeId, n => n.GradeId, (m, n) => new { stu = m.stu, StudentName=m.StudentName, g = n }).SelectMany(x => x.g.DefaultIfEmpty(), (x, y) => new
{
StudentId = x.stu.StudentId,
StudentName = x.stu.StudentName,
StandardId = x.stu.StandardId,
StandardName =x.StudentName,
GradeName=y!=null?y.GradeName:"无年级值"
});
foreach (var item in list4)
{
StringBuilder sb = new StringBuilder();
sb.Append("学生ID:" + item.StudentId);
sb.Append(",姓名：" + item.StudentName);
sb.Append(",水平ID：" + item.StandardId);
sb.Append(",水平：" + item.StandardName);
sb.Append(",班级：" + item.GradeName);
Console.WriteLine(sb.ToString());
}
Console.ReadKey();
}
}
#endregion
}View Code
结果：
示例：分页查询
var page = 1;
var pageSize = 10;
var query = (from user in db.Set<User>()
join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId
join rolePrivilege in db.Set<RolePrivilege>() on userRole.RoleId equals rolePrivilege.RoleId
join priviege in db.Set<Privilege>() on rolePrivilege.PrivilegeId equals priviege.Id
join role in db.Set<Role>() on userRole.RoleId equals role.Id
where user.Id == 1 && userRole.RoleId == 1
orderby user.Id descending
select new
{
user.Id,
userRole.RoleId,
user.Username,
PrivilegeName = priviege.Name,
RoleName = role.Name
}).Skip((page - 1) * pageSize).Take(pageSize);本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
