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