Linq中使用Left Join

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

                               };