Linq中使用Left Join

﹏ヽ暗。殇╰゛Y 2022-08-06 01:06 234阅读 0赞

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