Linq的链接

注意：如果是外链接，是将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,

                 };

 

use Test
Createtable Student(
ID intidentity(1,1) primarykey,
[Name]nvarchar(50) notnull
)

CreateTable Book(
ID intidentity(1,1) primarykey,
[Name]nvarchar(50)notnull,
StudentID intnotnull
)

insertinto Student values('张三')
insertinto Student values('李四')
insertinto Student values('王五')
select*from student

--张三借的书
insertinto Book values('红楼',1)
insertinto Book values('大话红楼',1)

--李四借的书
insertinto Book values('三国',2)

--王五没借书

--一本错误的记录
insertinto Book values('错误时怎样练成的',111)

--左连接
select s.name,b.name from student as s
leftjoin Book as b on s.id=b.studentid

--右连接
select s.name,b.name from student as s
rightjoin Book as b on s.id=b.studentid

 

要用Linq实现左连接，写法如下

 

DataClasses1DataContext db =new DataClasses1DataContext();
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实现右连接，写法如下

 

 

DataClasses1DataContext db=new DataClasses1DataContext();
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

};