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

 

要用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

                               };

 

 三表左连接

 匿名类

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