Linq中使用Left Join

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

 

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
                               };

 

 

参考资料：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