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