Linq中使用Left Join

原文地址为： Linq中使用Left Join

　　准备一些测试数据，如下：

 

use Test
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实现左连接，写法如下

 

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

};

 

 

参考资料：http://developer.51cto.com/art/200909/152189.htm

http://hi.baidu.com/thinsoft/blog/item/83fb1e9089cc7186a877a4b1.html

http://apps.hi.baidu.com/share/detail/12540006

http://www.winu.cn/space-14160-do-blog-id-25172.html

转载请注明本文地址： Linq中使用Left Join