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

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 // 这里主要第二个集合有可能为空。需要判断 };

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