Linq实现左连接、右连接

--一本错误的记录

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

 

                               };