Linq Left Join;linq左连接 (转载)
来源 https://www.cnblogs.com/xinjian/archive/2010/11/17/1879959.html
准备一些测试数据,如下:
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
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//这里主要第二个集合有可能为空。需要判断
};
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
};
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
};
三表左连接
匿名类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
var list = ( from u in dc.userinfos join j in dc.jobs on u.job equals j.jid into j_join from x in j_join.DefaultIfEmpty() join c in dc.cities on u.city equals c.cid into c_join from v in c_join.DefaultIfEmpty() select new { id = u.id, name = u.name, jname = x.jname, cname = v.cname, /*u1=u,x1=x,v1=v*/ //不要用对象的方式 因为对象可能为null那么对象.属性就会抛异常 } ).ToList(); for (var i = 0; i < list.Count(); i++) { Console.WriteLine(list[i].name + '\t' + list[i].jname + '\t' + list[i].cname); //字段为null不报异常 //Console.WriteLine(list[i].u1.name+'\t'+list[i].x1.jname+'\t'+list[i].v1.cname+"\r\n"); //对象x1 v1 有可能为null 抛异常 } Console.ReadLine();
模型类
linq join 左连接 leftjoin 多个on条件 where 条件
var haveChange = from newScore in newScoreList join oldScore in oldScoreList on new{newScore.ExamId,newScore.StudentId,newScore.Subject,newScore.ClassId} equals new{oldScore.ExamId,oldScore.StudentId,oldScore.Subject,oldScore.ClassId} where newScore.Score != oldScore.Score select newScore;
var query= from cc in COPTC join cd in COPTD on new {cc.TC001,cc.TC002} equals new{ cd.TD001,cd.TD002} into m from n in m.DefaultIfEmpty() join ma in MOCTA on new{cd.TD001, cd.TD002,cd.TD003 } equals new{ ma.TA026,ma.TA027,ma.TA028} where new char[]{'Y','y'}.Contains(ma.TA013) && new char[]{'Y','y'}.Contains(cc.TC027) && Convert.ToDateTime(ma.TA003) > Convert.ToDateTime("2010-07-20") && Convert.ToDateTime(ma.TA003) < Convert.ToDateTime("2010-12-31") select new { cc, cd };
参考资料:
https://www.cnblogs.com/weixing/p/4447927.html
https://www.cnblogs.com/joeylee/p/3711654.html
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
树立目标,保持活力,gogogo!