注意:如果是外链接,是将join后面的表into到一个新的表里,而不是将join的结果集放在新的表里。因此新的临时表有可能为空,用用DefaultIfEmpty取得,其中的每一行也要判断是否为空。(==null)
1、左连接:
var LeftJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept
from dept in JoinedEmpDept.DefaultIfEmpty()
select new
{
EmployeeName = emp.Name,
DepartmentName = dept != null ? dept.Name : null
};
2、右连接:
var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};
3、内连接:
var query = from t in entitiy.TB_GCGL_ADA_USER
join p in entitiy.TB_GCGL_ZY_ZYK
on t.ETPRS_CODE equals p.ETPRS_CODE
select new TB_USER_ZYK
{
USER_ID = t.USER_ID,
USER_NAME = t.USER_NAME,
USER_PASSWORD = t.USER_PASSWORD,
};
Create table Student(
ID int identity ( 1 , 1 ) primary key ,
[ Name ] nvarchar ( 50 ) not null
)
Create Table Book(
ID int identity ( 1 , 1 ) primary key ,
[ Name ] nvarchar ( 50 ) not null ,
StudentID int not null
)
insert into Student values ( ' 张三 ' )
insert into Student values ( ' 李四 ' )
insert into Student values ( ' 王五 ' )
select * from student
-- 张三借的书
insert into Book values ( ' 红楼 ' , 1 )
insert into Book values ( ' 大话红楼 ' , 1 )
-- 李四借的书
insert into Book values ( ' 三国 ' , 2 )
-- 王五没借书
-- 一本错误的记录
insert into Book values ( ' 错误时怎样练成的 ' , 111 )
-- 左连接
select s.name,b.name from student as s
left join Book as b on s.id = b.studentid
-- 右连接
select s.name,b.name from student as s
right join Book as b on s.id = b.studentid
要用Linq实现左连接,写法如下
var leftJoinSql = from student in db.Student
join book in db.Book on student.ID equals book.StudentID into temp
from tt in temp.DefaultIfEmpty()
select new
{
sname = student.Name,
bname = tt == null ? "" :tt.Name // 这里主要第二个集合有可能为空。需要判断
};
用Linq实现右连接,写法如下
var rightJoinSql = from book in db.Book
join stu in db.Student on book.StudentID equals stu.ID into joinTemp
from tmp in joinTemp.DefaultIfEmpty()
select new {
sname = tmp == null ? "" :tmp.Name,
bname = book.Name
};