Linq中使用左右连接

.Net技术 码拜 7年前 (2014-07-25) 890次浏览 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

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

 

};


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明Linq中使用左右连接
喜欢 (0)
[1034331897@qq.com]
分享 (0)

文章评论已关闭!